SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS 2008 package not running the right configuration file in the SQL Agent Job


SSIS 2008 package not running the right configuration file in the SQL Agent Job

Author
Message
Griffster
Griffster
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 Visits: 713
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...
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27277 Visits: 13268
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.


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

MCSE Business Intelligence - Microsoft Data Platform MVP
Griffster
Griffster
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 Visits: 713
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?
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27277 Visits: 13268
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 Smile

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.


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

MCSE Business Intelligence - Microsoft Data Platform MVP
Griffster
Griffster
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 Visits: 713
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?
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27277 Visits: 13268
You still can use the same method, but you need to disable package configurations and rely solely on the configurations specified in the SQL Agent jobs.


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

MCSE Business Intelligence - Microsoft Data Platform MVP
Griffster
Griffster
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 Visits: 713
Sorry, disable package configurations? If I did this the package won't pick up any configurations I put in the configurations tab of the SQL Agent Job will it? Or do you mean, do as I did earlier in this post and leave "Enable package configurations" ticked in BIDS for the package but remove any configuration files in the package configuration window, putting whichever configuration file I want in to relevant SQL Agent Job's configuration window? Or are you referring to the Set Values tab in the SQL Agent Job? If the last one, I have around 80 of these variables so will be difficult to maintain.

Is there an alternative to what I've done here then out of interest? If so, I'll try and apply that with my next SSIS package. Probably gone too far with these for now if removing the package's attached configuration file will work.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27277 Visits: 13268
I'm not 100% sure, but I don't think the checkbox for Enable Package Configurations needs to ticked in order to specify a config file in a job. I'll have to test it out sometime. If it is required, just select it but don't configure a configuration.


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

MCSE Business Intelligence - Microsoft Data Platform MVP
Griffster
Griffster
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 Visits: 713
That's great. THanks for your help here. Think I'll leave it in there to create the initial configuration file so it creates one that I can copy and amend to variations of the config, then re-deploy the package with that file removed.

I must admit, it does seem a crazy change by Microsoft this as it's designed to assist with dev v production environments ok, but has a horrible detrimental affect to packages that need to be run differently in the same environment, like in my instance. Yuk!
param_urpal
param_urpal
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 23
I have a XML config file just with one variable,
<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="WARBABANK\parmeshwari.kumar" GeneratedFromPackageName="TestConfig" GeneratedFromPackageID="{28DFF61A-A509-4404-956D-9B2704C55848}" GeneratedDate="9/23/2013 11:02:21 AM"/>
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package.Variables[User::remotePath].Properties[Value]" ValueType="String">
<ConfiguredValue>HI Who are you?</ConfiguredValue>
</Configuration>
</DTSConfiguration>

I just added a SSIS with script task that will display messagebox with this value.
This runs in SSIS as package , but when I run this as job, this gives me "Exception has been thrown by the target of an invocation."

I had added this file under Configuration tab and executed. But this always gives me this error

Can you please help me to find this solution asap?

What package level protection we need to add?
Do we have to import package or we can use it from FileSystem ? (both way I tried)

expecting help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search