July 29, 2010 at 3:25 pm
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
August 5, 2010 at 5:24 am
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).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 19, 2013 at 12:04 pm
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]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply