SSIS how to manage dynamic connection managers?

Question

Here is what i am trying to achieve,

1) Start the loop from 5 years back from current year 
2) Check if database for that year is exist if not create new one  ?
3) move the tables according to there year of creation into the database of that year ?

For this i have created the SSIS package i have added the the connection manager which connection string sets dynamically for each loop of year that connection string works fine when checking for the database existence and the creating new database but when the package try to execute the sql task for moving the data tables then it throws error as

[Execute SQL Task] Error: Connection manager "dummyserver.dummydatabase2012" does not exist.

any help would be great ..

With regard to your bounty, would you please update your question to reflect what is out of date. We need specifics here. An example of what should happen if the database already exists would be helpful along with how you're "moving" tables. Must we worry about RI? Is there a subset of tables to worry about? — billinkc, Feb 24 '14 at 13:34

score 0 · Answer 1 · answered Oct 24 '13 at 14:11

0

If you need to create a new database, create a connection manager to the Master database. You can use that one CM to work with all databases, just use the DB.SCHEMA.TABLE syntax when referencing tables (eg. select * from mydb.dbo.mytable).

answered Oct 24 '13 at 14:11

Metaphor

6,157
10
54
77

score 0 · Answer 2 · answered Oct 25 '13 at 07:53

0

You can try to create the table each time using a SQL statement like this

CREATE TABLE IF NOT EXISTS TableName ...

answered Oct 25 '13 at 07:53

Redax

9,231
6
31
39

score 0 · Accepted Answer · answered Feb 24 '14 at 07:17

0

You say you set the connection string dynamically. In that connection string make a variables for the ServerName and the IntialCatlog and assign that property values using variable names and then change that variables names as your connection demands at runtime using script task or if they coming from database then using `sql task. Let me know if any issues.

answered Feb 24 '14 at 07:17

Mahesh

8,694
2
32
53

Thanks.! It worked i had set my server name with variable but not the connections `IntialCatlog` property that was causing the error.Thanks Again. – Feb 24 '14 at 07:18

SSIS how to manage dynamic connection managers?

3 Answers3