Setting up xml configuration in SSIS for multiple environments

Question

I have implemented a SSIS package and need to currently deploy it to QA. I have defined connection managers that are pointing to the DEV environment as seen in the screenshot below

I have created an xml configuration file that contains properties like connectionstring, servername etc. Please see below

I am new to SSIS and doing the deployment of a package for the first time. Do I need to create a connection manager for QA enviornment or how do I handle them in config file. How do I specify a config file for each environment.

Answer 1

There is something called Package configuration that you need to enable.

1.Right Click on you SSIS project. 2.In the properties window you can see something called Configurations~ under the MISC section.Click on that and you can see a window called 'PACKAGE CONFIGURATION ORGANIZER' 3.Enable Package Configuration 4.Click next and in Configuration TYPE SELECT XML Configuration File 5.Follow the STEPS that come through.

Answer 2

I have a separate Config File in each environment that has obviously environmental changes to configure like DB ConnString and filePath, etc.

When you deploy the package to the new environment, you also need to deploy a config file.

The configfile is specified in Microsoft agent inside Job Step.

You should probably TEST this in DEV first before you provide instructions to the Apps Team (if your environments are controled by different parties).

You mentioned new Connection Managers in QA...

I use a variable I call filePath to denote the directory of the file to be processed and set and Expression to the actual file (@filePath + [fileName])

That way you have the same package in every environment.

You just need a config file to alter environment changes.

Adding pic for showing how to add config file to job.

Adding a few more steps on how to use variables to configure file path:

Set up 3 variables:

1. fileName string - hard code the file name from the fileshare directory
2. filePath string - this is set from Config
3. filePathName string - make this an expression that concats the 2 + 1

Example:

Now that you have your variables set up, now you have to put an expression on connection manager to use filePathname as the connstring to file:

1. Click on the connection manager that this variable is trying to manage
2. Open the properties window if not already open (f4)
3. Go to the expressions property (make sure you are in the connection managers property.
4. click the ellipses to open property expressions editor
5. select the property (flat file is Connection String and Excel is ExcelFilepath, etc) and map it to filePathName.