I’ve written one post about SSIS – Enable Package Configuration for Deployment, and another one about SSIS – Add Configuration Files for Deployment.
I am afraid that after reading these two posts you are quite confused. You are probably screaming silently. Why is she talking about environment variables, together with configuration files? Do we need both, or just one of them? Why did my job still fail after I changed my password to DB2? Questions, one after another…
So here I have more writing to do to clarify the confusion.
First of all, after reading those two posts, you already know:
- how to create the environment variables,
- how to create the configuration files in XML, and
- how to enable package configurations in BIDS.
- You also know how to point to a configuration file for your SQL job step when executing a SSIS package.
The first 3 “hows” are important as a SSIS developer. There are a few more”secretes” regarding package configuration you will also need to know as an experienced SSIS developer.
Here are those “secretes” (assuming that you have done the first 3 hows in BIDS).
1)There are a couple of ways to test your package in BIDS. Here is one, by right-clicking on the package name in the Solution Explorer.
Have you ever thought about how you are connecting to your sources when you test you package in this manner?
Is the connection done through one of the configuration files?
Or by the login credential you entered in the Connection Manager?
The answer is by the package configuration files you’ve specified in your environment variables.
If this is your habit to test your package locally, then you will need to maintain your own package configuration files with the correct user id and password (or through Integrated Security mode).
2) Well, the above is not my favorite way for testing locally. I very often test just parts of my package at a time by right-clicking on a sequence container. When I test in this manner, how is my package connecting to the sources?
The correct answer this time is through the connection manager. So you better make sure that you have the correct credentials for each of your connection manager.
3) so what’s the third secrete? The third one has to do with the SQL Agent job you’ve created to execute your package.
Again, I assume that you have done all the right things in your BIDS locally, and have successfully uploaded your package to the Integration Services, and have created a job step to execute your package.
I will also assume that your server administrator has also done on the server exactly the same things you have done locally.
The secrete is that you do nothing in the job step. After all the hard work you have put in, you deserve a break.
By nothing, I mean putting nothing on the Configurations tab.
I also mean doing nothing on the Data sources tab. Do not check any of the check boxes for the connection managers.
By doing nothing, your job step will use the environment variables on the server to point to the correct package configuration files (DTSConfig files we’ve created), which have the correct credentials to connect to the data sources.
4) Last secrete. This is a secrete I’d rather not know. Adding package configuration files for your job step, is only necessary if you want to overwrite the package configuration files defined by your environment variables. There are case where you want to do this, but I am hoping that you will only need to do this for very few of the packages you’ve designed.
I am hoping that this post will leave you feel better about package configuration files and package deployment.