Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Writing the Execute SQL Task Output to package Variable


Writing the Execute SQL Task Output to package Variable

Author
Message
Turtle2008
Turtle2008
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 68
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.
SSIS\SSRS\SSAS
SSIS\SSRS\SSAS
Right there with Babe
Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)

Group: General Forum Members
Points: 753 Visits: 513
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
glaudiej
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 43
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
SSIS\SSRS\SSAS
Right there with Babe
Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)

Group: General Forum Members
Points: 753 Visits: 513
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
glaudiej
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 43
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
glaudiej
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 43
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
SSIS\SSRS\SSAS
Right there with Babe
Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)

Group: General Forum Members
Points: 753 Visits: 513
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
glaudiej
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 43
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
Koen Verbeeck
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19399 Visits: 13250
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 Smile.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
glaudiej
glaudiej
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 43
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search