How to transfer a ssis package from Dev to Prod?

Question

I'm trying to move my packages to production using a configuration file, but file is changed only partly and the results go still to DEV server.

Does anybody know what to do?

Answer 1

Hi,

It is difficult to isolate the cause of your issues without access to your configuration files.

What I suggest you do is make use of package configurations that reference a database within your environment. The databases themselves can then be referenced using environment variables that are unique to each environment.

This a brilliant time saver and a good way to centrally manage the configuration of all your SSIS packages. Take a look at the following reference for details.

http://www.mssqltips.com/tip.asp?tip=1405

Once configured, you can deploy the same identical package between dev and production without needing to apply a single modification to the SSIS package or mess around with configuration files.

Answer 2

You could still have hard-coded connections in your package even though you are using a configuration file. You'll need to check every connection as well.

Answer 3

You can also go the long way around. Go into Integration Services and Export the stored package to its dtsx file. Then you can pull open the file in any good text editor, do a find/replace on your server name and then go back into Integration Services and Import the updated package. Alot of times it's just easier...

Answer 4

Hi, everybody and thanks for answering. I'd managed to solve this problem in an ugly way - editing packages on server, but I'd like very much more elegant solution - now I'm trying with environment variable,it seems great, but the wizard that I'm getting is different from that is given in link - and I don't know how to continue.(I'm using VStudio 2005) Besides, I tried configuration file as XML, but package run fails even on the source machine, so I'm stuck !

Answer 5

My personal technique has been to first have a single config file that points the package to a SQL Based Package Config (the connection string to the config DB). Subsequent entries in the package config use the SQL store to load their settings. I have a script that goes into the XML of the package and preps them for deployment to stage or prod. A config file holds the name of the Package Configuration's initial file config entry and where the stage and prod configuration db configruation file is located. The script produces two subdirectories for stage and prod. Each directory has a copy of the solution packages modified for their particular deployment.

Also! Don't forget to turn off encryption in the package files!