CODEWITHSUNDEEP
ssis
Jesse Petronio
Jesse Petronio

Reputation: 723

SSIS Deployment Woes

I'm quite confused as to how to create a deployment in SSIS 2008 that I can use throughout the various sites we are going to deploy to. I'm using the deployment utility to deploy my ETL packages which are file based and executed using a SQL job.

When I rebuild my solution, the deployment files are created along with their configuration files which I bind my connection strings to. I've discovered that each of the packages are still referencing the configuration files in my project folder, rather than the configuration files in the deployment folder. I thought that when I created a deployment, the paths referencing the configuration files would be relative paths.

Ideally, what I would have liked to have been able to do would be to copy the contents of the deployment folder to a flash drive, plug it in at the site I'm deploying to and edit the configuration file per the customer site, execute the deployment manifest file in the folder and expect everything to work. But this doesn't seem to be the case.

I also notice that the SQL job has an option to specify the configuration files for the packages, but this doesn't seem to have an effect either. I must clearly be doing something wrong here, please could someone assist.

Upvotes: 1

Views: 79

Answers (1)

RBL
RBL

Reputation: 36

Seems like you are encountering these two issues with SSIS deployment and execution:

  1. Configuration file references are stored with absolute paths (meaning the concrete path used in the development environment when the configuration file reference was created, and in production this is the same path that will be used).
  2. Specifying a different configuration file at runtime in SSIS 2008 cannot override values specified at design time (see Understanding How SSIS Package Configurations Are Applied at Run Time).

To deploy your packages with a simple file copy the way you describe, you must change your packages to use a relative reference to your configuration files:

Right click the package file and select View Source to open the XML view of the package source. Search for your configuration file, which will include the path, and remove the path; keeping only the filename portion. Alternatively, change the absolute path to a relative path to the configuration file. Save and close the XML view of the package.

Now when you deploy the package and the configuration file together, ensuring they have the same relative location to each other, the package will find the config file by the relative path, and work the way you expect.

Note: from this point forward you will need to open the BIDS IDE by double-clicking on the project or solution file. If you launch Visual Studio, and then open the project or solution from within the IDE, the IDE will not be able to find the configuration file when you execute the package (the current directory will be Windows\System32, not your package folder).

Upvotes: 1

Related Questions