INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SSIS Scheduled Job Parameters Ignored

SSIS Scheduled Job Parameters Ignored

(OP)
I have a SSIS package on SQL Server 2008 that accepts three parameters.
The SSIS package has the parameters defined in an XML configuration file.

In the scheduled job step that points to the dtsx SSIS package, I have specified the variables
on the 'Set Values' list ( eg. \Package.Variables[User::varMyvariable] with a value ) but
the step always ignores my parameters and uses the ones hard coded in the XML file.
I can even see on the 'Command Line' tab for the Step that my step parameters have been
accepted and are part of the command line BUT it still uses the variables from the XML.
The XML does not get overridden.

Is there a setting missing somewhere I need to set to get the packages to use external set parameters?
Help! Stuck on this for ages.





Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

RE: SSIS Scheduled Job Parameters Ignored

It looks like your sql server does not have access to folder with xml file. Or you did not set your variables in 'Set Values' step as read/Write. instead of useing hard codded values try use values from xml file in debug mode and make sure it is picking up right values. Another suggestion is enable logging on ssis and check all error messages

RE: SSIS Scheduled Job Parameters Ignored

looks like the OP is stating that the configuration file is being used even if he uses a /Set to set config options.

this is expected behaviour and that bitten many people.
Refer to https://msdn.microsoft.com/en-us/library/ms141682....

the most important thing to take in consideration is that the config file used at design time should NEVER EVER be available at execution time as it will be used if so regardless of any parameter supplied to the dtexec utility.
This for SQL Server 2008
For 2012 and 2014 there are further options and behavior may be slightly different

Execution of package should then set any "/set " required and a "conf config_file" to use a config file. Both can be used in this case and I may be wrong but if both are specified the order they are specified will be the one they are applied.
e.g. dtexec package.dtsx /conf config /set variableA=yyy
where config contains a set of variableA = bbbb
will, I think, apply the value of the config file and then the value on /set resulting on a final value of yyy

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: SSIS Scheduled Job Parameters Ignored

(OP)
The behaviour between 2005 and 2008 has changed.
I didn't mention the job was transferred from 2005 on my first posting.
Basically the parameters were being ignored on the scheduled job because there
was a reference to the configuration file. In Visual Studio when you right click on the
Control Flow and select 'Package Configurations' I still had the tick selected against 'Enable Package Configurations'.
In SQL Server 2005 even if this is ticked, the parameters get overridden by the scheduled job. In 2008 they do not!
So I removed the tick and blasted thing now works.



Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close