Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SSIS 2008 package not running the right configuration file in the SQL Agent Job Expand / Collapse
Author
Message
Posted Thursday, July 19, 2012 1:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 8:19 AM
Points: 247, Visits: 689
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?
Post #1332522
Posted Friday, July 20, 2012 12:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:23 AM
Points: 12,228, Visits: 9,202
Did you turn on logging and see if there are configuration warnings?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1332726
Posted Friday, July 20, 2012 3:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 8:19 AM
Points: 247, Visits: 689
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?
Post #1332806
Posted Friday, July 20, 2012 3:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:23 AM
Points: 12,228, Visits: 9,202
Do you have a package configuration inside the package that tells the package where to load the XML config file?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1332808
Posted Friday, July 20, 2012 3:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 8:19 AM
Points: 247, Visits: 689
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.
Post #1332812
Posted Friday, July 20, 2012 4:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:23 AM
Points: 12,228, Visits: 9,202
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?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1332844
Posted Friday, July 20, 2012 5:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 8:19 AM
Points: 247, Visits: 689
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?
Post #1332890
Posted Friday, July 20, 2012 5:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:23 AM
Points: 12,228, Visits: 9,202
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1332898
Posted Friday, July 20, 2012 6:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 8:19 AM
Points: 247, Visits: 689
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)?
Post #1332924
Posted Friday, July 20, 2012 6:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:23 AM
Points: 12,228, Visits: 9,202
Jup, that should be about it.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1332931
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse