Dynamically creating SSIS package for each flat file

Trying to figure out the best method of reading in a number of flat files, all with different number of columns and data types and outputting them to a database.

Here's the problem: They are EBCDIC encoded and some of the columns are packed decimal. I've set up one package that takes the flat file, unpacks the decimal (Using UnpackDecimal component) and then sending the rest through a second component to go from EBCDIC -> ASCII.

What I need is a way to do this for every flat file based on the schema for that flat file. One current solution is to write a script/app to create the .dtsx XML file and then execute that for each flat file. It appears like this may be possible, but I haven't gotten far enough to know for sure. So my questions are this:

1) Is there an easier way to do this (ie somehow feed the schema to the package and use it to dynamically set up the column makers and determine which columns get fed to the unpack decimal component.

2) If there isn't a better way, will dynamically creating the .dtsx XML file based on the necessary input/output columns for each flat file work? If so, what is a good source of information on this (information about how the .dtsx XML file is set up, what needs to be changed/what doesn't, etc).

Thanks,

Travis

Trav2003 wrote:

1) Is there an easier way to do this (ie somehow feed the schema to the package and use it to dynamically set up the column makers and determine which columns get fed to the unpack decimal component.

2) If there isn't a better way, will dynamically creating the .dtsx XML file based on the necessary input/output columns for each flat file work? If so, what is a good source of information on this (information about how the .dtsx XML file is set up, what needs to be changed/what doesn't, etc).

Thanks,

Travis

1) No. SSIS can't handle dynamic columns. The best it can do it dynamically create a child package, which is no different than #2.
2) Yes, it will work. You probably don't want to create XML directly, but instead use the API to generate the package. The updated samples contain one showing how to create a basic package. You may also find this tool helpful for reverse engineering packages.
|||you might also check out http://www.aminosoftware.com they have a custom source component that will read in many forms of ebcdic (including packed, zoned, etc) and output it into ASCII with only a single pass through the data file.