I have a table (Say Table A) that lists about 10 or 15 tables, each have a different number/names of columns. I need to create a data flow which follows the same pattern for all those tables. So I have a Foreach loop in SSIS package that loops through all the records in Table A, saves the name of the 10 or 15 tables in a variable and does a data flow operation.
Within the data flow, it registers the design of the first default table which I give it, with names/no of columns and it works perfectly, Problem is when it goes through the second table, it has its own columns and that is where it throws an error saying that meta data for tables it not matched or something like that. Basically table definitions are different.
How can I make SSIS dynamically create the design of these tables, I really don't want to have 15 different data flows for each table.
Suggestions please.
Sad to say, SSIS itself does not allow such scenario. SSIS is bound to metadata, i.e. column names and data types; moreover, it checks for a match before running the dataflow tasks.
You have to create a dataflow for each table layout; you can extend your design with several dataflows and conditional invocation with task precedence constraints. BIML can help you do generate SSIS package based on your metadata or code, but the limit stays the same - data design has to be fixed before running the package and cannot be altered