none
SSIS environment variables for SQL instances – SQL Server 2008 RRS feed

  • Question

  • Hello everyone,

    I have a small challenge that I'm facing.

    CONTEXT: I am doing this project for this client where they are still using SQL Server 2008R2. There are three environments Dev, Test, and Prod, as the case usually is. However, these environments are down as instances of a SQL Server installation which is sitting on a single Windows Server. I am working on the SSIS packages that use SQL Server package configuration. I am trying to get an indirect configuration by using environment variables.

    QUESTION: How do I set environment variables for different environments of instances sitting on only one Windows Server system? If there were three separate standalone servers I would have individual environment variables with the same name across all the three servers and just change the connection property. Does anyone have a better approach on how to configure SSIS packages for environments such as I have described above?

    Please note that at the moment the client is using XML Configuration, which I’m finding to be so cumbersome and clunky with so many files scattered everywhere. I wanted to bring everything into one SQL table according to the environment.

    Many thanks,  


    Mpumelelo

    • Edited by Mpumelelo S Wednesday, October 31, 2018 3:10 PM
    Wednesday, October 31, 2018 2:51 PM

Answers

  • I have found that whether one uses three different XML files for configuration or the variable approach, both work but both have their own drawbacks, depending on how one looks at it. The method that I have opted for is described below.

    I am not using XML Config files at all anymore. Instead, I have added a variable to each package that defines the environment. I called that variable EnvironmentName. I then configured that variable to the SQL Config Table and set its configuration line to the first position in the list of package configurations. The value that loads the variable EnvironmentName is set to come from the SQL Server Agent job under the option “Set Values” for each step that runs a package. Details of how this can be done are on this web link:   http://www.bradleyschacht.com/override-ssis-package-variables-without-opening-the-package/

    All is working well now and thank you everyone for your help.

    Kind regards,


    Mpumelelo

    • Marked as answer by Mpumelelo S Friday, November 2, 2018 10:11 AM
    Friday, November 2, 2018 10:11 AM
  • Hi Mpumelelo S, 

    Since all the Dev, Test and Prod instances are on the same machine, indirect SQL Server Configuration will not be a good choice. 

    You can use indirect XML configuration in the packages, and put all the configuration files into Dev/Test/Prod folder. When you want to run the package in Test environment, copy the Test Config file into config folder(stored in environment). 

    Please check this link:

    Defining a Configuration Approach for Integration Services Packages


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Mpumelelo S Friday, November 2, 2018 10:11 AM
    Thursday, November 1, 2018 7:33 AM

All replies

  • Hi Mpumelelo S, 

    Since all the Dev, Test and Prod instances are on the same machine, indirect SQL Server Configuration will not be a good choice. 

    You can use indirect XML configuration in the packages, and put all the configuration files into Dev/Test/Prod folder. When you want to run the package in Test environment, copy the Test Config file into config folder(stored in environment). 

    Please check this link:

    Defining a Configuration Approach for Integration Services Packages


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Mpumelelo S Friday, November 2, 2018 10:11 AM
    Thursday, November 1, 2018 7:33 AM
  • Yes you can do it in Sql server table, Please check the link below

    https://mikedavissql.com/2011/10/04/using-configuration-tables-in-ssis-20082005/


    Cheers,

    Thursday, November 1, 2018 8:08 AM
  • You can just have three environment variables indicating the environment info if you want

    Like CONNSTR_DEV,CONNSTR_QA,CONNSTR_PROD

    then store the connection strings for instances inside variables to point to the table which has config values inside each instance

    If you dont want to do that then another method is to use XML document with connectionstring info and put them inside three separate locations corresponding to each instance


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Thursday, November 1, 2018 9:12 AM
    Thursday, November 1, 2018 9:12 AM
  • Thank you for all the suggested solutions that have been given so far.

    @Yang – I’m not sure if I fully understand your suggested approach. I think the challenge with the solution that you have suggested is that I will have to reset the XML configuration for each package each time I deploy to a separate environment. There many packages for me to go through them one by one.

    @Visakh16 – “You can just have three environment variables indicating the environment info if you want”. How will the packages know that I have deployed to Test or Prod?

    Below is the approach that I have used but it is not perfect.

    1)  I have put all my bits and pieces of package related configurations in a SQL Config Table.

    2)  Now, here is where the challenge was because of the different environments being on the same machine. Instead of using the environment variable for the indirect configuration, which was not possible, I have used XML Configuration for the same purpose as suggested in the above postings. There is however one difference that I have made to my setup. Instead of three separate XML config files, I have only one for all the three environments. I have decided to use only one file to avoid having to reset the configuration pointing to the XML file on each package when the package are deployed to their respective environments. What I have done about use of one XML config file is that, on the SQL Server Agent jobs, Step 1 is set to define the environment by creating a new XML config file on the C:\ drive with the relevant connection string for the presenting SQL Config Table. I achieve that using PowerShell. The caveat about this though is that jobs in different environments cannot be scheduled to run at the same time as that will create a conflict on the XML Config file.   

    Generally, the challenge is on the name(s) of the XML config files or location where those files are kept. Its either one of the other, not both presenting at the same time.


    Mpumelelo

    • Edited by Mpumelelo S Thursday, November 1, 2018 2:47 PM
    Thursday, November 1, 2018 2:46 PM
  • I have found a better and more robust solution which works perfectly well. I will post it tomorrow. Time to go home now.

    Mpumelelo

    Thursday, November 1, 2018 5:01 PM
  • Thank you for all the suggested solutions that have been given so far.

    @Yang – I’m not sure if I fully understand your suggested approach. I think the challenge with the solution that you have suggested is that I will have to reset the XML configuration for each package each time I deploy to a separate environment. There many packages for me to go through them one by one.

    @Visakh16 – “You can just have three environment variables indicating the environment info if you want”. How will the packages know that I have deployed to Test or Prod?

    Below is the approach that I have used but it is not perfect.

    1)  I have put all my bits and pieces of package related configurations in a SQL Config Table.

    2)  Now, here is where the challenge was because of the different environments being on the same machine. Instead of using the environment variable for the indirect configuration, which was not possible, I have used XML Configuration for the same purpose as suggested in the above postings. There is however one difference that I have made to my setup. Instead of three separate XML config files, I have only one for all the three environments. I have decided to use only one file to avoid having to reset the configuration pointing to the XML file on each package when the package are deployed to their respective environments. What I have done about use of one XML config file is that, on the SQL Server Agent jobs, Step 1 is set to define the environment by creating a new XML config file on the C:\ drive with the relevant connection string for the presenting SQL Config Table. I achieve that using PowerShell. The caveat about this though is that jobs in different environments cannot be scheduled to run at the same time as that will create a conflict on the XML Config file.   

    Generally, the challenge is on the name(s) of the XML config files or location where those files are kept. Its either one of the other, not both presenting at the same time.


    Mpumelelo

    You told they're separate instances right?

    Based on instances where you deploy the package you can use the corresponding variable name you created


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, November 1, 2018 5:55 PM
  • I have found that whether one uses three different XML files for configuration or the variable approach, both work but both have their own drawbacks, depending on how one looks at it. The method that I have opted for is described below.

    I am not using XML Config files at all anymore. Instead, I have added a variable to each package that defines the environment. I called that variable EnvironmentName. I then configured that variable to the SQL Config Table and set its configuration line to the first position in the list of package configurations. The value that loads the variable EnvironmentName is set to come from the SQL Server Agent job under the option “Set Values” for each step that runs a package. Details of how this can be done are on this web link:   http://www.bradleyschacht.com/override-ssis-package-variables-without-opening-the-package/

    All is working well now and thank you everyone for your help.

    Kind regards,


    Mpumelelo

    • Marked as answer by Mpumelelo S Friday, November 2, 2018 10:11 AM
    Friday, November 2, 2018 10:11 AM