SSIS Scheduled Job Parameters Ignored 2

[Skittle]

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'.

 

[gk53]

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

 

[fredericofonseca]

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.aspx

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Skittle]

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'.