SSIS Agent Job Passing Parameters

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

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

    HTH,

    Steve.

    Steve.

  • Steve,

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

    Cheers

    Glynne

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

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

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

  • 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,

  • 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!

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

  • 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]

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

  • 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 (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.

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

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

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

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

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