How to pass a connection string into an SSIS file?

Question

I have an SSIS file created using the Import and Export wizard. Using that wizard seems to hard-code the connection string into the SSIS file; what I'd like to do is pass the connection string into the file so that I can configure the target database. Anybody know how to do this?

edit A little more info: Technically I've got 17 SSIS packages that I am executing from a PowerShell script using dtexec. I'd like the PowerShell script/dtexec to be able to pass the connection string into each SSIS package. I created these packages using the SQL Server Import/Export wizard.

Answer 1

Assuming the package is in C:\ssis, your connection manager is named MyConnectionManager, this will override the the value and point it to localhost.

dtexec /file C:\ssis\pkg1.dtsx /conn "MyConnectionManager";"\"Data Source=localhost\TestSQL2008R2;Initial Catalog=ConnDB;Integrated Security=SSPI;\""

Answer 2

The sample provides the best simple solution to promote the SSIS across environment. Refer the below blog by Rafael Salas to understand more options. Understanding Integration Services Package Configurations