I was executing an SSIS package and processing the data from the flat files downloaded from DoT. I had a set of a set of files to be downloaded from DoT and then processed into a table.

The process itself is simple where in we first use HTTP connection manager or C# script task to download the files and then process using MULTIFLATFILE connection manager in SSIS.

However the package failed multiple times. Couple of reasons:

1) Source flat files code page was different then ANSI 1252

Solution : use code page in the multifile connection manager as UTF 8

2) Failed with truncation error on multiple columns

Solution : change the typeguess property in the registry or use suggest more than 200 rows for predicting the datatypes of destination columns. Or as a last resort insert a dummy record more than 256 characters and then delete after ETL processing

3) OLEDB cgonnection manager failed for unicode source file

Solution: use ADO.NET connection manager

4) Truncation when inserting to SQL table using ADO.NET connection manager

The precise error was “[ADO NET Destination [2]] Error: An exception has occurred during data insertion, the message returned from the provider is:
Received an invalid column length from the bcp client for colid 19.”

Solution : check the column 19 and it was inserting more than allocated for the column. Increasing the column data length fixed the issue.