Writing the Execute SQL Task Output to package Variable

Question

Post reply

Writing the Execute SQL Task Output to package Variable

Turtle2008

SSC-Addicted

Points: 414
More actions
August 26, 2008 at 10:15 am

#123200

Hi All,
I'm using "Execute SQL Task" for the query "SELECT Count(*) FROM Table A". On the resultset properties, my result name is 0 and mapped to my package variable "Count" of int32 type. Task run successfully but could not write the output to the variable "Count".
I'm not sure about the following method ( I found it on the different post but nobody responded)
I created one more variable "Count1" and on the expression added the line @[User::Count] > 199? @[User::Count] : 199. My row count is greater than 199, but still the value of "Count1" variable has not changed.
BTW- Package execute successfully.
Thanks in Advance.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 1

TRY this

"SELECT Count(*) As Count FROM Table A".

Then on the ResultSet , Type Count in the value and map to your Varaiable.

This should work.

thanks

glaudiej Mr or Mrs. 500 Points: 534 More actions · Answer 2

hi,

i'm having the exact same issue, only for a input variable though. i'm trying to populate my variable in a sql task in order to give my etl flow a starting point. I have used the column name that my result set returns, as the name of the result set, and chose single row, as it returns only one row. Still the variable remains blank at execution.

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 3

Try as I said above. yeha I alwsys found issue with this.

SO what I do is always alias the column name .....Now Upper and lower case also makes difference...

Here do this.....Lets say your variable name is @MyCount

Then

SELECT ISNULL(COUNT(*), 0) as MyCount from Table .........

and in the resultset map the vraibale to MyCount ( make sure you type exactly what your alias is..so in this case MyCount)

glaudiej Mr or Mrs. 500 Points: 534 More actions · Answer 4

h there, i'm not sure what i'm missing, but i think i've done exactly what you said, and still it doesn't work.

1.

glaudiej Mr or Mrs. 500 Points: 534 More actions · Answer 5

sorry, pressend enter by mistake.

1. Defined variable Name = LastInvoiceA

Scope = package

Type = string

value = i left this blank, because i want to set it with script

2. in a execute sql task on the genera tab i chose single row result set, OLEDB provider and my connection i've created. here is my query: select isnull(LastInvoice,0) as LastInvoiceA

from admin_SalesFact

where Co = 'A'. the result for this in SQL returns: 561196 which is correct.

3. on the parameter tab my variable name is User::LastInvoiceA, direction = input, datatype = varchar, parameter name = 0(read that OLEDB connections expect either 0,1,etc) and size = 10, which is the size of my field in the table admin_SalesFact.

4. on the result set tab, i mapped ResultSet Name = LastInvoiceA to variable name = User::LastInvoiceA.

5. i rcreate a breakpoint on the sql task and run the package. Under locals , variable my variable User::LastInvoiceA = {}

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 6

first don't leave the value empty. becoz pkg will fail to validate if u leave empty. put anyvalue, becoz this value is changed during the execution.

Second why string? if its a count why not int or even bigint?....

U don't have to do anything on paramater tab..........leave it empty...........just map it to the resultset tab only the way i have said

put value to something.............this will work..........

and also if ur using as string, then you might to do as Acst in your script.....but thats on you.

glaudiej Mr or Mrs. 500 Points: 534 More actions · Answer 7

this is SO frustrating. I must be stupid for not getting this right.

I need to use a string, because it's an invoice, when the value = 000001 i need te 0's to be present, and some invoices could contain non numerics.

i gave the variable initial value of 666666 and used cast (varchar(10)) in my script - NO LUCK

then i added new variable (newtest) - int32 initial value = 160.

Selected a straight value in the scrip = select cast(100 as int) as newtest.

linked the resultset newtest to variable newtest - at runtime the variable's value is 160. so it takes the initial value, but never overwrites it at execution.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

Don't set anything at the Parameter Mapping. This is intended for when you use variables in your SQL Statement.

Just set the resultset at 'Single Row' in the general tab, then go to the Result Set tab. Click the button add. As Result Name, take the name of the column you want or simply put 0. At the Variable Name, well, choose your variable :).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

glaudiej Mr or Mrs. 500 Points: 534 More actions · Answer 9

as you can see from my previous post, I think I have done exactly that. That's what is so frustrating. I've read and research all the possible info there is on how to set up variables and parameters ( I will need this - this is where I’m ultimately heading – wanting to use the variable that was populated by the script – as a parameter to my OLEDB source connection ), but for some reason i cannot get it right.

I've just battled with it for so long, that I’m very frustrated; perhaps that's why I sound serious.

David Data SSCrazy Points: 2965 More actions · Answer 10

Have a look at this, I think it will answer your questions:

http://blogs.msdn.com/b/mattm/archive/2006/11/08/stored-procedures-with-output-parameters.aspx[\url]

nick.mcdermaid Hall of Fame Points: 3809 More actions · Answer 11

I'm pretty sure that the variable value is reset once the package finishes running. So unless you are doing something with the variable during runtime to validate it (like showing it in a messagebox or inserting it another table) then it will look like nothing has changed.

So can you clarify how you are verifying that the variable is not populated?

glaudiej Mr or Mrs. 500 Points: 534 More actions · Answer 12

hi there,

I was using the locals window which lists all the runtime values when you view them at breakpoint execution.

Hoever, in all my frustration because i thought i'm following the process 100% to get this set up, i decided to start a brand new package and declare the variables from scratch. And WHAT DO YOU know, it worked. So i suppose it was again one of those freaky situations where you cannot find the error and when you re-do it , there is no error.

So eventually i got it figured out to the point where my OLEDB source can now select data from the source system, using the variable in the where clause as the starting point. And my entire fact table load completed successfully

WHOOOOO hhoooo

Glaudie

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 13

Well, I'm glad it worked out in the end 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

marcusq71 SSC Rookie Points: 42 More actions · Answer 14

I am having a similar problem. Were you ever able to determine what was causing the variable not to populate?