SSIS deployment issues - Dynamic configurations for Environments

0
votes

I have a SSIS package using BIDS 2008 which is for SQL Server 2008 R2.

I was successfully able to deploy the package along with config file on a shared folder on the server . A SQL Agent job is configured to execute the package . It works fine on the DEV. While doing package deployment on the QA environment I noticed that I need to change the server names from the connection strings specified in the config file as well as amend the package .

My worry is the next time I do enhancement to the package I would need to reconfigure package file which is error prone.

Looking for dynamic configuration solution for various environments.

1
ssis

1 Answers

1
votes
  • You may create Environment Variables on server and assign value as DEV/QA/UAT/PROD.

  • Configure your package to read this values so that you don't have to change during deployment.

     Example :
 %Environment%\MyPackageStore\ProjectName\Package.dtsx

you can fetch value and assign to variable at package level to build a connection string : enter image description here

If you want to Configure SQL Agent Job, here is the example : enter image description here