March 13, 2009 at 4:27 am
I am working on SSIS and I have a following business requirement.
1.Table A containing list of database instances & connection strings ( all database instance have the same table structure)
2.Read connection string from the table A and assign it to connection manager ( by iterating through connection strings one by one)
3.Table B contains names of tables to be copied from OLE DB source to OLE DB destination.
4.Read the names of tables to be copied from OLE DB source to OLE DB destination from table B.
5.Copy table from source to destination ( by iterating through tables)
6.Repeat Step 4 till all tables are copied from OLE DB source to OLE DB destination.
7.Execute stored procedure ( perform business logic)
8.Delete the tables copied from OLE DB source to OLE DB destination from OLE DB destination.
9.Repeat step 2.
I am done with step 1,2,3,4 i.e dynamically assigning connection strings to connection manager. I am now stuck up in step 5 where I dynamically change tables( for e.g there are ten tables that have to be moved from source to destination. Source will have ten tables with data and destination will have all the ten tables with same structure with no records in it. But schema of these ten tables is different). OLE DB source and destination can be linked by creating mappings. So , I have created mappings initially, but as I change tables dynamically, SSIS is not creating mapping dynamically, so throwing an error. Am not sure of how mappings can be changed ( or metadata) at dynamically.
Can anyone please help me in this regard?
October 19, 2010 at 3:31 pm
Any luck with this? I have to do something similar.
October 19, 2010 at 8:59 pm
I am really sorry i can't help you at this moment because i just start working SSIS Job. Please update us if you find solution, Its really help for us. Good luck!
October 20, 2010 at 12:24 am
You cannot control mappings dynamically at run time in SSIS, using the built-in components.
If you have the money and inclination, this may work for you (I have not tried it myself).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 20, 2010 at 12:55 am
If you are handy with T-SQL, you can create a loop over some dynamic T-SQL that will accomplish the same thing.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply