SQLServerCentral » Data Warehousing » Integration Services » SSIS Agent Job Passing ParametersInstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralTue, 26 Sep 2017 16:02:50 GMT20SSIS Agent Job Passing Parametershttps://www.sqlservercentral.com/Forums/FindPost505921.aspxI'm trying to set up a SQL Agent job to run a SSIS package.
I want to pass a parameter to the package to tell it to either run the whole package, or just run the report at the end. The variable is just a Boolean that is set to true or false.
The package works fine in BIDS. I can set the variable and the execution works.
I have scoured the web and found several conflicting solutions.
I have tried every combination in the 'Set Values' tab, that I can think of (except the right one of course).
Property Path
Value
Package.Variables[User::Report_Only].Properties[Value] 'True'
Package.Variables[User::Report_Only].Properties[Value] True
Package.Variables[User::Report_Only].Value 'True'
Package.Variables[User::Report_Only].Value True
I always get a message of the type;
DTExec: Could not set
Package.Variables[User::Report_Only].Properties[Value] value to 'True'.Tue, 21 Feb 2017 07:44:46 GMTGlynne SmithRE: SSIS Agent Job Passing Parametershttps://www.sqlservercentral.com/Forums/FindPost1859915.aspxGaurav<br/><br/>Better to start a new topic for a new question. &nbsp;But I don't advise you to constantly change the job once it's set up. &nbsp;You should alter your package so that it picks up the value from a table. &nbsp;Your SQL code can then update the table, which is much easier (and requires lower permissions) than updating the job.<br/><br/>JohnTue, 21 Feb 2017 07:44:46 GMTJohn Mitchell-245523RE: SSIS Agent Job Passing Parametershttps://www.sqlservercentral.com/Forums/FindPost1859909.aspxHi All,<br/><br/>Is there any way so that I can change the set values in sql agent job through TSQL.<br/>Here is my case.<br/>I have a job which runs SSIS package, ssis variable value is passed through step set value field.<br/>I have to process data based on the rule written on sql and based on the rule I want to change the set value from the sql code.<br/><br/>Thanks in advance.<br/><br/>GauravTue, 21 Feb 2017 07:34:21 GMTgauravjoshi.kecRE: SSIS Agent Job Passing Parametershttps://www.sqlservercentral.com/Forums/FindPost1855522.aspx<div id="if_insertedNode_1485981016162"><div data-id="505964" class="if-quote-wrapper" unselectable="on" data-guid="1485981016126"><a class="quote-para" unselectable="on" style="display: none;" href="#" data-id="505964" title="Move cursor below" contenteditable="false"><span unselectable="on">+</span></a><a class="quote-delete" unselectable="on" style="display: none;" href="#" data-id="505964" title="Delete quote" contenteditable="false"><span unselectable="on">x</span></a><span unselectable="on" class="quote-markup">[quote]</span><div unselectable="on" class="if-quote-header" contenteditable="false"><div unselectable="on" class="if-quote-toggle-wrapper"><a class="if-quote-toggle hide quote-link" href="#" data-id="505964" title=" "></a></div><span unselectable="on" class="quote-markup">[b]</span>stevefromOZ - Friday, May 23, 2008 9:29 AM<span unselectable="on" class="quote-markup">[/b]</span></div><div class="if-quote-message if-quote-message-505964"><div class="if-quote-message-margin">Using a package level parameter, this works in SQLAgent --&gt; \package.Variables[myVar].ValueHTH,Steve.<a class="if-quote-goto quote-link" href="#" data-id="505964"><span class="goto"></span></a></div></div><span class="quote-markup">[/quote]</span></div><br/>Thanks Steve, it helped me as well :)<br/><br/>Kind Rgds,<br/>Sanjay</div>Wed, 01 Feb 2017 13:33:12 GMT397229RE: SSIS Agent Job Passing Parametershttps://www.sqlservercentral.com/Forums/FindPost1404978.aspxTo Steve,
Thanks for your post, it's a very great help.
Save me a bunch of money !
Cheers
RaslerWed, 09 Jan 2013 13:28:13 GMTchjquestRE: SSIS Agent Job Passing Parametershttps://www.sqlservercentral.com/Forums/FindPost1141179.aspxI've never tried it. In the past I've used a variable that gets used in an expression in the connection in the package.
More recently I've switched to config files. The upside here is that you can move packages around servers (dev/qa/prod/etc) and the package will read the local config file to determine it's connections. That's a whole different story though. Worth a web search to make your life easier.Wed, 13 Jul 2011 11:02:41 GMTLerxtDBARE: SSIS Agent Job Passing Parametershttps://www.sqlservercentral.com/Forums/FindPost1141171.aspxThanks for the response. The image is missing but I got it.
Also Is it possible to modify connectionstring value under "Data Sources" tab ?Wed, 13 Jul 2011 10:54:05 GMTsaqib-431177RE: SSIS Agent Job Passing Parametershttps://www.sqlservercentral.com/Forums/FindPost1141155.aspx[quote][b]saqib-431177 (7/13/2011)[/b][hr][quote][b]stevefromOZ (5/23/2008)[/b][hr]Using a package level parameter, this works in SQLAgent --&gt; \package.Variables[myVar].Value
Steve.[/quote]
Can you please where in SqlAgent, I can pass the parameter?
I couldnt understand the above directions.[/quote]
Enter them on this tab of the Job Step Properties screen when you have selected an SSIS Job Step
[img]https://www.sqlservercentral.com/Forums/Uploads/image-unavailable.png[/img]
Edit: I had way too much info visible there at first. :Whistling: Had to edit screen shot.Wed, 13 Jul 2011 10:36:28 GMTLerxtDBARE: SSIS Agent Job Passing Parametershttps://www.sqlservercentral.com/Forums/FindPost1141136.aspx[quote][b]stevefromOZ (5/23/2008)[/b][hr]Using a package level parameter, this works in SQLAgent --&gt; \package.Variables[myVar].Value
Steve.[/quote]
Can you please where in SqlAgent, I can pass the parameter?
I couldnt understand the above directions.Wed, 13 Jul 2011 10:15:42 GMTsaqib-431177RE: SSIS Agent Job Passing Parametershttps://www.sqlservercentral.com/Forums/FindPost1103214.aspx@SSC Veteran: Thanks for the final clue to this mystery. I was actually putting in my top level package name and not the literal 'Package' Doh! Still, looks like I'm in good company on this one.
Wed, 04 May 2011 08:44:56 GMTDerek RobinsonRE: SSIS Agent Job Passing Parametershttps://www.sqlservercentral.com/Forums/FindPost877247.aspxI came across a handy way to make sure the syntax you use for this path is exactly right: open up package configurations and go through that wizard selecting the value you want the path for, on the "Completing the Wizard" summary screen you'll see it under "properties:". Also, there's more than one "correct" path. In my case I guessed the first and generated the second of these:
[code="plain"]\Package\Data Flow Task.Variables[QueryText].Value
\Package\Data Flow Task.Variables[User::QueryText].Properties[Value][/code]Thu, 04 Mar 2010 15:57:54 GMTohackRE: SSIS Agent Job Passing Parametershttps://www.sqlservercentral.com/Forums/FindPost874661.aspxI was running into a similar issue and really banging my head against the desk...literally.
I noticed at least one person with the same issue I had. Case sensitivity. \Package.Variables[variable_name].[b][size="4"]V[/size][/b]alue
I feel really dumb for wasting half an hour figuring this one out. I also feel better, however, knowing I'm not the only one. :-P
I really do wish there was some sort of consistency in the MS world regarding case-sensitivity.Mon, 01 Mar 2010 13:08:45 GMTLerxtDBARE: SSIS Agent Job Passing Parametershttps://www.sqlservercentral.com/Forums/FindPost810068.aspxHi,
I'm as well getting an error some-thing like -
Option " /SET" is not valid. The command line parameters are invalid. The step failed.
My cmd line says:
/DTS "\MSDB\&lt;dtsx package name&gt;" /SERVER "&lt;server name&gt;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "\Package.Variables[Location].Value";"T:\" /REPORTING E
Could someone suggest a way out?
Thanks!Wed, 28 Oct 2009 09:39:26 GMTAmbuj MathurRE: SSIS Agent Job Passing Parametershttps://www.sqlservercentral.com/Forums/FindPost758625.aspxI see this is an old thread, but I am encountering the same problem as originally posted. I have configured the sql agent job step properties page but I continue to receive the "could not set..." error.
/FILE "S:\ETL\PROJECTS\Hermes Profile Load\Hermes_Profile_Load\Hermes_Profile_Load\Hermes_Profile_Load.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "\Package.Variables[User::MaxBatch].Properties[value]";500 /REPORTING E
Are there other dependencies on this working? Protection level settings? Package Configurations mappings? I've tried varieties of everything I can think of.
Thanks,Thu, 23 Jul 2009 15:13:46 GMTstew_bRE: SSIS Agent Job Passing Parametershttps://www.sqlservercentral.com/Forums/FindPost542386.aspxHi Steve,
I'm pretty new to the SSIS Job Scheduling side of things and therefore I also need some help regarding passing parameters to an SSIS Package stored in a SQL Server Agent job. I need to pass values to 3 variables that control what data is loaded and also which portion of the SSIS is executed.
The 3 Variables are:
* Var_Fin_YM
* Var_Load_Recon
* Var_Load_Final
I've looked at quite a few websites for an answer but have not found anything of use.
Your help would be greatly appreciated.
Kind Regards,
Colin.Tue, 29 Jul 2008 01:09:27 GMTmackieRE: SSIS Agent Job Passing Parametershttps://www.sqlservercentral.com/Forums/FindPost505976.aspxGlad you got it to work!
look at it this way, it's two days that you didn't spend doing something else :P
Oh and the pointer i got was from [url=http://msdn.microsoft.com/en-us/library/ms188978.aspx]this page[/url], they're sample under the sub heading Options then PropertyPath.
Fri, 23 May 2008 09:39:31 GMTstevefromOZRE: SSIS Agent Job Passing Parametershttps://www.sqlservercentral.com/Forums/FindPost505972.aspxAlso, if you have deeper levels (eg sequence containers etc), the same syntax but 'slashed' paths to the object before you hit it's variables collection. so with a sequence container named cont and a variable within that named seqCont, this is the path to set that var:
\package\cont.Variables[seqCont].Value
Fri, 23 May 2008 09:37:12 GMTstevefromOZRE: SSIS Agent Job Passing Parametershttps://www.sqlservercentral.com/Forums/FindPost505971.aspxSteve,
That was it.....only been messing with this for 2 days....
Cheers
GlynneFri, 23 May 2008 09:36:04 GMTGlynne SmithRE: SSIS Agent Job Passing Parametershttps://www.sqlservercentral.com/Forums/FindPost505964.aspxUsing a package level parameter, this works in SQLAgent --&gt; \package.Variables[myVar].Value
HTH,
Steve.Fri, 23 May 2008 09:29:42 GMTstevefromOZ