How to import a CSV file of variable recording length using SSIS?

Question

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

I am trying again and again to try to get a CSV file in a SQL Server table using SSIS, where the input file has different record lengths. On this issue, two different record lengths are 63 and 326 bytes. All record lengths will be imported into the same 326 byte wide table.

Imported over 1 million records.
I do not control the creation of the import file.
I have to use SSIS.
I confirmed with MS that this was reported as an error. I tried several workarounds. Most of them were where I try to write my own code to intercept the record, and I cannot make it work as I want.

Answer 1

I had a similar problem, and I used special code (Script Task) and the Script component on the "Data Stream" tab.

I have a flat file file that feeds into the Script component. Inside, I use code to control incoming data and fixes to the recipient.

My problem was that the provider was using "000000" as dates are not available and another coloumn had a fill / crop problem.

Answer 2

You should have no problem importing this file. Just make sure that when creating a flat file connection manager, select the Delimited format, then set the SSIS column length to the maximum file column length so that it can accommodate any data.

It looks like you are using a fixed-width format, which is not true for CSV files (since you have a variable-length column), or you might have incorrectly configured the column delimiter.

Answer 3

Same problem. In my case, the target CSV file has header and footer entries with formats completely different from the file body; Header / footer are used to check the completeness of file processing (date / time, number of records, amount of sum - “checksum” by any other name ...). This is a common format for files from mainframe environments, and although I haven't started using it yet, I expect to have to use scripts to disable the header / footer, save the rest as a new file, process the new file, and then check . Can’t exactly expect MS to get this out of the box (but that would be nice, right?).

Answer 4

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

Answer 5

You can write a script task using C # to iterate over each line and overlay it on the desired number of commas to unload the data. This assumes, of course, that all data is aligned with the corresponding columns.

those. when you read each entry, you can "count" the number of commas. Then simply add X the number of commas to the end of the entry until it has the correct number of commas.

Excel has encountered a problem causing this type of file to be created when converting to CSV.

If you can do it “manually”, the best way to solve it is to open the file in Excel, create a column for the “end” of the record and fill it to the end with 1 or another character.

Unpleasant, but can be a quick fix.

If you are unable to do this, you can do the same programmatically, as described above.