Running an INSERT statement a @variable without using EXEC

The Code-Piece below does not work because @.Tablename is not an objekt.

=============================================

DECLARE @.Tablename varchar(256)

set @.Tablename = 'MyTable'

insert into @.Tablename default values

=============================================

How can i make it work without using EXEC?

Thanks in advance

Raimund

You have to use dynamic SQL (which is what I presume you mean by using EXEC). I don't think there is any other way.|||If I use scope_identity with an insert-statement by dynamic SQL, scope_identity reurns NULL;|||

If you put the Scope_identity call into the dynamic sql you will be able to capture it to a variable. That variable can then be returned if you use the sp_executesql form of dynamic sql.

|||

You need to call the identity in the dynamic SQL

declare @.tablename varchar(100)

declare @.sql nvarchar(1000)

declare @.i int

set @.tablename = 'mytable'

set @.sql = 'insert into ' + @.tablename + ' default values

set @.i = scope_identity()'

exec sp_executesql @.sql, N'@.i int output', @.i output

select @.i

|||This does mean you can put this code in a UDF|||

Ok. It works fine.

Thanks.

Best Regards

Raimund

Run Variable in DataReader Source?

Hi all,
I am trying to have a DataReader Source that can run a variable which I used to store the SQL statement. For example, I have:
Variable #1
Variable name: tablename
Data Type: string
Value: "name_of_table"
Variable #2
Variable name: sql_stmt
Data Type: string
Value: "SELECT * FROM " + @.tablename
I want to use DataReader Source to run Variable #2 in the SqlCommand that connects to an ODBC connection. If it is possible by any way, please let me know. Thanks in advance.
Daren
Use a property expression on the SqlCommand of the DataReader Source. Expressions for Data Flow components can be found on the Data Flow task itself. Select the Data Flow and open the VS Properties grid. Then select expressions.|||Hi Darren,
I still do not get how to use Variables in DataReader Source? Can you lead me to an example or show me a simple example on how to get it to work on AdventureWorks database.
Thank you.
Daren
|||

A variable can be used in an expression.

A variable can be an expression in so much as you can set the EvaluateAsExpression property to True, and the Expression property to an expression. The value of the variable will then be the evaluated expression result.

The DataReader source as a SqlCommand property, and that supports expresisons as well, this is called a property expression. If you don't know what this is, please look it up in Books Online. So the SqlCommand can be a literal, or an expression which itself can reference a variable. That variable can be set via EvaluateAsExpression as well.

These topics are well documented in Books Online. Try these for a start-

DataReader Source (http://msdn2.microsoft.com/en-us/library/ms137897(SQL.90).aspx) - "This source includes the SQLCommand custom property. This property can be updated by a property expression when the package is loaded. For more information, see Integration Services Expression Reference, Using Property Expressions in Packages, and Source Custom Properties."

Using Property Expressions to Specify Property Values for Data Flow Objects (http://msdn2.microsoft.com/en-us/library/ms136104.aspx)

|||Ok, thanks Darren. I am gonna try it out to see if it works...
Daren
|||Hi Darren,
Sorry to bother you again, for the following sentence:
So the SqlCommand can be a literal, or an expression which itself can reference a variable. That variable can be set via EvaluateAsExpression as well.
do you mean I can write expression in the SqlCommand? like "Select * from "+@.tablename? as this is how I write in the expression for a variable. If this is not the way to write it, can you show me an example on how to do it? because the online book is not helping much without any example.
Thanks in advance.
Daren
|||

I will tell you how I run expressions on my datareaders. I am by no means stating that the following is the best way, but it's the only way I know of and it works for me. If you have not already found the datareader expression box, which is definitely not placed intuitively given that most other expressions are found on the tasks themselves, do the following:
- Once inside your dataflow task, click on the workarea (to make sure you haven't highlighted anything within the dataflow)
- Look in the properties window (mine found on bottom right)
- Scroll down in your properties to the Expressions box, and click on the ellipses
- Once there, looking at the properties available in the drop down box you will see any datareader.SqlCommand that you currently have in your dataflow. From there click on the corresponding expression ellipses.
- Here is where you can set queries similar to the one you posted above.
Beware the expressions are very picky with data types if you haven't already found that out, so you'll have to use the casting terminology to cast variables to a string if they aren't already. Here's an example for you, where I'm casting variables that are of type DateTime:

"SELECT * FROM DATE_TBL WHERE DATE BETWEEN '" + (DT_WSTR, 30) @.[User::IVR_START_DATETIME] + "' AND '" + (DT_WSTR, 30) @.[User::IVR_END_DATETIME] + "'"

Hope this helps, I know the pain I went through finding out that datareaders could use expressions, especially back when there was little info available.

Adrian

|||

Adrian is right that you have to be wary of data types - its easy to get confused. Always remember that all you are doing when you are building your SQL statement is concatenating strings. So, make sure everything is converted to a string (i.e. DT_STR or DT_WSTR) before trying to concatenate.

Not sure I agree that the expressions interface isn't placed intuitively but I'll bow to Adrian's opinion on that one :)

-Jamie

|||Hi Adrian, sorry for this late reply, I was not around for the past few days doing something else. But thanks for the little guide of yours, it helps me a lot. Also I will take note on the data types there. Thanks again.
Daren
|||

Adrian, I have a similar cast of a DateTime variable to a string, but in attempting to get part of the date (for example, the Year), SSIS returns an invalid cast.

Example code: (DT_WSTR,4)YEAR(@.[User::DateToImport])

This evaluates perfectly in the Expression Builder, but upon running the project, the following errors are returned:

Error: The function "YEAR" does not support the data type "DT_WSTR" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.

Error: The expression "(DT_WSTR, 4) YEAR(@.[User::DateToImport])" on property "ConnectionString" cannot be evaluated. Modify the expression to be valid.

So, there's a bit of a disconnect here somewhere...

|||What type is the variable User::DateToImport? The error says to me that it is String (DT_WSTR). It needs to be a DateTime.|||It is very much a DateTime type.|||

Take a read of this: http://blogs.conchango.com/jamiethomson/archive/2005/10/11/2261.aspx

It may be a pointer as to the problem. Basically there is bug that means a datatime variable can hold a value which isn't a datetime.

-Jamie

|||

Thank you Adrian.
I have been looking for this answer for about 2 weeks now. Your post was the only one that i have found that told me that i need to specify my variable in the Expression ellipses. I have been trying unsuccesfully to put my varaible in the [DataReader Source].[SQL Command] property.

Run Variable in DataReader Source?

Hi all,
I am trying to have a DataReader Source that can run a variable which I used to store the SQL statement. For example, I have:
Variable #1
Variable name: tablename
Data Type: string
Value: "name_of_table"
Variable #2
Variable name: sql_stmt
Data Type: string
Value: "SELECT * FROM " + @.tablename
I want to use DataReader Source to run Variable #2 in the SqlCommand that connects to an ODBC connection. If it is possible by any way, please let me know. Thanks in advance.
Daren
Use a property expression on the SqlCommand of the DataReader Source. Expressions for Data Flow components can be found on the Data Flow task itself. Select the Data Flow and open the VS Properties grid. Then select expressions.|||Hi Darren,
I still do not get how to use Variables in DataReader Source? Can you lead me to an example or show me a simple example on how to get it to work on AdventureWorks database.
Thank you.
Daren
|||

A variable can be used in an expression.

A variable can be an expression in so much as you can set the EvaluateAsExpression property to True, and the Expression property to an expression. The value of the variable will then be the evaluated expression result.

The DataReader source as a SqlCommand property, and that supports expresisons as well, this is called a property expression. If you don't know what this is, please look it up in Books Online. So the SqlCommand can be a literal, or an expression which itself can reference a variable. That variable can be set via EvaluateAsExpression as well.

These topics are well documented in Books Online. Try these for a start-

DataReader Source (http://msdn2.microsoft.com/en-us/library/ms137897(SQL.90).aspx) - "This source includes the SQLCommand custom property. This property can be updated by a property expression when the package is loaded. For more information, see Integration Services Expression Reference, Using Property Expressions in Packages, and Source Custom Properties."

Using Property Expressions to Specify Property Values for Data Flow Objects (http://msdn2.microsoft.com/en-us/library/ms136104.aspx)

|||Ok, thanks Darren. I am gonna try it out to see if it works...
Daren
|||Hi Darren,
Sorry to bother you again, for the following sentence:
So the SqlCommand can be a literal, or an expression which itself can reference a variable. That variable can be set via EvaluateAsExpression as well.
do you mean I can write expression in the SqlCommand? like "Select * from "+@.tablename? as this is how I write in the expression for a variable. If this is not the way to write it, can you show me an example on how to do it? because the online book is not helping much without any example.
Thanks in advance.
Daren
|||

I will tell you how I run expressions on my datareaders. I am by no means stating that the following is the best way, but it's the only way I know of and it works for me. If you have not already found the datareader expression box, which is definitely not placed intuitively given that most other expressions are found on the tasks themselves, do the following:
- Once inside your dataflow task, click on the workarea (to make sure you haven't highlighted anything within the dataflow)
- Look in the properties window (mine found on bottom right)
- Scroll down in your properties to the Expressions box, and click on the ellipses
- Once there, looking at the properties available in the drop down box you will see any datareader.SqlCommand that you currently have in your dataflow. From there click on the corresponding expression ellipses.
- Here is where you can set queries similar to the one you posted above.
Beware the expressions are very picky with data types if you haven't already found that out, so you'll have to use the casting terminology to cast variables to a string if they aren't already. Here's an example for you, where I'm casting variables that are of type DateTime:

"SELECT * FROM DATE_TBL WHERE DATE BETWEEN '" + (DT_WSTR, 30) @.[User::IVR_START_DATETIME] + "' AND '" + (DT_WSTR, 30) @.[User::IVR_END_DATETIME] + "'"

Hope this helps, I know the pain I went through finding out that datareaders could use expressions, especially back when there was little info available.

Adrian

|||

Adrian is right that you have to be wary of data types - its easy to get confused. Always remember that all you are doing when you are building your SQL statement is concatenating strings. So, make sure everything is converted to a string (i.e. DT_STR or DT_WSTR) before trying to concatenate.

Not sure I agree that the expressions interface isn't placed intuitively but I'll bow to Adrian's opinion on that one :)

-Jamie

|||Hi Adrian, sorry for this late reply, I was not around for the past few days doing something else. But thanks for the little guide of yours, it helps me a lot. Also I will take note on the data types there. Thanks again.
Daren
|||

Adrian, I have a similar cast of a DateTime variable to a string, but in attempting to get part of the date (for example, the Year), SSIS returns an invalid cast.

Example code: (DT_WSTR,4)YEAR(@.[User::DateToImport])

This evaluates perfectly in the Expression Builder, but upon running the project, the following errors are returned:

Error: The function "YEAR" does not support the data type "DT_WSTR" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.

Error: The expression "(DT_WSTR, 4) YEAR(@.[User::DateToImport])" on property "ConnectionString" cannot be evaluated. Modify the expression to be valid.

So, there's a bit of a disconnect here somewhere...

|||What type is the variable User::DateToImport? The error says to me that it is String (DT_WSTR). It needs to be a DateTime.|||It is very much a DateTime type.|||

Take a read of this: http://blogs.conchango.com/jamiethomson/archive/2005/10/11/2261.aspx

It may be a pointer as to the problem. Basically there is bug that means a datatime variable can hold a value which isn't a datetime.

-Jamie

|||

Thank you Adrian.
I have been looking for this answer for about 2 weeks now. Your post was the only one that i have found that told me that i need to specify my variable in the Expression ellipses. I have been trying unsuccesfully to put my varaible in the [DataReader Source].[SQL Command] property.

sql

Run DTS Package based on a Variable

I'm trying to run a DTS package based on the result of a partcular
query.
SELECT SUM(Bal_Orig_Curr) AS bal
INTO tempbal
FROM Gw078p
I want to run the rest of the package only if the value of "bal" is
equal to zero otherwise generate an email. How can create this split?
ThanksYou could store the results of the query in a global variable. After that, a
vbscript task can create the branching logic by updating the precedence
constraints.
Please see http://www.sqldts.com/214.aspx for more info. Much easier in SSIS
as the precedence constraints themselves can be based on an expression in
the GUI.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Run DTS Package based on a Variable

I'm trying to run a DTS package based on the result of a partcular
query.
SELECT SUM(Bal_Orig_Curr) AS bal
INTO tempbal
FROM Gw078p
I want to run the rest of the package only if the value of "bal" is
equal to zero otherwise generate an email. How can create this split?
Thanks
You could store the results of the query in a global variable. After that, a
vbscript task can create the branching logic by updating the precedence
constraints.
Please see http://www.sqldts.com/214.aspx for more info. Much easier in SSIS
as the precedence constraints themselves can be based on an expression in
the GUI.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Run DTS Package based on a Variable

I'm trying to run a DTS package based on the result of a partcular
query.
SELECT SUM(Bal_Orig_Curr) AS bal
INTO tempbal
FROM Gw078p
I want to run the rest of the package only if the value of "bal" is
equal to zero otherwise generate an email. How can create this split?
ThanksYou could store the results of the query in a global variable. After that, a
vbscript task can create the branching logic by updating the precedence
constraints.
Please see http://www.sqldts.com/214.aspx for more info. Much easier in SSIS
as the precedence constraints themselves can be based on an expression in
the GUI.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

run a Package from package

I have a DTS package that executes a procedure returns a global variable.

In the next ActiveX task (vb) I'd like to examine the variable and based on its value run other DTS packages but I do not know how to set up the code to run a DTS package from within an ActiveX script.

Any help appreciated .
SteveGo to Start > Run and type DTSRUNUI
Type in the SQL Server connection info at the bottom
Click the [...] button at the to choose the package name. Click [Advanced]
Click the [Generate] button
To the left of the [Generate] button, you will see the command to run the DTS from command line.

Run a global variable dynamically

I have a SSIS pkg with several task. I need to run a global variable dynamically.

the global variable's value is basically a bat file with two arguments (\\.....\..bat \\......\.xls \\.....\...xls)

bat file arg1 arg2

This global variable gets set from its previous task at run time. I did put in this global variable as an argument in expression and for executable i am calling the cmd.exe file. But for some reason it does not work. the command prompt comes up wait for an action from user which is wrong. Please help

Thanks

Make sure that you have a full path to the batch file.

Also, make sure you are using the "/C" flag before the path to the batch file.

Command should look like:

cmd.exe /C \\server\mybatchfile.bat arg1 arg2......|||

Thanks Phil. That worked.