SSIS package ignores project configuration settings and follows SQL Job Agent

2
votes

I have a 2012 SSIS package that is hooked into 'project configuration parameters'. There are two date parameters that I set by going into the Integration Services Catalog. When I execute the package from the Job Agent, the values of the two date parameters from the project configuration are ignored and the dates are picked up from the job agent.

When I go to

SQL Job Agent

then to the step for the SSIS package

then to the configuration tab.

I see that those two dates values are displayed with a bold font. Sometime after the initial setting up of the job I changed those. Any parameters in the configuration, once edited, stays bold. I am guessing that the bold indicates that the Job Agents setting will override other settings. So, my specific question is how can you un-bold and configuration setting in an SSIS tab in the SQL job agent-or more generally how do I make the package execute with the Project Settings.

3
ssissql-server-2012

3 Answers

5
votes

Your key for decoding the attributes are

  • bold = explicit override (locally defined)
  • underscored = set from SSIS Environment configuration
  • asterisked = sensitive value therefore click and find out
  • regular = stored package value

Of course, I can use /SET option to further muddy the waters but I haven't tested to see what the order of operations is for application of values.

Explicit override

Sample of an explicit override

enter image description here

Environment configuration

This demonstrates the value being set from configuration. The underscore shows that configuration is applied, the checkbox indicates from where the value comes. enter image description here

Default

Unmodified value from the SSIS package enter image description here

How do I change these?

Click the ellipses (...) on your parameter and you're presented with this dialog. If have not applied a Configuration, you won't have the ability to set Use Environment Variable. Also, if the parameter has been marked as Required, you won't be able to use the default value.

enter image description here

0
votes

The answer above is very complete, but I have learned what makes a parameter become bold in the SQL Job Agent UI. Let's say for example I go to a job in SSMS and select created script. The @command looks like this

@command=N'/ISSERVER "\"\SSISDB\MDS\MDS_Test\MDS_Test_Load.dtsx\"" 
/SERVER "\"SQL_SRV-SS01\"" /ENVREFERENCE 10034 
/Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 
/Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True 
/CALLERINFO SQLAGENT /REPORTING E',

Now, I go to configure an SSIS package and enter in specific dates for two parameters-- StartDate and EndDate. Click OK twice and then create the script for the job again.

@command=N'/ISSERVER "\"\SSISDB\MDS\MDS_Test\MDS_Test_Load.dtsx\"" 
/SERVER "\"SQL_SRV-ONE\"" /ENVREFERENCE 10034 
/Par "\"$Project::EndDate(DateTime)\"";"\"11/9/2014 12:00:00 AM\"" 
/Par "\"$Project::StartDate(DateTime)\"";"\"12/17/2014 12:00:00 AM\"" 
/Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 
/Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True 
/CALLERINFO SQLAGENT /REPORTING E',

There are our two specified parameters! Embedded in @command. So, how do you 'unspecify' parameters? I think you need to script out the job, delete it, change the @command -cutting out the /PAR entries and recreate it.

0
votes

I too encountered this pain - overriding a parameter value in a SSIS job step could not be reverted to using the mapped Environment variable. But I finally managed to find a workaround and you were very close Marty. Don't bother scripting the whole job though. Instead:

  1. Make one more manual change to the parameter value in question within the package configuration in the job step. Click Ok to go back to the Job Step List screen.
  2. Select Script Action to new query window and you should see something like this:

EXEC msdb.dbo.sp_update_jobstep @job_id=N'6a4b399e-f1ad-4753-96d9-b9ea75fb7bf9', @step_id=1 , @command=N'/ISSERVER "\"\SSISDB\EnvironmentName\ProjectName\Package.dtsx\"" /SERVER "\"InstanceName\"" /ENVREFERENCE n /Par "\"$Project::ParameterName\"";"\"YourValue"" /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E'

  1. Locate the variable in question and delete it (see bold section above as an example). Make sure you delete the /Par before your variable.
  2. Run the script
  3. Re-check the job to confirm the change.

Hope that helps!