How to upload Parent child SSIS package to server

Question

Hi all I am very new to SSIS. I have got SSIS package developed by some other guy this package reads data from flat files and stores to database after mapping.

Flow: 1) First package extract records from flat file and stores in table. 2) Then it calls child package using Execute package tasks. 3) Then child package do some calculations and update the database table.

SSIS is using Environment variable to get database information.

Every thing is working fine but now I want to deploy this package to my client's server.

Ques: Do I need to copy and paste files from bin folder and paste on clients machine? What I Tried: I copy files from bin folder and placed on my local computer. Then I create a job in MSSQL and run the job. Package runs perfectly. But Later I changed location of my project and problem starts job stops working. Issue: Error says location of child package is not available(As I changed position of my project files)

Kindly suggest what to do.

Answer 1

I am going to make several assumtions here so please correct me if I get any wrong.

The problem I am guessing is that on your Package.dtsx within the connection manager this is currently linked to the package location within the project folder. In this case you are wanting to change it to another location, however the package in the connection manager is still pointing to the project location.

If I were you I would do the following:

Create a string variable

• PackageFolderPath - C:\CurrentPackagePath\DBPackage.dtsx

Now what you want to do is go to the package within the connection manager and under the properties add an expression for ConnectionString with the following: “@[User::PackageFolderPath] If you evaluate the expression it should give you the location you setup in your variables.

Please note however that if you want this to work on the development system then setup the package to the project location.

Now once you have those setup, copy the files across the new server and under the SQL agent job to go the Set Values tab and within here you want to add the following:

\Package.Variables[User::PackageFolderPath].Properties[Value]

Under the value you want to put wherever the package is now located

This now should pickup the new location of the package when it is run.

A better way to do this would be to make use of the deployment utility and using an XML configuration variable on the package. However this way should work.