Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Writing the Execute SQL Task Output to package Variable Expand / Collapse
Author
Message
Posted Tuesday, August 26, 2008 10:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 04, 2012 11:28 AM
Points: 14, 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.

Post #559028
Posted Tuesday, August 26, 2008 11:21 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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
Post #559067
Posted Thursday, June 24, 2010 9:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 23, 2012 10:47 AM
Points: 8, 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.
Post #942539
Posted Thursday, June 24, 2010 9:44 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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)
Post #942542
Posted Thursday, June 24, 2010 10:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 23, 2012 10:47 AM
Points: 8, 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.
Post #942584
Posted Thursday, June 24, 2010 11:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 23, 2012 10:47 AM
Points: 8, 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 = {}
Post #942593
Posted Thursday, June 24, 2010 1:20 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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.
Post #942661
Posted Friday, June 25, 2010 2:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 23, 2012 10:47 AM
Points: 8, 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.
Post #942871
Posted Friday, June 25, 2010 5:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:35 AM
Points: 12,208, Visits: 9,172
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 :).




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

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #942976
Posted Friday, June 25, 2010 6:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 23, 2012 10:47 AM
Points: 8, 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.
Post #942987
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse