January 8, 2019 at 12:44 pm
In developing an SSIS package that processes data and writes the output to a table with a 100+ columns, is there a way to change the target table name without having to remap all the columns?
For example, in one case the target table name was changed but the columns were left the same. The SSIS component for the destination 'forgot' all the column mappings and they had to be entered in the GUI again manually. In another case I was using an identical target table for testing before using the final table. In each case, the mapping was lost when the table name was changed. Are there any ways to avoid this?
January 8, 2019 at 1:13 pm
bwelch42 - Tuesday, January 8, 2019 12:44 PMIn developing an SSIS package that processes data and writes the output to a table with a 100+ columns, is there a way to change the target table name without having to remap all the columns?For example, in one case the target table name was changed but the columns were left the same. The SSIS component for the destination 'forgot' all the column mappings and they had to be entered in the GUI again manually. In another case I was using an identical target table for testing before using the final table. In each case, the mapping was lost when the table name was changed. Are there any ways to avoid this?
If you make the source column names match the target column names, you can right-click and select 'Map Items by Matching Names'.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 8, 2019 at 2:08 pm
The package is just an XML file. In some cases, it's easier to make simple changes in the XML rather than using the GUI. I believe that this is one of those cases, but it's been a long time, so I'm not absolutely sure.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 8, 2019 at 2:26 pm
If the potential target tables of the package have the exact same structure, and you are using OLE DB connections, then in the "OLE DB Destination" task you can set the Data Access mode to "Table name or view name variable - fast load". and just assign a user variable the value of the table name you are using. I wrote a package that can dynamically read from source and destination tables in this way, as long as the various sources have the same structure, and the various destinations have the same structure it works just fine. You'll probably want to set the DelayValidation property of the Data Flow to True as well.
January 9, 2019 at 5:18 am
Chris Harshman - Tuesday, January 8, 2019 2:26 PMIf the potential target tables of the package have the exact same structure, and you are using OLE DB connections, then in the "OLE DB Destination" task you can set the Data Access mode to "Table name or view name variable - fast load". and just assign a user variable the value of the table name you are using. I wrote a package that can dynamically read from source and destination tables in this way, as long as the various sources have the same structure, and the various destinations have the same structure it works just fine. You'll probably want to set the DelayValidation property of the Data Flow to True as well.
Now that you mention it, I realise that I do this a lot and it works just fine!
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply