July 19, 2012 at 1:36 pm
I've written and tested an SSIS package which uses an xml configuration file. When I deployed this to the Production server and added it's execution as a scheduled SQL Agent job, I changed the configuration file to a different xml file. For some reason, it's ignoring the new xml configuration file and running the settings from the package's xml configuration file. At a loss why it's doing this and how to fix. Can someone offer me any advice please?
July 20, 2012 at 12:50 am
Did you turn on logging and see if there are configuration warnings?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 20, 2012 at 3:43 am
It's populating the sysssislog table and stating it's running the original config file not my new one so no clue there unfortunately. The package was set to write to this system table in the relevant database. I don't have anything however in the Logging tab of the SQL Agent job. Would adding another log provider in there provide any clues?
July 20, 2012 at 3:45 am
Do you have a package configuration inside the package that tells the package where to load the XML config file?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 20, 2012 at 3:55 am
When I created the package I selected Package Configurations from the SSIS drop-down at the top, ticked enable package configurations, added an xml configuration file with the configuration string set to the new file on a file server. The config file on the file server is not the one mentioned in sysssislog. That is the one deployed as part of running the deployment manifest program on the Production Server.
July 20, 2012 at 4:49 am
Just to understand the complete picture, you have 2 configuration files?
* one specified in the package (with a direct hardcoded package configuration)
* one specified in the deployment manifest that is located on a file server
How did you change the configuration file in the SQL Agent job?
Which one exactly is mentioned in the logging?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 20, 2012 at 5:42 am
Sorry, probably didn't explain myself too well there. I created a configuration file (conf 1) when I built the original package. When you set CreateDeploymentUtility to True it adds that configuration file to the deployment manifest. On the production server then, when I run the manifest it displays the configuration file I added to the package (conf 1) and allows me to change some of the variables in there before deploying to the prod server location. I didn't change any values there, and so it deploys this configuration file (conf 2). When I then create the sql agent job with a step to run the ssis package (file store), I must have added in conf 2. I let that run for a few weeks until I was happy it was stable. I now want to swap it to another configuration file. I copied conf 2 to another file, conf 3, then edited conf 3 making the necessary changes. Now we're going live I thought it would be just a job of removing conf 2 from the sql agent job and adding in conf 3.
I've eliminated the possibility of it being a read access issue. The agent job was running as my sql server agent account. I added in full file and folder permissions to the config files and folders. Re-ran it and still the same issue.
Stuck again! Could it be a caching issue with the config file? Do I need to restart the sql agent service may be?
July 20, 2012 at 5:58 am
My guess is that the package still references config2.
This is the order in which configs are applied:
1. Apply package configurations. (--> conf2)
2. Apply configurations specified in jobstep (--> conf3)
3. Re-apply package configurations (--> conf2)
So basically conf2 is overwriting conf3.
I would edit the package in your development machine, remove the package configurations, redeploy the package and specify the config file in your jobstep.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 20, 2012 at 6:52 am
So back in to BIDS, select SSIS > Package Configurations. Leave Enable Package configurations ticked. Then select the configuration file in the list of configurations and press Remove, close it. Rebuild and deploy to the production server. Then re-run the sql agent job (which has conf 3 in the configurations pane)?
July 20, 2012 at 6:58 am
Jup, that should be about it.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 20, 2012 at 7:06 am
OMG! That's worked! Is it best practice then to remove the configuration file when you're happy with the package? Really strange that. We've recently upgraded to SQL 2008 from 2005 and I never remember having this issue in SQL 2005 SSIS/SQL Agent...
July 20, 2012 at 7:10 am
Griffster (7/20/2012)
OMG! That's worked! Is it best practice then to remove the configuration file when you're happy with the package? Really strange that. We've recently upgraded to SQL 2008 from 2005 and I never remember having this issue in SQL 2005 SSIS/SQL Agent...
The behaviour of SSIS package configurations has changed between 2005 and 2008.
Behavior Changes to Integration Services Features in SQL Server 2008
It's not really a best practice to remove your package configurations. What usually is done is that you have another package configuration that points to the location of the XML config file (usually an environment variable). This is called an indirect package configuration. If the package changes environment, it will pick up the new location of the config file.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 20, 2012 at 7:34 am
Oh lordee! That's a nightmare! Why would they do that?!
So, the best thing to do is, in BIDS, create an environment variable package configuration to point to an xml configuration file outside of the package. Is that right? I've never used an environment variable configuration before really. Once you've done that, how do you set up your SQL Agent job to look at different xml configuration files?
July 22, 2012 at 2:15 pm
Griffster (7/20/2012)
Oh lordee! That's a nightmare! Why would they do that?!So, the best thing to do is, in BIDS, create an environment variable package configuration to point to an xml configuration file outside of the package. Is that right? I've never used an environment variable configuration before really. Once you've done that, how do you set up your SQL Agent job to look at different xml configuration files?
The reason they changed that is so that you can change the connectionstring to the config table/file and that new package configurations are applied. It allows you to change configurations at runtime. But it is pretty confusing 🙂
If you use an environment variable, you don't do anything in SQL Server Agent. Everytime the package changes from environment, it will read the new value from the environment variable and change configurations accordingly.
If you want to change configurations ad hoc in SQL Agent, I wouldn't use an environment variable or package configurations at all. I would simply rely on the configuration file you specify in the job.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 23, 2012 at 2:06 am
Mmm. Well the purpose of my configuration file is to import certain types of information throughout the day. In my SSIS package I have many sequence containers with dummy scripts in them leading to a data flow task to import data from a text file to a SQL table. The link from the script to the data flow is conditional though dependent on the value of a boolean variable. As some tables take a long time to import, I only do them at certain times of the day. So let's say I have ten tables (table1, table2, ..., table10) and I have a variable for each (var1, var2, ..., var10) to state whether I want to import that table in this execution, my SQL2005 SSIS version had one SQL Agent job scheduled to run at midday that ran a configuration file with var1, var2, var5, and var10 set to true (i.e. import the table), and then another SQL Agent job running at midnight using the same SSIS package with a different configuration file that contained var1-var10 set to True. This worked fine before. With this change to SSIS on SQL 2008 I don't think I can take this approach any more reading what you're saying here. Have I read your comment right there please?
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply