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.

Answer 1

Why can't you just import it as a test file and set the column delimeter to "," and the row delimeter to CRLF?

Answer 2

I used BULK Import with little programming to settle the files and it provided me same speed as that of SSIS package but more control over the import too.

Answer 3

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.

Answer 4

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.

Answer 5

I think this was a problem with DTS / SQL Server 2000 also. It would pain me that they havent corrected this. The learning curve, as it is, is steep for me!

Answer 6

Same issue. In my case, the target CSV file has header & footer records with formats completely different than the body of the file; the header/footer are used to validate completeness of file processing (date/times, record counts, amount totals - "checksum" by any other name ...). This is a common format for files from "mainframe" environments, and though I haven't started on it yet, I expect to have to use scripting to strip off the header/footer, save the rest as a new file, process the new file, and then do the validation. Can't exactly expect MS to have that out-of-the box (but it sure would be nice, wouldn't it?).