How to change the connections in SSIS package after deployment?

0
votes

Just started to work on a SSIS ETL package at work and I am sort of stuck thinking how to solve this problem and how others solved it. Since I am fair new to SSIS, I must ask the experts here. So I am creating a SSIS ETL package which I create source and destination connection (mostly relational databases) in a development environment, which means the source and destination connections are to development databases. Everything works out fine. The problem is when I complete the SSIS package, build it, create the manifest and deployed, then I realize the package will fail, because I have deployed the package in the production environment, which means the source and destination connection are to production databases, in production environment.

So my questions is how do I create a ssis ETL package in the development phase or development environment using development source and destination connections be able to deploy a package into a different environment where the source and destination connection are different?

I am using sql server 2012 with BIDS 2012, Data Tools 2012.

2
sql-serverssis
You need to create a config file (xml). simple-talk.com/sql/ssis/… – FutbolFan
Wrong simple talk article. In SQL 2012 you use environments: simple-talk.com/sql/ssis/… – Nick.McDermaid
You could skip the Environment part of the excellent article Nick linked and just do a direct configuration. Whereas this image is pointing at the Parameters tab, click on the Connection Manager tab and there you can specify the new connection string. – billinkc
Thank you for all your the advice. It sounds like I can either use environments or package configuration to do this. I will give it a shot and see if it'll work out. – user2103970

2 Answers

0
votes

You can make the package connection configurable.

  1. select "SSIS">> Pacakage Configuration >> navigate to connection >> select servername and database name
  2. Click next
  3. Save pacakge configuration to dtsconfig file.

During deployment modify this dtsconfig file and specify.

0
votes

once you have deployed your package navigate to Integration Services Catalog -> SSISDB -> your SSIS Project. Once there open up the desired package and under [Connections] tab you can change the connection. Alternative to this (method 2) is to create Environments; where you can quickly change between dev, qa, prod by checking towards the bottom which environment you wish SSIS to pick up config from.