SSIS - Is there a Data Flow Source component that will handle CSV files where the column order may change? -
We have written several SSIS packages that import data from CSV files using flat file source.
It seems now that after deploying these packages into production, providers of these files can distribute the files, where the column order of the files varies (Ask!). If this happens, our package will fail. For example, an additional column is inserted at the beginning of each row. In this case, the flat file source continues to use the existing column order, which clearly has a detrimental effect on the change!
Example Using a trivial example, the original file contains the following content:
our reference, customer, amount 235, MFI, 20000.00 236, MS, 30000.00 The output from the flat file source is:
our reference client amount 235 client A 20000.00 236 client b 30000.00 Existing unchanged package is run against this file, then the output from the flat file source is: Ideally, we will deal with this problem - that is, based on column names instead of column order Produces. Any suggestion will be welcomed our reference, client reference, client, amount 235, A424, client A, 20000.00 236, b222, Client B, 30000.00
our reference client amount 235 A. 244 Client A, 20000.00 236b222 Client B, 30000.00
It is not what I know .
To investigate the problem in order to set up two separate connection managers, with a single flat line. It can read a first line and can tell whether it is okay or not and no.
If you want to work, you can take it one step further and that flat field can only make connection to the manager, and to parse the line, a script in your flow Use the component and you need the column to flow later.
As far as I know, there is no way to dynamically add the column to the flow at runtime - so you have to add all required columns to the script task output. Whether they can be found and parsed from each line is up to you. No "new" (i.e. unexpected) columns can not be used. You can throw default or exception which columns are missing.
Before changing the Connection Manager, there is a final chance to use the SSIS object model to modify the package - or even dynamically write the basis of inspection of the input file But the object model on. I have done a lot of package generation using templates in C # and then received the main files describing mainstream files based on main metadata.
Comments
Post a Comment