Dynamically modifying flat file connector based on variable header row

I'm loading daily polling files from 3000 store POS systems. The data is csv with a header row containing the column names.

Unfortunately, the POS system allows the order of the columns in the polling file to be modified at the store level. The same columns will always be there, just in a different order.  Again, the first row of each file contains the column headers, in the appropriate order.

Is it possible to dynamically set the input columns of the flat file connector as each file is processed

We currently pre-process each file in VB, then re_format and write out to a consolidated file for bulk loading into SQL.   We are trying to move to real time processing where we handle a single file at a time as they arrive. If possible I'd like to do it all in SSIS without having the VB preprocessor.

Any thoughts

   Thanks, Mike

Answer this question

Dynamically modifying flat file connector based on variable header row

JReuben
I suggest you either use a custom component or a script component. The code you have in your VB you can call in you can have in your component.

You can place the code in the preexecute method that works out which field each column is in and then populate the flow accordingly.

If you want some code let me know
David_bubu

Unfortunately, there is no easy way to do that.

The other way, I see, is to open the package programmatically, get the flat file connection manager object and then change the order of its columns. That would probably gain you some preprocessing performance in comparison to reformatting files, but you would need to programmatically adjust the package to your current file, and it won't let you process multiple files with different column orders in the same session.

Thanks,< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Dynamically modifying flat file connector based on variable header row

Answer this question