Dynamic Database Source Changing

Hi,

I am building a data warehouse for a customer who has systems located in two different countries.

I need to import that data from four seperate databases, which all share the same structure.

To do this i have created 20 packages to import that data from the source database. What i would like to do, is at run time set which database the SSIS package should get its data from.

In sql 2k this was easy with a global variable that was set, then use a dynamic properties task to set the data source.

How can i achieve the same result in SSIS? the data source is an ODBC connection, with the four ODBC connections having similar names, eg ABC_NZ, ABC_AU

Thanks in Advance!

Truby

Use a ForEach Loop Container to loop over your collection of ODBC connection names.

Upon each iteration, set the connection string of the connection manager. This technique is described here: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx although in this exampe it talks about using a flat file conneciton manager which is not what you want. The principle is the same though.

-Jamie

|||

Hi Jamie,

Thanks for that, it would be perfect if i could run the database extracts from each system at the same time, but i need to be able to schedule the extracts at different times due to different time zones.

what i really want to be able to do is specify the connection at run time.

eg set variable "datasource" to be "ABC_NZ", and that will point at the ABC_NZ ODBC.

Truby

|||

AHA. You can supply this information on the command-line. use the /SET option of dtexec.exe

And use dtexecui.exe to build the command-line for you.

-Jamie

|||

Hi,

I had a similar problem like you. I recognized that it is helpful to use one or more global variables which hold the infomation about the data source. The variable(s) could be set during runtime (i.e. from a db table) and finally you can dynamically change a connection in the connection manager when you click on the connection, properties, expressions. Under expressions you might use your variables to set up a new connection string dynamically.

Example for OLE DB:

"Data Source="+@.[User::Address]+";User ID="+@.[User::UserID]+";Initial Catalog="+@.[User::CatalogName]+";Provider=SQLOLEDB.1;Password="+@.[User::PWD]+";"

If you are not sure about the structure of your connection strings then have a look under:

http://www.connectionstrings.com/

Hope that helps.

Regards,

Stefan

|||

Hi,

You can create a package configuration file and specify Connections being set dynamically from SQL AGent or a schedule job.

Follow

In the Integration serivice screen select package configuration Create a file and select the ODBC connection items as configurible. Remember to copy the .dtsconfig file in the place where ur deloying the package.

Once you have done this.

Create a Schedule job under the steps u select the pakage. After setting the package you can go to the connection tab and then change the datasource and the connection strings to what ever you want and leave it.

Like that you can create multiple scheuler for the same dts package and make it run in different time zones according to your requirment.

Hope this helps a bit

Mani