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.
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.