I need to get data from flat files to DB using SSIS. The problem here is, I have to dynamically choose the Derived Column Transformation(DCT) depending on the flat file.
Example:
Suppose I have flatfile-1 which need the DCT with 5 columns,
then if I get another flatfile-2 which needs DCT with 10 columns. So how can I dynamically choose the DCT basing on the flat files?
I have to do it for many tables ... kindly share your thoughts.
I think I got a way to do this, I am writing a C# code for generating the SSIS package. So for each flat file I get in a loop I will process it basing on the number (Each file belongs to a table which is indicated by a number.) Now when I get the flat file I am extracting its name (which contains the number) then on the fly I am getting the table name and the columns of this data group by querying on meta table and use the obtained columns as the columns for "Derived Column Transformation" and basing on the positions (this is in my case where the flat file import is done basing on their positions.) I think by generating the package pro-grammatically we will be able to generate packages for each flat file. Hope this helps.