Execute Package Utility / Set Values / How to set Property Path and Values
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: ProcessMode;M Package.Variables[User:rocessMode].Value;M 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
February 4th, 2008 8:28am
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
February 4th, 2008 8:56am
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
February 4th, 2008 9:06am
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.
February 4th, 2008 10:35am
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 promptusing 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 UtilityVersion 9.00.3042.00 for 32-bitCopyright (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 variablewith 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
February 5th, 2008 11:48pm
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?
February 6th, 2008 11:50am
Yes, effectively I changed the variable scope to package and this work fine!!
May 20th, 2012 1:09pm
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
May 20th, 2012 2:06pm