SSIS Agent Job Passing Parameters

Question

Post reply

SSIS Agent Job Passing Parameters

Glynne Smith

SSC Eights!

Points: 804
More actions
May 23, 2008 at 8:46 am

#122174

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

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

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

HTH,

Steve.

Glynne Smith SSC Eights! Points: 804 More actions · Answer 2

Steve,

That was it.....only been messing with this for 2 days....

Cheers

Glynne

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

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.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

Glad you got it to work!

look at it this way, it's two days that you didn't spend doing something else 😛

Oh and the pointer i got was from this page, they're sample under the sub heading Options then PropertyPath.

Steve.

mackie Right there with Babe Points: 745 More actions · Answer 5

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.

stew_b Mr or Mrs. 500 Points: 550 More actions · Answer 6

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,

Ambuj Mathur SSC Eights! Points: 892 More actions · Answer 7

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!

LerxtDBA SSCrazy Points: 2190 More actions · Answer 8

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.

ohack SSCrazy Points: 2043 More actions · Answer 9

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]

Derek Robinson Mr or Mrs. 500 Points: 594 More actions · Answer 10

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

saqib-431177 SSC Enthusiast Points: 128 More actions · Answer 11

stevefromOZ (5/23/2008)
Using a package level parameter, this works in SQLAgent --> \package.Variables[myVar].Value
Steve.

Can you please where in SqlAgent, I can pass the parameter?

I couldnt understand the above directions.

LerxtDBA SSCrazy Points: 2190 More actions · Answer 12

saqib-431177 (7/13/2011)
stevefromOZ (5/23/2008)
Using a package level parameter, this works in SQLAgent --> \package.Variables[myVar].Value
Steve.
Can you please where in SqlAgent, I can pass the parameter?
I couldnt understand the above directions.

Enter them on this tab of the Job Step Properties screen when you have selected an SSIS Job Step

Edit: I had way too much info visible there at first. :Whistling: Had to edit screen shot.

saqib-431177 SSC Enthusiast Points: 128 More actions · Answer 13

Thanks for the response. The image is missing but I got it.

Also Is it possible to modify connectionstring value under "Data Sources" tab ?

LerxtDBA SSCrazy Points: 2190 More actions · Answer 14

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