I thought I'd share a unique problem that I'm dealing with and see if we can get a discussion going as to how to best handle it.
I'm dealing with legacy clients, and we're in a position where we can't ask them change their file format.
Here's the issue. We need to load into a sql table a comma delimited, text quoted file into a sql table.
As an example when clientA came on board they only needed to send 2 columns of data, when clientB came on board they sent 4 Columns of data, and ClientC has 6 Columns of data.
We can put in nulls for for the column data that is not sent.
I want to be able to load records from the three clients above into a single sql table:
Col1 Col2 Col3 Col4 Col5 Col6
Alfred Ames NULL NULL NULL NULL
Aaron Abbot NULL NULL NULL NULL
Bob Baines 123 Blue NULL NULL
Bill Bathgate 456 Yellow NULL NULL
charlie Cass 789 green 123 anywhere USA
cathy cow 444 red 234 nowhere Germany
SSIS requires that I map input column to output column and this leads to difficulties.
1) I can have multiple data flow tasks and only execute a single one based on the number of columns in that particular txt file.
---This doesn't seem like a very clean solution to me especially with the number of different times I'd have to do this.
2) Using a script transform I parse the file and programatically create the NULL rows for output.
---I've actually done this for our smaller txt files, but this method kills performance on larger text files.
3) pre-flood the text files with column delimiters at the end of each line.
---This solution is actually similar to #2 above in that you're manufacturing columns. The transform loads quickly, but I haven't found a way to quickly add commas at the end of each line of a text file.
Have any of you faced this challenge? How did you handle it? What suggestions do you have that I might have missed?