Execute Package Utility / Set Values / How to set Property Path and Values

Question

Hi

I have one problem in SSIS for passing Variable Values while executing Package. I'm giving in details as below:

 

I opened Microsoft SQL Server Management Studio

Made Connection to Integration Services

To Execute Package I Right Clicked and Click on Run Package

Then I Clicked on Execute and package was executed successfully.

 

Problem is that if I try to Set Values then Package through Error

DTExec: Could not set ProcessMode value to M.

 

Basically I could not understand in which format I should pass the Variables.

What I tried is listed below:

1. ProcessMode;M
2. Package.Variables[User:rocessMode].Value;M
3. Package.Variables[ProcessMode].Value;M

 But every time I got errors.

 

And then I tried from Command Line

DTEXEC /DTS "\MSDB\Load_Order" /SERVER SERVERNAME /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING V  /SET Package.Variables[ProcessMode].Value;M

 

First time Process run successfully. And it has changed the ProcessMode to M also. But after that it was also not changing the ProcessMode Value to M.

 

Please help in regarding. I tried a lot from Site examples also, but could not get proper solution.

 

Thanks in advance

Bhudev

Answer 1

 

The property path should be as below -

 

Code Snippet

\Package.Variables[User::ProcessMode].Properties[Value]

 

 

If using in a command line, then you often see a quoted path, followed by the value, as below -

 

Code Snippet

/SET "\Package.Variables[User::Variable].Properties[Value]";NEWVALUE

 

 

Answer 2

Hi,

 

I just tried and found Error Message:

 

Warning Description: The package path referenced an object that cannot be found: "Package.Variables[ProcessMode].Value". This occurs when attempt is made to resolve a package path to an object that cannot be found.

 

DTExec: Could not set Package.Variables[ProcessMode].Value value to M.

 

Please Response

 

Thanks & With Best Regards

Bhudev

 

Answer 3

The path does not match what I suggested.

 

Compare the following two lines -

Package.Variables[ProcessMode].Value

\Package.Variables[User:rocessMode].Properties[Value]

 

To be sure we are talking about the same thing, try using DTEXECUI, and on the Set Values page enter the Property Path as I suggested and then the Value you want. If it fails, go to the Command Line page and copy the command and post it here.

Answer 4

Hi Darren,

 

As you specified, I used DTEXECUI and on Set Values page, I entered values as below:

 

PropertyPath: \Package.Variables[UserrocessMode].Properties[Value]

Value: M

 

And after entering these values when I move to Command Line Page Utility has made following Command Line Script:

 

/DTS "\MSDB\Load_PartMaster" /SERVER INDAMBRISHNB /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING V  /SET "\Package.Variables[UserrocessMode].Properties[Value]";M

 

Then I press Execute Button and find following error:

Could not set \Package.Variables[UserrocessMode].Properties[Value] value to M.

 

And Then I tried this command script  from Command prompt using DTEXEC Utility and then also I got same error message.

 

Well one more thing I noticed that when you set values on Set Values Page and then move to Command Line Page you will get Command Line Script for Set Values within Quotes "...." as below:

 

/SET "\Package.Variables[UserrocessMode].Properties[Value]";M

 

But If you move to Command prompt and type DTEXEC /? SET and then you will get help as below

 

Code Snippet

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

 

Set property_path;value

 

Optional. Overrides the configuration of a variable, property, container,
log provider, foreach enumerator, or connection within a package. When
specified, the /SET option sets the specified propertypath to the value given.
Multiple /SET options can be specified.

 

The following is an example of executing a package that is provided a variable
with a value.

 

dtexec /f mypackage.dtsx /set \package.variables[myvariable].Value;myvalue

 

 

Which does not states that there is neen of quotes "....."

I believe that Microsoft Developers and Help File Writer was not in sink.

 

But major problem is that both ways are not working.

 

Please response

 

Thanks & With Best Regards

Bhudev

Answer 5

The quotes around the path and value are both optional, unless required to delimit the value.

 

Sorry, I was a bit zealous in my format above as well. It appears that the following formats are both valid -

 

\Package.Variables[ProcessMode].Value

\Package.Variables[ProcessMode].Properties[Value]

 

The variable namespace can also be inferred as shown.

 

 

So saying all that, why does it not work for you. The only reason I can think of now is that the variable itself does not exist.

 

Extract the package from MSDB, and open in the designer (or even notepad), to check for that variable. Is it something like the local development copy has the variable, but the version in the MSDB store is older, and does not?

Answer 6

And if it does exist, make sure the variable is at the package-level scope and not something more specific.

 

Answer 7

Yes, effectively I changed the variable scope to package and this work fine!!

Answer 8

Hi ,Bhudev

in ssis child package configured using parent package variables.

just check first child package configuration and according to it check your parent package.

while checking parent package make sure that the variable scope is available to execute package task.

---

Regards, nishantcomp2512 Please mark posts as answered where appropriate

Answer 9

Hi,

I followed as outlined above which seems to work well.  Except the data I enter for the variable value seems to change.

If I have Property Path as: \Package.Variables[User::sqlWhereClause].Properties[Value]

Then Paste Value: AND ("1:99:4:SPRiProjectojPSTFocusArea"."Project path"=';001606;001605;002305')

When you tab away or look at the Command line value the data I pasted as my variable value now looks like : AND (\"1:99:4:SPRiProjectojPSTFocusArea\".\"Project

Thanks for looking.

Jamie.

Answer 10

I have passed values to an SSIS package which expects a variable I named 'strJobFlow'.  This assignment worked for me in the Property Path of Set Values for the job that calls the package:

\Package.Variables[User::strJobFlow].Properties[Value]

Note the double colon.  I cannot remember where I uncovered that, but for me it was the key to making this work.

Hope that helps.

Steve

Answer 11

SSIS is a steaming pile of crap.
All I get is "The package execution returned DTSER_FAILURE (1)."
What a totally fucking useless error message.
I am trying to convince my employers to not use it.
I can't see any benefit it has over a console app.