Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Property Paths syntax

A comment here from Evan made me realise that there doesn't really exist a good reference for property paths in SSIS. Property paths are used in configurations and the /SET option of dtexec.exe to set properties in the package dynamically at runtime.

For some inexplicable reason dtexecui.exe does not contain a user interface for building these things yourself so you have to manually encode them yourself. This is a major shortcoming given that they could easily lift the package browser from the Configurations Manager:

 

If you want to change this, vote here and ADD A COMMENT: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124577

Anyway in the meantime, until Microsoft give us a package browser in dtexecui.exe, I thought it would be useful to list a few myself. This is as much for my own reference as much as anyone else's.

  • A Variable

\Package.Variables[Namespace::VariableName].Properties[Value]

  •  Connection string of a connection manager

\Package.Connections[ConnectionManagerName].Properties[ConnectionString]

  • A property of the package

\Package.Properties[PropertyName]

  • Variable scoped to a container

\Package\ContainerName.Variables[Namespace::VariableName].Properties[Value]

  • Property of a container (remember that a task is inherently a container as well)

\Package\TaskName.Properties[PropertyName]

  • Property of a component in a data-flow (assuming the component exposes properties that can be set from the data-flow)

\Package\Data Flow Task.Properties[[DataReader Source].[SqlCommand]]

 

Hopefully that should be enough of a reference to be going on with. If I've missed anything out, let me know!

-Jamie

 

Published Tuesday, March 13, 2007 12:35 AM by jamie.thomson

Comments

 

Barclay said:

You hint at this with the variable scoped to a container, but is the path that starts with \package a list of 1 or more \containernames (e.g. \package\For Each Container\EXE Process File.Properties[WorkingDirectory]) to reference tasks that are in containers?

April 25, 2007 2:14 PM
 

Steve said:

Hi Jamie,

How can \Package\Data Flow Task.Properties[[DataReader Source].[SqlCommand]] be used within a Script task to set the value?

I want to set the value of the SqlCommand after the script task looks at the value of a package variable to dynamically create the Sql Command to be used in the data flow task.  Do you have any suggestions?

May 4, 2007 4:37 PM
 

jamie.thomson said:

Steve,

It can't be done. You cannot change properties of the package from within the script task.

What you CAN do is store the sql statement in a variable (this can be done from a script task) and then use a property expression to set \Package\Data Flow Task.Properties[[DataReader Source].[SqlCommand]]  dynamically.

-Jamie

May 4, 2007 4:57 PM
 

Steve said:

Jamie,

Thanks for the reply.  That is exactly what I was looking for.  I was just suggesting the Script Task because I knew I could modify the variable value within that.  In any event, I am able to do what I needed to do.  Thanks so much for the help!

-Steve

May 8, 2007 6:19 PM
 

Kar Rao said:

I tried changing a variable from DTExecUI.

The variable was originally read from a XML config file. I stored the package to MSDB. I then ran dtexecui, selected msdb package, and in Set Values, I entered property path and value. Got a DTExec error :

Could not set "\Package.Variables[User::var_input_step_Name].Properties[Value]" value to "RAW".

I also tried removing the "", and I even deleted the configuration. Still the same error.

What am I doing wrong?

TIA

May 16, 2007 2:38 PM
 

Adam Fox said:

How do you define the connection string from a connection manager in a parent package?

No matter what I try I get the "Attempting to set variable" "Warning variable does not exist"

Thanks

Adam

September 10, 2007 7:37 PM
 

jamie.thomson said:

Adam

Try:

\Package.Connections[conn_man_name].Properties[ConnectionString]

-Jamie

September 10, 2007 8:00 PM
 

Adam Fox said:

Thanks for response Jamie - I have tried that tho.

Heres a bit more information:

I have a master loader package which defines 3 connection managers for staging, datamart and system.

I want to use 4 xml package configurations to load data from 4 offices that all have the same databases.  So I have defined one configuration that defines the connection string for the 3 databases mentioned above just to test the configuration.

The master loader executes various other dimension and fact loaders.

Each of these executed packages also define the same 3 connection managers.  In the child package I have loaded a variable from the parent, and this works ok.  In the child package I am trying to grab the connection string for the 3 master connection managers but this fails.  It gives me the same "attempting to load parent package variable" message but then gives the warning the "parent variabel not loaded because parent variable doesnt exist"

I have tried

\Package.Connections[conmgr_name].Properties[ConnectionString]

Package.Connections[conmgr_name].Properties[ConnectionString]

Connections[conmgr_name].Properties[ConnectionString]

conmgr_name.Properties[ConnectionString]

conmgr_name[ConnectionString]

But all to no avail :o(

Anything jump out at anybody as to what I am doing wrong?

Thanks again

Adam

September 10, 2007 8:51 PM
 

SSIS Junkie said:

Someone recently asked me if it was possible to dynamically set the location of a checkpoint file. Happily

November 15, 2007 8:46 PM
 

bear in a box said:

hi jamie,

i have a copy-data task, underneath it are components of ole db source, data conversion and ole db destination.

i tried SET VALUE the filename of the DB SOURCE so that i wouldnt have to change the filename, save then run the package.  

these wouldnt work (i was careful with Cases because underneath, the C++ based engine would reject them; because SQL execution is not translated like in VB):

\Package\CopyDataTask.OLE DB Source.Properties[OpenRowset]

or

\Package\CopyDataTask.Properties[[OLE DB Source].[Table]]

or

\Package\CopyDataTask.Properties[[OLE DB Source].[OpenRowset]]

- i checked the xml configuration, just to validate the path, but it didnt go below that level.

on perhaps another way, you mentioned that:

"..and then use a property expression to set \Package\Data Flow Task.Properties[[DataReader Source].[SqlCommand]]  dynamically."

in reverse, can a property expression (OpenRowSource of OLE db component) get a value from a variable at the higher level?

tia,

roasty

January 10, 2008 2:12 PM
 

bear in a box said:

ops, in the last line, i meant:

in reverse, can a property expression (OpenRowset of OLE db component) get a value from a variable at the higher level?

---

sorry, i have spent 4 hours already over the Internet and help over this; its 10:26pm where i am.

January 10, 2008 2:18 PM
 

jamie.thomson said:

Hi,

"can a property expression (OpenRowset of OLE db component) get a value from a variable at the higher level?"

What do you mean by "higher level"?

-Jamie

January 10, 2008 2:27 PM
 

bear in a box said:

like the ole db source, it is a component so viewing it from a package tree, it is below the copy data source task.  in your picture above, it would be like the PackageLogFile has been expanded, then expanded further for the components.

at this lower, expanded level, there are no places for variables.

up at the copy data source task level, or in your picture above, the level of connection managers, there is place for variables.

if i can set a variable at that level then have the component of the task try to get the value of the variable as its OpenRowset value?

January 10, 2008 2:43 PM
 

jamie.thomson said:

I'm sorry, I still don't understand.

"at this lower, expanded level, there are no places for variables."

Do you mean there's no place to assign variable values to properties of components? If so, that's not true. You should read this:

Dataflow expressions

(http://blogs.conchango.com/jamiethomson/archive/2007/10/09/SSIS_3A00_-Dataflow-expressions.aspx)

-Jamie

January 10, 2008 3:00 PM
 

bear in a box said:

hi jamie,

i am quite sick and tired, i will check back next week.

i tried another way by setting the properties of the connection manager instead, to use a Name File connection string; also by using a user-defined variable like RawDataFilename as the value of Filename property.

property path to set in the DT exec ui:

\Package.Connections[CopyDataTask].Properties[ConnectionString]

value:

Data Source=\\servername\raw data folder\distributor\yearmonth;File Name=\\servername\raw data folder\distributor\yearmonth\111.dbf;Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=DBase 5.0;

or the 2nd workaround i tried:

Filename - @[User::Rawdatafilename]

property path to set:

\Package\CopyDataTask.Variables[User::Rawdatafilename].Properties[Value]

value:

\\servername\raw data folder\distributor\yearmonth\111.dbf

---

according to local sql online help:

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/extran9/html/a4bfc925-3ef6-431e-b1dd-7e0023d3a92d.htm

Property Expression for the ConnectionString Property of a Flat File Connection Manager

The following property expression can be used to set the ConnectionString property of a Flat File connection manager. The expression uses a single user-defined variable, myfilenamefull, which contains the path to a text file.

@[User::myfilenamefull]

---

however, that is a for a flat file, i am handling dbf files.

no need for you to burn so much over this...i will keep on finding a workaround.

tia,

rostand

January 10, 2008 3:03 PM
 

bear in a box said:

i will check the dataflow expression link above my previous post...again tia...

January 10, 2008 3:05 PM
 

Chris W said:

Jamie,

Up above, you said the following which is exactly what I am trying to accomplish.  Unfortunately, I'm enough of a newbie to completely not understand the statement.

"What you CAN do is store the sql statement in a variable (this can be done from a script task) and then use a property expression to set \Package\Data Flow Task.Properties[[DataReader Source].[SqlCommand]]  dynamically."

How do I store the sql statement in a variable?  Through code in the script task?  I have never used the script task but I can brush up on it.

Once I do get the sql statement into a variable, what property expression do I set?  On the Data Reader task in the data flow?  Do I set the sql command in the Data Reader to a user variable like:  @[USER::MySqlStmt]

Thanks!

January 14, 2008 10:48 PM
 

jamie.thomson said:

Chris W,

Yeah, storing the SQL statement in a variable will most likely be done using a script task. There's plenty of articles around the blogosphere explaining how:

http://blogs.conchango.com/jamiethomson/archive/tags/Scripting/default.aspx

http://search.live.com/results.aspx?q=script+task&form=QBJK&q1=macro%3Ajamiet.ssis

"Do I set the sql command in the Data Reader to a user variable like:  @[USER::MySqlStmt]"

Yep, you can do that. Or you can use the OLE DB Source and set the SQLCommand (I think thats its name) property via property expressions on the parent data flow.

-Jamie

January 15, 2008 2:22 AM
 

Chris W said:

Thanks Jamie!

Took me a while but I figured it out from your comments here and from the other blogs.

January 15, 2008 10:15 PM
 

Justin said:

Hi Jamie

Plz help me to  resolve my pblm

setting values to a variable in ssis at runtime

i need to assign the value to the variable at runtime

canu give me the script used for it?

April 4, 2008 10:58 AM
 

jamie.thomson said:

Do you mean from the command-line (i.e. as per the subject of this blog entry)? If so, the answer is above.

-Jamie

April 4, 2008 4:31 PM
 

Ted Pin said:

Jamie, I don't know how you make time to write so many consistently informative articles, but they are indispensible. Thanks a lot 'ol chap! ;-)

April 11, 2008 4:22 PM
 

Stan said:

tried the following from command line in sqlagent step and it failed, trying my best to piece all the articles together on this subject but dont really see any recent good examples for dummies when it comes to datetime...

"c:\Program Files\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /SQL "\Reporting ETL" /SERVER MYSERVER /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET \Package.Variables[User::inceptFromDate].Properties[Value];"01/02/2007"

I wanted 2/1/8 and saw one blog that said dd/mm/yyyy was legitimate.

inceptFromDate is defined in pkg as a ValueType of DateTime in the pkg.   It is used directly (for instance) in an oledb source query against a datetime column directly,

April 17, 2008 5:36 AM
 

jamie.thomson said:

Stan,

Is this any use:

How to pass DateTime parameters to a package via dtexec

(http://blogs.conchango.com/jamiethomson/archive/2005/10/11/SSIS_3A00_-How-to-pass-DateTime-parameters-to-a-package-via-dtexec.aspx)

-Jamie

April 17, 2008 7:04 AM
 

Stan said:

no, that was the first article I read, it wasnt clear what the "fix" meant to using this feature going forward, exactly what the format needs to be on the command line (although I tried one of the formats as you can see) and where/how (if at all anymore) the cast needs to take place.  The discussion felt "just out of reach".

BTW I should have written "I wanted 2/1/7 and saw..."

April 17, 2008 2:02 PM
 

jamie.thomson said:

Hi Stan,

I just tried the following and it seemed to work OK:

dtexec /F Package.dtsx /SET \Package.Variables[User::Var].Properties[Value];200

7-01-02

Any joy for you?

-Jamie

April 17, 2008 4:37 PM
 

Stan said:

bear with me Jamie, what is the 200 for, and why do you show 7-01-02 two lines below that?  Is the 7-01-02 part of the command, or some pkg feedback?

April 17, 2008 5:42 PM
 

jamie.thomson said:

Sorry Stan. Typo :)

It should be:

dtexec /F Package.dtsx /SET \Package.Variables[User::Var].Properties[Value];2007-01-02

-Jamie

April 17, 2008 8:34 PM
 

Stan said:

thanks Jamie, here is the behavior using your example(remember inceptFromDate's ValueType property is datetime in the package, at least before the command line override is attempted)...

-1st step of pkg successfully executes the following statement:

exec usp_ExtractDate_Insert ?,?,?,?   where 2nd param is mapped from User::inceptFromDate  with a direction of Input and a data type of Date, pretty much the same for inceptToDate which is 3rd param and defaults in this exercise

-2nd step seems to be the one aborting:

select inceptFromDate as inceptFromDate, inceptToDate as inceptToDate  from [dbo].[ExtractDate] where endtime is null  (purpose is to set the vars based on final decisions made by usp_ExtractDate_Insert)

Dont have the problem when both use the defaults set in the pkg, ie no command line override.

What is the ValueType of your var?  If it is not a datetime, what command line date mapping issue has MS fixed per the reference at top of article at (http://blogs.conchango.com/jamiethomson/archive/2005/10/11/SSIS_3A00_-How-to-pass-DateTime-parameters-to-a-package-via-dtexec.aspx) and what are you doing with it in your pkg to deal with comparisons between it and sql server datetime columns?

April 20, 2008 6:16 PM
 

Mark Harrison said:

One other trap, these things are case sensitive. Forinstance, \Package.Variables[User::InputFileName].Properties[Value]. The capital V in Value is important.

I'm sure you knew all that anyway, but just in case :-) ....

May 12, 2008 5:54 AM
 

rahul sharma said:

on easy technique to get the property path is

Create a configuration xml file and inclued all the properties or variable which you want to use in the /set option. the configuration file contains the property path for those copy them form configuration file and use it .

Then delete configuration file from package.

August 30, 2008 12:53 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems