Getting SQL result in a variable

Hi there

I have a global variable say cnt in SSIS package, now I want to get total number of rows from a table say emp in that variable cnt.

how do we achieve that?

thanks and regards

Rahul Kuamr

Hello Rahul,

You can use a row count transformation just before the destination component in your dataflow, you a have to declare a variable of type int32 and assign this variable in the row count transformation.

Regards,

Raju

|||

on the same line,another query

If we wish to get result of some SQL query say select name from emp where id='234'

thanks and regards

Rahul kumar

|||

Hi,

To get the result of a query into a variable first you have to declare a variable of the same type which is returned by the query, and use an execute sql task in the control flow, Double click the execute sql task and click on the general tab and make the result set property to "Single Row" and write the query in this way: "Select name as empname from emp where empid = '234'. " Now you can assign the name alias "empname" used in the query to a result set. To do that click on the result set which is available in the execute sql task and give the name of the result set as "empname" and assign to a variable which is of the same type.

Note: property to "Single Row" only works when the query is returning a single row. If the query is returning mutiple rows, you have to declare a variable of the type object and select the property of the result set as "Full result set". and assign the result name to the variable which has been declared as object.

Regards,

Raju

|||

Thanks a lot buddy.

It works!!

Regards

Rahul Kumar

|||

Hello, found this post and am hoping that you can help with assigning a variable with an Execute SQL Task. My task runs, but the variable doesn't get a value, stays blank.

I'm trying to retrieve a string value from a SQL table called tblSys_Config. There's only one row in the table. The column I want is called is an nvarchar(3) field called Config_Code.

I've defined a user variable called TestVar with type as string.

I've set up my Execute SQL Task with a SingleRow result set, and the SQL Statement is "select Config_Code As ConfigCode from tblSys_Config".

The ResultSet for the SQL Task has Resultname ConfigCode (same in "As ConfigCode" in SQL statement), with variable name user::TestVar.

When I execute the task, it runs but just doesn't update the variable, it stays blank. Maybe I'm not looking for the value correctly, just right clicking in design space in VS and choosing Variables to see the value of TestVar, always blank.

I did try setting the result set to Full Result Set, variable to Object, and the Result Name to 0, and got a very helpful value of System.Object. Maybe that's what I have to work with, but I just have a single value to retrieve that should work with SingleRow. I've also tried different naming conventions and making sure Result Name is same case as the column name.

Thanks for any advice you can give me, maybe I do have use an Object Variable.

Chera

|||

cboom wrote:

Hello, found this post and am hoping that you can help with assigning a variable with an Execute SQL Task. My task runs, but the variable doesn't get a value, stays blank.

I'm trying to retrieve a string value from a SQL table called tblSys_Config. There's only one row in the table. The column I want is called is an nvarchar(3) field called Config_Code.

I've defined a user variable called TestVar with type as string.

I've set up my Execute SQL Task with a SingleRow result set, and the SQL Statement is "select Config_Code As ConfigCode from tblSys_Config".

The ResultSet for the SQL Task has Resultname ConfigCode (same in "As ConfigCode" in SQL statement), with variable name user::TestVar.

So far, so good.

cboom wrote:

When I execute the task, it runs but just doesn't update the variable, it stays blank. Maybe I'm not looking for the value correctly, just right clicking in design space in VS and choosing Variables to see the value of TestVar, always blank.

I think the problem is the way you are looking for the value. You may want to set a breakpoint and execute the whole package; then at the break point time, examinate the varibale by looking at the output window.

cboom wrote:

I did try setting the result set to Full Result Set, variable to Object, and the Result Name to 0, and got a very helpful value of System.Object. Maybe that's what I have to work with, but I just have a single value to retrieve that should work with SingleRow. I've also tried different naming conventions and making sure Result Name is same case as the column name.

You don't need to use a Object type variable...

|||

Thanks Rafael, you were right, just needed to find a new place to check the value of the variable, so played more with variables in Script Tasks and Componenets and got quite a bit more figured out. Had to learn about specifying the ReadOnlyVariables and ReadWriteVariables for the scripts.

Thanks, appreciate you pointing me in the right direction.

Chera