Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Preprocessing Flat Files Expand / Collapse
Author
Message
Posted Tuesday, February 25, 2014 8:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 11:56 AM
Points: 228, Visits: 792
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.

Post #1544980
Posted Tuesday, February 25, 2014 8:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 291, Visits: 519
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?
Post #1544985
Posted Wednesday, February 26, 2014 5:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:50 PM
Points: 204, Visits: 1,311
"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
Post #1545344
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse