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 12»»

SSIS Agent Job Passing Parameters Expand / Collapse
Author
Message
Posted Friday, May 23, 2008 8:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 12, 2011 8:28 AM
Points: 162, Visits: 180
I'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'.
Post #505921
Posted Friday, May 23, 2008 9:29 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Friday, September 12, 2014 10:23 PM
Points: 1,815, Visits: 3,457
Using a package level parameter, this works in SQLAgent --> \package.Variables[myVar].Value


HTH,

Steve.



Steve.
Post #505964
Posted Friday, May 23, 2008 9:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 12, 2011 8:28 AM
Points: 162, Visits: 180
Steve,
That was it.....only been messing with this for 2 days....

Cheers
Glynne
Post #505971
Posted Friday, May 23, 2008 9:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Friday, September 12, 2014 10:23 PM
Points: 1,815, Visits: 3,457
Also, 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




Steve.
Post #505972
Posted Friday, May 23, 2008 9:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Friday, September 12, 2014 10:23 PM
Points: 1,815, Visits: 3,457
Glad 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 this page, they're sample under the sub heading Options then PropertyPath.




Steve.
Post #505976
Posted Tuesday, July 29, 2008 1:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 18, 2012 4:11 AM
Points: 27, Visits: 219
Hi 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.
Post #542386
Posted Thursday, July 23, 2009 3:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 8, 2013 7:56 AM
Points: 16, Visits: 142
I 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,
Post #758625
Posted Wednesday, October 28, 2009 9:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 5, 2014 3:53 AM
Points: 26, Visits: 279
Hi,

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\<dtsx package name>" /SERVER "<server name>" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "\Package.Variables[Location].Value";"T:\" /REPORTING E

Could someone suggest a way out?

Thanks!
Post #810068
Posted Monday, March 1, 2010 1:08 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, August 22, 2014 11:04 AM
Points: 442, Visits: 485
I 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].Value

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.

I really do wish there was some sort of consistency in the MS world regarding case-sensitivity.
Post #874661
Posted Thursday, March 4, 2010 3:57 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:08 PM
Points: 346, Visits: 455
I 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:
\Package\Data Flow Task.Variables[QueryText].Value
\Package\Data Flow Task.Variables[User::QueryText].Properties[Value]
Post #877247
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse