If your SSIS solution is built with project deployment mode(not package deployment mode) and would like introduce environment variable then this post is for you.
Let's start with a few steps before going into environment variable. In general, after building SSIS solution, you get .ispac file which you take from DEV to UAT/TEST/PREPROD/PROD environment. And then by following the wizard you deploy the .ispac file under SSIS catalogue. To know step by step process to deploy SSIS package to catalogue please follow : Deploying SSIS package to Catalogue
After deployment , you will find the packages under SSIS catalogue, as like below figure 01:
At next step you will create job/Jobs to run the packages. Now say, if you don't create any environment variable then the job will like below:
Will this job execute without environment variable? Surely, it does. But the connection string still retain development server information. However, the solution is already moved to UAT or Test Environment, so you need to change the connection string manually. If Environment variable have been defined then it can be assigned to the job so the same job (process) can run for different environment (UAT/Pre-PROD/PROD) by pointing the right environment.
Job with environment variable look like below figure 3:
At this setup, you can choose from different environments before running the job. And each environment can hold different source and target database as well as source and destination file location. Now let's see how to create the environment variable.
Step 1: Create environment variable
Under SSISDB, you will find Environment folder as like below picture, right mouse click and follow the step.
Step 2: Property set up for environment variable
At this step, you will define connection strings, source/destination file path etc.
Step 3: Connect environment variable from project, Under SSISDB catalog choose the project and then right mouse click to find 'Configure'.
And then add reference to the project, References->Add and then environment can be chosen.
Step 4: From step 1 to 3, we worked with SSISDB, Now let's look at the job, SQL Server Agent->Jobs, where will have option to choose from environment
And from the properties, click on Steps and then you will find variable to choose from.
In this example, only UAT environment have been created but as per your need Pre-PROD, PROD can be created and source and target database can be assigned accordingly. And those environment can be assigned to job.
Let's start with a few steps before going into environment variable. In general, after building SSIS solution, you get .ispac file which you take from DEV to UAT/TEST/PREPROD/PROD environment. And then by following the wizard you deploy the .ispac file under SSIS catalogue. To know step by step process to deploy SSIS package to catalogue please follow : Deploying SSIS package to Catalogue
After deployment , you will find the packages under SSIS catalogue, as like below figure 01:
Fig 01: SSIS Catalogue |
At next step you will create job/Jobs to run the packages. Now say, if you don't create any environment variable then the job will like below:
Fig 2: Job Properties without Environment variable |
Will this job execute without environment variable? Surely, it does. But the connection string still retain development server information. However, the solution is already moved to UAT or Test Environment, so you need to change the connection string manually. If Environment variable have been defined then it can be assigned to the job so the same job (process) can run for different environment (UAT/Pre-PROD/PROD) by pointing the right environment.
Job with environment variable look like below figure 3:
Fig 3: Job with Environment variable |
At this setup, you can choose from different environments before running the job. And each environment can hold different source and target database as well as source and destination file location. Now let's see how to create the environment variable.
Step 1: Create environment variable
Under SSISDB, you will find Environment folder as like below picture, right mouse click and follow the step.
Fig 4: Create environment variable |
Step 2: Property set up for environment variable
At this step, you will define connection strings, source/destination file path etc.
Fig 5: setup properties for environment |
Step 3: Connect environment variable from project, Under SSISDB catalog choose the project and then right mouse click to find 'Configure'.
Fig 6: Configure environment variable |
And then add reference to the project, References->Add and then environment can be chosen.
Fig 7: adding environment variable reference to the project |
Step 4: From step 1 to 3, we worked with SSISDB, Now let's look at the job, SQL Server Agent->Jobs, where will have option to choose from environment
Fig 8: Job Properties |
In this example, only UAT environment have been created but as per your need Pre-PROD, PROD can be created and source and target database can be assigned accordingly. And those environment can be assigned to job.