SSIS how to set connection string dynamically from a config file

Question

Hi,

I am using sql server integration services(SSIS) in sql server business intelligent devolopment studio.

I need to do a task --that is.

I have to read from a source database and put it into a destination flat file.But the same time the source databse should be configurable.

That means in the Oledbconnection manager connection string should change dynamically.this connection string should be taking from a configuration/xml/flat file.

I read that i can use varaibles and expressions to change the connection string dynamically.But how do i read connection string value from a config/xml/flat file and set the variable?

This part i am unable to do.Or is this the right way to achieve this..

Can we add web.config files to ssis project.?

I am new to SSIs.

Please provide some help with examles etc. and this is quiet urgent for me.

Thanks

SNA.

Answer 1

Here's some background on the mechanism you should use, called Package Configurations: Understanding Integration Services Package Configurations. Here's a walkthrough of setting up a configuration on a Connection Manager: SQL Server Integration Services SSIS Package Configuration - I do realize this is using an environment variable for the connection string (not a great idea), but the basics are identical to using an XML file. The only step(s) you have to change in that walkthrough are the configuration type, and then a path.

Answer 2

Some options:

1. You can use the Execute Package Utility to change your datasource, before running the package.

2. You can run your package using DTEXEC, and change your connection by passing in a /CONNECTION parameter. Probably save it as a batch so next time you don't need to type the whole thing and just change the datasource as required.

3. Or you can use the SSIS XML package configuration file. Here is a walk through.