SSIS Package Connections

Question

SSIS Package Connections

tripri

SSCommitted

Points: 1584
More actions
August 31, 2010 at 11:24 am

#387706

Hi,
I need a help from you guys regarding modifying connections in SSIS Package as m new to these concepts.
I'm using SQL Server 2008 and i've designed an SSIS package which performs basic ETL job from a database in one server to database in another server.
So initially while designing the package i gave the connections like
Source
Server->Server1
Database-> DB1
Destination
Server->server2
Database->DB2
Now if i want to execute that package on different servers like
source connections
--------
Server->server3
Database->db3
Destination connections
----------
Server-> server4
Database->db4
like the above if i have 100 servers and DBs..
So how can i change the connections in the package automatically to the source and destination server and DBs...
Thank you..

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 1

You could use a variable for the connection, and under 1 of the menus at the top, there is a Parameter config that you can implement. You can use a table in a database as input for the parameter, or a XML file, and quickly change the values, before you rerun the package.

The package will then load from those values you have specified, and run on the new connection.

Cheers,

J-F

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 2

J-F Bergeron

SSCrazy Eights

Points: 8593

August 31, 2010 at 11:28 am

#1214503

Under SSIS -> Package Configurations

Cheers,

J-F

tripri SSCommitted Points: 1584 More actions · Answer 3

Thank you so much J-F

That clears my doubt most of it.

I got one more question

Suppose if i have given that package to 20 persons and i dont know their server and DB names. but they have server and DB's on which that package can execute successfully .

So now how can i get the connections updated to that unkown server and DB

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 4

tripri (8/31/2010)
Thank you so much J-F
That clears my doubt most of it.
I got one more question
Suppose if i have given that package to 20 persons and i dont know their server and DB names. but they have server and DB's on which that package can execute successfully .
So now how can i get the connections updated to that unkown server and DB

Well, you'd have to redeploy the package, with the new configuration file/Table, and ask them to input their "unknown database name", or input it yourself?

You'll have to redeploy it, because right now, your package does not use variables for the database connection, so fixing this first might be the key.

The whole point is to render this installation easier with the addition of the config file, now it is up to you to know which database you want to connect to...

I hope I'm clearer than mud...

Cheers,

J-F