February 25, 2014 at 8:47 am
Question for you guys - what have you found as the best way to preprocess flat txt files to prepare them for SSIS? For example say the 3rd party vendor has issues with their files that don't follow the specifications, but it's to a point where it so common that you have an automated fix for it. However they are formating issues that break the SSIS Flat File Source object.
Examples: End of line format changing (CR vs CRLF), "NULL" appearing in numeric columns, delimiters changing (e.g. normally a pipe, but occasionally appear as commas instead).
The obvious solution for me was to write some C# to do preprocessing, but at that point you might as well do the whole thing in C# as it's inefficient to read the whole file in memory to prevalidate, then re-read it again to load it.
February 25, 2014 at 8:55 am
sometimes this comes down to being able to maintain the code in the future, as it sounds like your will be finding new issues as time goes on. If you are familiar with .Net code, then add the fixes into a script task before the data flow task. Yes, you could load the data with the SqlBulkLoad objects and get the same speed as SSIS, but will the next guy easily understand the code?
February 26, 2014 at 5:42 am
"Examples: End of line format changing (CR vs CRLF), "NULL" appearing in numeric columns, delimiters changing (e.g. normally a pipe, but occasionally appear as commas instead)." If with Changes within the same file you are in a problem.
One possible solution is may to use bulk insert into a staging table with one column varchar(max).
In that cloumn you can test for certain conditions for each row in the text file with SQL statements
and then split the row into the apropriate number of columns and insert in a useable table.
I use this typ of procedeure then I import text files.
Best regards
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply