none
How to deploy SSIS packages to multiple platforms?

    Question

  • I have created a suite of packages in a solution which I now need to deploy to Test and Production platforms.

    I finally figured out how to use configuration files to specify the connection string for the target database (i.e. by manually entering the password into the config file, when using SQL Server Authentication in the Connection Manager), but I still have a logical conundrum. I need to be able to deploy to 3 different configurations (Dev, test, Prod), each of which will have its own connection string. I obviously don't want 3 versions of the ETL pointing to the same config file, so how do I get each deployment to point to its own config file?

    Do I need to point each package to a platform-specific config file before deploying, then do it all over again to deploy to the next platform?

    Or, can this be done after deploying the ETL, during creation of SQL Server Agent jobs to run the packages?

    
    
    
    
    Tuesday, July 16, 2013 5:29 AM

Answers

  • I have discovered by experiment that I can use the Job Step editor in SQL Server Agent to assign a configuration file when I create a job. This is the perfect solution for me, because I can keep 3 versions of the config file and assign the appropriate one when I set up the ETL jobs & schedules for each target deployment.

    Too bad MSDN didn't mention this in anything I read about configurations and deployment.

    
    Monday, July 22, 2013 3:26 AM

All replies

  • If you're using 2005 or 2008 you could use the Package Deployment Utility.


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Tuesday, July 16, 2013 6:10 AM
  • Thanks SSISJoost,

    I have used the Deployment Utility, but I forgot about the Configuration Manager button, which I have not used before. I'll look into that now.

    Wednesday, July 17, 2013 12:05 AM
  • This doesn't work for me. I created 3 Solution Configurations ('Dev', 'Test', 'Prod'), then separately selected each one and set up its configuration file. Now, when I select any of the Solution Configurations, I still get the most recently set configuratio file.

    I still have the same configuration file for every kind of deployment. What does this thing take to get it to make sense?

    
    
    Wednesday, July 17, 2013 4:22 AM
  • In case my last message wasn't clear, if I now select the 'Dev' Solution Config and build it, the 'Prod' version of the Config file is what ends up in the Deployment folder, when I was expecting it to be the 'Dev' version.

    I don't think I've ever spent so many days reading circular documentation and running fruitless tests on something that is so conceptually simple. I've created more useful deployment utilities using DOS batch files in the bad old days.

    Wednesday, July 17, 2013 4:41 AM
  • Hi Allen,

    Where is the Configuration file saved in your machine?

    How does your package identify that location?(Is it through Environment variables?)

    All that now you need is, Package pointing to one configuration filter. Now when you deploy to 'test', you deploy the package in 'test' server and place the configuration file(With details edited for test).


    Rajkumar

    Wednesday, July 17, 2013 5:41 AM
  • Thanks, Rajkumar,

    I've been saving the config files to a single location, thinking that each solution config I created could be made to point to a different config file, but that obviously doesn't work.

    It sounds like I need to manually replace the config file each time I deploy to a different target, or use an evironment variable and change its value each time. Either way, I'll actually be managing the configuration outside SSIS.

    Perhaps Microsoft should have just called it a Configuration Exporter instead of confusing people by implying that it is capable of managing anything.

    I'll try the environment variable approach; it sounds like the least error-prone method.

    Wednesday, July 17, 2013 11:18 PM
  • I have discovered by experiment that I can use the Job Step editor in SQL Server Agent to assign a configuration file when I create a job. This is the perfect solution for me, because I can keep 3 versions of the config file and assign the appropriate one when I set up the ETL jobs & schedules for each target deployment.

    Too bad MSDN didn't mention this in anything I read about configurations and deployment.

    
    Monday, July 22, 2013 3:26 AM