Passing Variables

Question

I am having a hard time passing variables from the control flow to the data flow process. 

The project starts off with a Execute SQL Task.  This task does a select from the sysdatabases table in the master db which creates a list of databases that will be queried for info.  That is stored in a info is stored in a package variable (user::varDBName) that is used to enumerate a For Each Loop and everything is good till this point.  

How can the variable be passed into a t-sql statement in the OLE DB Source?  Or can the variable be assigned to another variable that holds the sql statement that will be accessed by the OLE DB Source?

Any help will be much appreciated.

Thanks

Answer 1

AnthonyKautz wrote:

I am having a hard time passing variables from the control flow to the data flow process.  The project starts off with a Execute SQL Task.  This task does a select from the sysdatabases table in the master db which creates a list of databases that will be queried for info.  That is stored in a info is stored in a package variable (user::varDBName) that is used to enumerate a For Each Loop and everything is good till this point.   How can the variable be passed into a t-sql statement in the OLE DB Source?  Or can the variable be assigned to another variable that holds the sql statement that will be accessed by the OLE DB Source? Any help will be much appreciated. Thanks

Anthony,
You are spot on with your last question there. Another variable (call it vSQL) can hold the SQL statement.
vSQL should have EvaluateAsExpression=TRUE and Expression=<expression to build SQL statement>

The Expression should be something like:
"SELECT * FROM " + @[User::varDBName] + "..MyTable"


Once you have done that you simply set the following in your OLE DB Source Component:
AccessMode="SQL Command From Variable"
SQLCommandVariable="User::vSQL"

Good luck!

-Jamie

Answer 2

Thanks for responding.  This looks great.  I am having one small problem that when the expression is entered the following message appears in the value section:

The expression for variable "varSqlStmt" failed evaluation. There was an error in the expression.

Then when the package is saved before it runs the following non fatal errors appear.

TITLE: Microsoft Visual Studio
------------------------------

Nonfatal errors occurred while saving the package:
Error at Package1: The data type of variable "User::varDBName" is not supported in an expression.

Error at Package1: Reading the variable "User::varDBName" failed with error code 0xC00470D0.

Error at Package1: The expression for variable "VarSqlStmt" failed evaluation. There was an error in the expression.

It is wierd because the variable does take on the expression, but can not pass it to the OLE DB Source.

Any thoughts?

 

Answer 3

I was working on the same type of problem yesterday.  I think I got the same data type error when I tried to concatenate a variable that had a type other than String.  In my case, I was trying to do something like this:

"select * from customer where customer_id > " + @LowerId

I had defined LowerId as an integer variable.  When I changed it to a String I got past that error....and on to another error related to the length of my SQL statement.  If your SQL statement is as short/simple as the example above, I suspect it will work.

The SQL statement I have defined in my variable is about 500 characters long.  When I try to preview my data source with variable replacements, I get an error that I exceeded the maximum of 128 characters.  What is ironic is that the same long SQL statement works fine when I hard-code the variable values.  This led me to believe that the process that replaces variables only allocates 128 characters for its processing.  Just a guess though.

To work around the problem, I decided to "roll my own" variable replacement process by adding a Script Task at the very beginning of my control flow.  I created a script that formats the SQL and replaces the parameters.  The same SQL statement that previously caused my data source to choke now works just fine. 

In my case, I have several SQL statements that drive several different data flows in the package.  I tried to scope the SQL variables at the data flow level but I couldn't figure out how to set those variables in the script...so I have several SQL variables scoped at the package level.

I hope the next release has better direct support for using variables in the SQL text.  This variable indirection over-complicates the process IMHO.

Answer 4

Thanks for the reply.  In the Execute SQL Task when I am using an OLE DB connection with a full result set and any other data type besides object the task fails.  I am actually pulling the name field from the sysdatabases table and don't know if that is part of the problem.  What do you think?

Answer 5

Craig J wrote:

I hope the next release has better direct support for using variables in the SQL text.  This variable indirection over-complicates the process IMHO.

The SQL Command property should be made available as a property of the data-flow IMO - just like (for example) expressions in the derived column component are.
That would mean one less level of indrection.

-Jamie

Answer 6

AnthonyKautz wrote:

Thanks for the reply.  In the Execute SQL Task when I am using an OLE DB connection with a full result set and any other data type besides object the task fails.  I am actually pulling the name field from the sysdatabases table and don't know if that is part of the problem.  What do you think?

What is the type of User::varDBName? It needs to be String.

-Jamie

Answer 7

 I had the variable set as an object, but when it is changed to string then the Execute SQL Statment task fails.  The error is:

[Execute SQL Task] Error: Executing the query "SELECT Name FROM sysDatabases" failed with the following error: "The type of the value being assigned to variable "User::varDBName" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


The task is setup as follows:

General
Result Set:  Full result set
Connection Type:  OLE DB
Connection:  (local)
SQL Source Type:  Direct Input
SQL Statement:  SELECT Name FROM sysDatabases

Result Set
Result Name: 0
Variable Name:  User::varDBName

Variable
Name:  User::varDBName
Scope:  Package
Datatype:  String

Thanks

Answer 8

AnthonyKautz wrote:

I had the variable set as an object, but when it is changed to string then the Execute SQL Statment task fails.  The error is: [Execute SQL Task] Error: Executing the query "SELECT Name FROM sysDatabases" failed with the following error: "The type of the value being assigned to variable "User::varDBName" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. The task is setup as follows: General Result Set:  Full result set Connection Type:  OLE DB Connection:  (local) SQL Source Type:  Direct Input SQL Statement:  SELECT Name FROM sysDatabases Result Set Result Name: 0 Variable Name:  User::varDBName Variable Name:  User::varDBName Scope:  Package Datatype:  String Thanks

AHA, I see the problem. You are using the same variable for 2 different jobs. You need to pass a list of databases back to an Object variable and then enumerate over that list using a Foreach loop which will push the database name into a variable of type String.

More instructions here: http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx and there's a demo package there as well that does exactly what you are trying to do.

-Jamie

Answer 9

Jamie and Craig thanks soo much for the info it was all very educational.  Also, Jamie the info on you blog was right on the money.  I understand how to make the variables work now.  Hopefully this will be my last question.  Everything is set up and I can use the watch window to see that the sql statement variable is populating correctly.  However, when the data flow task is added and the OLE DB Source is set up the variable does not seem to pass correctly.  The OLE DB setup is as follows:

OLE DB Source Editor
Data Access Mode:  SQL Command from Variable
Variable Name:  varSQL

Variable
Name:  User::varSQL
Scope:  Package
Datatype:  String
Expression:  "SELECT * FROM " + @[User::varDBName] + "..tblRefCompany"

Error
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E14  Description: "Statement(s) could not be prepared.".
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E14  Description: "Invalid object name '..tblRefCompany'.".

It seems as though when the variable is not being populated it gives the OLE DB Editor issues.  Any Thoughts?

Again, Thanks for all the help.

 

Answer 10

Anthony Kautz wrote:

Jamie and Craig thanks soo much for the info it was all very educational.  Also, Jamie the info on you blog was right on the money.  I understand how to make the variables work now.  Hopefully this will be my last question.  Everything is set up and I can use the watch window to see that the sql statement variable is populating correctly.  However, when the data flow task is added and the OLE DB Source is set up the variable does not seem to pass correctly.  The OLE DB setup is as follows: OLE DB Source Editor Data Access Mode:  SQL Command from Variable Variable Name:  varSQL Variable Name:  User::varSQL Scope:  Package Datatype:  String Expression:  "SELECT * FROM " + @[User::varDBName] + "..tblRefCompany" Error Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E14  Description: "Statement(s) could not be prepared.". An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E14  Description: "Invalid object name '..tblRefCompany'.". It seems as though when the variable is not being populated it gives the OLE DB Editor issues.  Any Thoughts? Again, Thanks for all the help.

Well yeah, that makes sense. If the variable is empty then your SQL statement would be "SELECT * FROM ..tblRefCompany" which definately wouldn't work.

You should be asking yourself, "Why is the variable empty?"

-Jamie

Answer 11

Dude, I can't believe that I forgot to initialize the variable.  Duh?  Well, thanks again for all the help it was greatly appreciated.