Skip navigation

Category Archives: SSIS

I love to learn new things! As Albert Einstein once said,

“Learn from yesterday, live for today, hope for tomorrow. The important thing is not to stop questioning.”

Not to stop questioning… Hmmm, I think I can live with that.

Speaking on questioning, my learning experience today was actually a question a colleague of mine asked, but I was not sure of the answer. The question was, “If you have an SSIS package with configurations enabled, and you are using an XML configuration file, do you have to specify that config file in the Agent job?” I don’t like saying, “I don’t know”, so my answer was, “Give me a few minutes.”

I created a test case, because I had several questions that I wanted to answer. First, I created a quick database with one table that had an integer identity column (force of habit to create a key!), a column that would accept a string of different values, depending on where that value came from, and a column for an import date, again, out of force of habit.

Next, I created a simple SSIS package. One object, an Execute SQL Task, with an insert routine (INSERT INTO Config_Test VALUES (?, GetDate()), using a package variable as the only parameter.

The next thing was to create an Agent job to execute the package. I have not defined any configurations yet, so that I can get results that I am familiar with and expect before I try to retrieve results that I am not as familiar with. As with the SSIS package, the Agent job is a simple, one-step job that will simply execute the SSIS package, nothing more.

When I execute the job, I get the results I expected. The Package Variable’s value is inserted into the test table, and all is well with the world.

So, now that I have the return I expected, let’s see what happens when I enable configurations on the package and not the Agent job…

I enabled configurations (In Control Flow context, SSIS -> Package Configurations…) and then I designated an XML file for my configuration. I only exported the Package Variable’s value to the config file. I saved the SSIS package, and now I want to edit that XML file so that the variable’s value will reflect where the value is coming from.

I do not have to make any changes to the Agent job. When I execute the job again, will the package use the config file, even though it is not designated in the job?

Yes! Even if I do not specify that my SSIS package uses a config file, it will use the configuration file that I enabled within the package. So what is the configuration tab in the Agent job for? I made another configuration file, based on the first, and updated the value accordingly.

This time, I updated the Agent job to use the new, updated XML configuration file, while leaving the configuration file enabled in the package.

In essence, I have two different configuration files designated with this package. The first in the package, and the second, in the Agent job. Now this is a scenario I am curious about. Which configuration will the SSIS package use?

That settles it! If configurations are enabled in an SSIS package, they are not required in the Agent job that runs the package. Furthermore, if a configuration file is specified in the Agent job, that configuration is used in preference to the configuration that is enabled in the SSIS package itself. This could be useful to test for certain different configurations in an Agent job without changing the current configuration designated in the package. It can even be a totally different file.

I love to learn new things!