brad.mccollum (5/21/2014)
I'm about 2 weeks away from deploying several SSIS packages that I've been developing for a client. This is my first time to work with SSIS, so I'm still learning as I'm going in many cases.I'm going to create an XML Config File to allow for dynamic/different values to be set for several variables in order for the packages to operate using the desired variable values on my development machine as well as operate successfully in the production environment.
I understand how to indicate that I'm using an XML Config file and I'm likely going to use an environmental variable to allow for the DTSCONFIG file to be found in its location in the development environment as well as the production environment.
A few questions:
(1) When I do my package and deployment, is there going to be a place that asks me if the XML (DTSCONFIG) file needs to be included in the deployment of files or is this DTSCONFIG file going to automatically be incorporated / included in the deployment process without me having to indicate it needs to be included with the package files, etc?
(2) However the DTSCONFIG file is included in the deployment process, how do I indicate specifically where I'd like the DTSCONFIG file to reside on the production environment where the packages and associated files will be installed?
(3) The package will be called in the production environment using a SQL Agent Job. It seems like there's an option within the SQL Agent Job setup to where you can indicate that the job needs to be called using a particular DTSCONFIG file. With the face in mind that the path to the XML Config File will be referenced in the Environment Variable that I'll create on the production machine, will I "point" to the Environment Variable or the actual path of the DTSCONFIG file within the SQL Agent Job setup area? Seems like I should point to the Environment Variable since this is the sole reason I'm setting it up to where this variable knows/maps to whatever path for the DTSCONFIG file that I've indicated within the Environment Variable.
(4) My main SSIS package can and will call out to numerous "child" SSIS packages. Those child packages have several variables that they use just like the main/parent package does, but the child packages get the values for the several variables passed directly in from the main/parent package. I'm assuming there's nothing special I need to do here to address that as far as the package deployment setup is concerned?
Many thanks in advance for any answers you may be able to provide.
When you SSIS packages from a project in package deployment mode, the steps that you perform are:
- in the project properties within Visual Studio, go to "Deployment" and enable AllowConfigurationChanges and CreateDeploymentUtility
- next, right click on the project name and select "Build" - this will put all packages AND files they require in the the bin\deployment folder for the project.
- next, using Windows Explorer navigate to the location of the project, and then to the "bin\deployment" folder
- next, subject to any release processes you have, copy all files from that location to your release folder
- double click on the "SSISDeploymentManifest" file (it may be easier to copy the files to the target server first e.g. to c:\temp)
- this should launch the deployment wizard
- follow the prompts for the destination for the packages
- you will be asked for the location of the XML Config file. By default this will be somewhere in C:\Program Files\ but you can choose any folder you want (but put it on the server, not a network share)
- near the end of the installation you will be asked whether you want to update the context of the XML config file. If needed, do them at that point.
I don't use Windows Environment Variables all that often. Depending on where the variable is to be (a user or a system variable) the process is a little different and probably will require you to, at least, restart SSIS so that it gets a new copy of the variables.
As for specifying the name of the XML config file from within SQL Agent - unless this server actually needs multiple files, I would not bother. If not done properly, you may end up with problems that can be difficult to diagnose.
The child packages should be using information that the parent is supplying so there should be not need to worry about them because the parent package will send the values.