I want to take one Table and transform its Columns into another Table - many times for different tables. Each Table's Columns are completely independently named. I am using a Data Flow Transformation Script Component to do this.
The (extremely simplified) Script would look something like:
Dim sa() as string
'Col43 is one of 50 to 90 input columns
'Name1, Name2, Name3 = 3 of 50 to 90 output columns
sa = Row.Col43.Split(',')
Row.Name1 = sa(0)
Row.Name2 = sa(1)
Row.Name3 = sa(2)
My problem is ... how can I get those columns from the Data Flow Destination - OLE DB Destination AUTOMATICALLY mapped into the Data Flow Transformation - Script Component, ready to use?
Currently I'm typing them one at a time (anywhere from 50 to 90) in Script Transformation Editor - Inputs and Outputs - Output 0 - Output Columns - Add Column with Datatype/Length, whilst looking at the Destination Table definition.
There HAS to be way to automate this ... isn't there?
You know - a "copy OLE DB Destination External Columns to Script Transformation Output Columns" button?