SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS: Automatically map source to destination when new columns are added.


SSIS: Automatically map source to destination when new columns are added.

Author
Message
thomas_emerson
thomas_emerson
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 59
Hi, Any help would be appreciated....

We run 50+ reports that have the exact same "standard data" fields, just different criteria. Because of corporate rules, we must do all of our processing in one server and push all the output tables to another server; we are not allowed to do cross-server querying. On the source side, we run through a SPROC that uses dynamic SQL to create different table-names for each output so that we don't have conflicting output table names. So, depending on which report we are running, we dynamically name it ##ReportOutput_01, ##ReportOutput_02, etc. Where the 01, 02, parts are variables assigned to that particular criteria. On the destination side, we maintain an empty table as a template that contains the destination columns and use that to create our various destination output tables each time the process runs. The source/destination column names are always identical.
so.....

Let's say, for sake of this question, that the standard fields are RECORD_ID, COLUMN_1, COLUMN_2, and COLUMN_3. Let's say business tells us that we need to add COLUMN_4. I can easily add COLUMN_4 to the source SPROC and the destination template - but we have to open up all 50+ SSIS packages to actually do the mapping of the new column manually.

Is there an SSIS setting or something that says "automatically re-map all columns by name every time the package runs"? It would save us a lot of busy-work time every time there is an update.

If anyone knows, I will be very thankful.

Thomas
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63336 Visits: 13298
Nope, there isn't such a thing in SSIS. SSIS is awful when it comes to handling metadata changes.
However, it can be possible that there are commercial components who can do this. (check out CozyRoc).


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
chrisjamesb
chrisjamesb
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 16
I was looking for an answer to this but I didn't see it, now that I have an answer this is what I did for dynamically adding new columns. Since my data is created new each day I just added an execute SQL task and dropped and recreated the table from the data source. Ensuring that I used a SELECT into.

Example:
drop table [yourtable]
select * into [yourtable] from [otherdb].[dbo].[servertable]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search