How to import variable record length CSV file using SSIS?

Question

Has anyone been able to get a variable record length text file (CSV) into SQL Server via SSIS?

I have tried time and again to get a CSV file into a SQL Server table, using SSIS, where the input file has varying record lengths. For this question, the two different record lengths are 63 and 326 bytes. All record lengths will be imported into the same 326 byte width table.

There are over 1 million records to import.
I have no control of the creation of the import file.
I must use SSIS.
I have confirmed with MS that this has been reported as a bug. I have tried several workarounds. Most have been where I try to write custom code to intercept the record and I cant seem to get that to work as I want.

Solution 1

I had a similar problem, and used custom code (Script Task), and a Script Component under the Data Flow tab.

I have a Flat File Source feeding into a Script Component. Inside there I use code to manipulate the incomming data and fix it up for the destination.

My issue was the provider was using '000000' as no date available, and another coloumn had a padding/trim issue.

Solution 2

You should have no problem importing this file. Just make sure when you create the Flat File connection manager, select Delimited format, then set SSIS column length to maximum file column length so it can accomodate any data.

It appears like you are using Fixed width format, which is not correct for CSV files (since you have variable length column), or maybe you've incorrectly set the column delimiter.

Solution 3

You can write a script task using C# to iterate through each line and pad it with the proper amount of commas to pad the data out. This assumes, of course, that all of the data aligns with the proper columns.

I.e. as you read each record, you can "count" the number of commas. Then, just append X number of commas to the end of the record until it has the correct number of commas.

Excel has an issue that causes this kind of file to be created when converting to CSV.

If you can do this "by hand" the best way to solve this is to open the file in Excel, create a column at the "end" of the record, and fill it all the way down with 1s or some other character.

Nasty, but can be a quick solution.

If you don't have the ability to do this, you can do the same thing programmatically as described above.

Comments

[Taptronic]

Basically, when you encounter a short record on the import file, SSIS continues importing into the current row on the SQL Server table. It SHOULD begin importing that very next byte of the file in a brand new row on the table. It totally mangles the data. Even the CRLF, itself, gets imported!

[Taptronic]

I had selected Delimited and set the length to the max record length - no luck. I had to give up on it and write custom import code using C#, processing record by record.