Welcome to EMC Consulting Blogs

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?

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?

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

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

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

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

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

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

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.

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?

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

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

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!

Thanks Jamie!

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

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?

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! ;-)

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,

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

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?

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?

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 :-) ....

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.