SSIS - Flat file source changes slightly (breaks everything)

[andrewhh82]

Hi,

I'm new to SSIS (using 2008). I put together what i thought was the correct way to import data from a tab delimited flat file, do some manipulation to it, and then insert it into SQL Server. Worked great. Then i had a slightly different flat file. It had all the same columns but a few additional ones scattered through out. The columns i needed were all named the same as the original file. I was hoping that SSIS would only grab the columns i specified in the first file. I copied this new file over the old flat file. And it broke everything. The data types changed, new columns were added and nothing was mapped correctly.

Can anyone tell me the correct approach for this. I will need to import from several different flat files. They all have the subset of columns i need to import, with the correct column names. Then they will all also have some erroneous columns scattered throughout. I simply want to ignore these in the import process. Any help on the best way to accomplish this would be greatly appreciated!

-Andrew

 

[Alan0568]

Andrew,

Obviously the file should not change, try to give whoever has put you in this situation a kicking ;-)

Failing that, the best way I have dealt with similar is to firstly import the file (all columns) as one single long column. Then once you have that within the flow you can use ‘conditional split’ , data conversion & derived column (or even the script task) to obtain just the data(columns) you require whilst protecting yourself from further failures caused by changes.

I have simplified the answer as it can actually become quite complex but I have had success with this approach.

Hope that’s helps.

A

 

[Alan0568]

I intended to elaborate on the derived columns etc, use the various functions such as substring, right etc to pick out the columns

A

 

[MDXer]

In one data flow you could try to import the source file into a raw file and then see if you can extract only the columns you need by name in a second dataflow.