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 Friday, July 20, 2012 7:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:05 AM
Points: 247, Visits: 696
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...
Post #1332939
Posted Friday, July 20, 2012 7:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 13,741, Visits: 10,716
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1332942
Posted Friday, July 20, 2012 7:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:05 AM
Points: 247, Visits: 696
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?
Post #1332958
Posted Sunday, July 22, 2012 2:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 13,741, Visits: 10,716
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.




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 #1333535
Posted Monday, July 23, 2012 2:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:05 AM
Points: 247, Visits: 696
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?
Post #1333631
Posted Monday, July 23, 2012 2:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 13,741, Visits: 10,716
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1333632
Posted Monday, July 23, 2012 2:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:05 AM
Points: 247, Visits: 696
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.
Post #1333643
Posted Monday, July 23, 2012 3:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 13,741, Visits: 10,716
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1333652
Posted Monday, July 23, 2012 5:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:05 AM
Points: 247, Visits: 696
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!
Post #1333690
Posted Monday, September 23, 2013 3:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 27, 2013 4:07 AM
Points: 1, 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
Post #1497304
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse