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'.
July 13, 2011 at 10:36 am

This was removed by the editor as SPAM
July 13, 2011 at 11:02 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 19 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: 2121 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.

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

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

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