Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS: Automatically map source to destination when new columns are added. Expand / Collapse
Author
Message
Posted Thursday, July 29, 2010 3:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 30, 2012 4:10 PM
Points: 4, 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
Post #961048
Posted Thursday, August 5, 2010 5:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 13,359, Visits: 10,223
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #964147
Posted Thursday, September 19, 2013 12:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 13, 2013 3:38 PM
Points: 3, Visits: 9
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]
Post #1496533
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse