Writing the Execute SQL Task Output to package Variable

  • 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.

  • 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

  • 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.

  • 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)

  • 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.

  • 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 = {}

  • 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.

  • 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.

  • 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

  • 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.

  • 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]

  • 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?

  • 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

  • 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

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

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

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