December 7, 2011 at 6:06 pm
I developed parent and child packages. The parent package contains an Execute Package Task. The Execute Package Task requires a connection. My connection calls the child package based on the file system (drive "P:" because I used a mapped drive instead of UNC). I deployed the parent package to the server, then I deployed the child package to the server. However, the connection that the parent's Execute Package Task uses still references the child package on the P: drive. I guess I could deploy the child package to the server first, then modify the connection that the parent's Execute Package Task uses. However, is there a way to automate this? Otherwise, I might have to manually go through this when I go from development, to testing, then to production. Could I use, for example, a dtsconfig for the parent package that contains information for the connection (a connection string, basically)? What is the best, most maintainable way to do this?
December 7, 2011 at 11:53 pm
The best, most maintainable way is to store your packages in the MSDB database, and not on the filesystem.
If that is not an option, go with package configurations.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 8, 2011 at 9:56 am
Okay, so should I deploy the child package first? How do I connect the parent to the child package? Will I have to manually modify that connection as we go from devopment to test to production?
December 8, 2011 at 10:23 am
Package configurations are what you need. They allow you to manage package properties externally.
http://msdn.microsoft.com/en-us/library/ms141682.aspx
The way I’ve used configurations to deploy from dev/uat/prod are:
Create an environmental variable on each server that you will run packages on. This variable will have the same name on every server but the value will be specific to each environment. When a package runs this first thing it does is reaches out to this variable to find where it should look for the rest of the configuration information for the server it is running on.
Personally I use a SQL Server table to store the rest of my config info, but you can use any of the other config types. When you deploy packages to different environments you will also need to deploy an environment specific version of the config info, but you will not have to modify the packages.
December 8, 2011 at 10:38 am
Ah, I think I get it! I was making things too complicated, I guess. Let me see if I understand at the high level.
1. I use a dtsconfig (or other package configuration method) for the parent in order to set the parent packages connection to the children, BUT
2. I need to deploy the child packages to the server BEFORE I deploy the parent package if I want to be able to set the connections properly.
Is that correct?
December 8, 2011 at 11:09 am
The deployment order won't matter. As long as both packages are on the server and the config that you are using has the correct information for its environment you will be fine.
At run time the parent will look for the "bootstrap" config which will give it the child package location. The parent will use the config's value to reset its connection to the child and then it will execute the child package.
December 8, 2011 at 11:34 pm
imani_technology (12/8/2011)
Ah, I think I get it! I was making things too complicated, I guess. Let me see if I understand at the high level.1. I use a dtsconfig (or other package configuration method) for the parent in order to set the parent packages connection to the children, BUT
2. I need to deploy the child packages to the server BEFORE I deploy the parent package if I want to be able to set the connections properly.
Is that correct?
As Eric said, order of deployment doesn't matter, if you use package configurations properly.
If you are storing the packages in the MSDB database, then you need to deploy the child package first during development, so that you can create the references in the parent package.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply