SSIS how to when columns names change
-
Hello,
I'm in a situation where I have to simply extract some info from an excel file, do some data conversion, a lookup and inserts them into a table.
Until there, no problem unless that my client has told me that the columns names of the destination table will change in the future. Not every month but... say every 2-3 months.
How can i keep the columns mapping in my package ?
When a change occurs, the client send an excel file with the old columns names and the new ones.
Do you have an idea ?
Thanks
-
SSIS is very sensitive about metadata changes.
With out-of-the-box SSIS, there is not much that you can do than manually remap the columns each time they change.
But maybe you could try scripting. Try to write a script task that opens the Excel file and renames every column to the first set of columns you configured. Then read it in with your dataflow.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
That won't work - it's the destination that's changing, not the source.
Quite horrible and a very unusual practice. I suggest you tell them it can be done only manually and take their money every time they change. Or teach them how to do it (more money!).
-
Phil Parkin (10/8/2010)
That won't work - it's the destination that's changing, not the source.Hmmm. Reading and Friday afternoon, things that don't mix too well.
All right. Read the damn thing into a temp table and use a dynamic insert statement to insert your data into the destination table. The client can configure the column names himself in a configuration table. You just read the configuration table to find out which column names to use and you create your insert statement on the fly. (this will work as long as the number of columns don't change, just the names)
edit: or, instead of using a config table, you can use the excel file with the mapping. As long as the structure of that Excel file doesn't change, of course
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
If you can use third-party solutions, check the commercial CozyRoc Data Flow Task Plus. It can modify the SSIS data flow metadata at runtime, dynamically including / excluding columns. You can also define mapping dictionary between the source and destination, to guide column mapping process.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply