SSIS Agent Job Passing Parameters

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » SSIS Agent Job Passing Parameters

16 posts, Page 1 of 2

1 2 »»

SSIS Agent Job Passing Parameters

Rate Topic

Display Mode

Topic Options

Author

Message

Glynne Smith

Posted Friday, May 23, 2008 8:46 AM

SSC-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

stevefromOZ

Posted Friday, May 23, 2008 9:29 AM

SSCommitted

Group: Moderators
Last Login: Yesterday @ 11:15 PM
Points: 1,764, Visits: 3,202

Using a package level parameter, this works in SQLAgent --> \package.Variables[myVar].Value

HTH,

Steve.

Steve.

Post #505964

Glynne Smith

Posted Friday, May 23, 2008 9:36 AM

SSC-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

stevefromOZ

Posted Friday, May 23, 2008 9:37 AM

SSCommitted

Group: Moderators
Last Login: Yesterday @ 11:15 PM
Points: 1,764, Visits: 3,202

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

stevefromOZ

Posted Friday, May 23, 2008 9:39 AM

SSCommitted

Group: Moderators
Last Login: Yesterday @ 11:15 PM
Points: 1,764, Visits: 3,202

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

mackie

Posted Tuesday, July 29, 2008 1:09 AM

SSC 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

stew_b

Posted Thursday, July 23, 2009 3:13 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 08, 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

Ambuj Mathur

Posted Wednesday, October 28, 2009 9:39 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 8:39 AM
Points: 26, Visits: 267

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

LerxtDBA

Posted Monday, March 01, 2010 1:08 PM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:11 AM
Points: 441, Visits: 442

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

I really do wish there was some sort of consistency in the MS world regarding case-sensitivity.

Post #874661

ohack

Posted Thursday, March 04, 2010 3:57 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 3:03 AM
Points: 282, Visits: 400

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 »

16 posts, Page 1 of 2

1 2 »»

Permissions