I have a data flow in SSIS that's using an ODBC Source to a conditional split.
The source returns a dynamic set of columns dependent on availability of data in the source - the number of columns goes from 1 to 13.
In my conditional split I have it pointing at the source and feeding the data to a destination that fits its number of columns.
Example:
Condition 1 -> Map column 1 to column 1 and ignore the other 12 columns Condition 2 -> Map column 1 and 2 to column 1 and 2 and ignore the other 11 columns
However, if the source only contains 1 column it fails on the second condition because "there are some mapping errors on this path"
I know that the count of columns will never exceed 13 which means I can set conditions for columns 1 - 13.
Is there any way that I can ignore the mapping error or force SSIS to stop at the last executable case in my conditional split?
I don't personally want to have to dive into a script component so if this can be done with conditional split I'd be relieved!
Any thoughts?
As Larnu indicates, the number of columns in a data flow is a design time artifact and cannot be changed at run-time.
But, you should be able to handle this with 12 data flows.
Execute SQL Task -> However your current ODBC source is generating a variable set of columns, determine how many are being returned. Assign this to an SSIS Variable @[User::ColumnCount]
Attach 12 output paths from the Execute SQL Task to custom Data Flow Tasks that account for the number of source columns.
Change the precedence constraint on each of the paths to be Constraint and Expression with expressions like @[User::ColumnCount]==1 ... ==13
The SSIS designer is going to try to validate metadata as you design the package. As will the execution engine when you run the package. Therefore, you'll need to set the Delay Validation property to True on each of the Data Flow Tasks after you finish designing them.
In fact, as I think about this more, you'd like be better served by a parent/child package paradigm here. Design a package per data flow task and then have the parent/controller package invoke them much as I described above. That should simplify the metadata validation challenges you'll experience trying to get this built.