Post reply

How to Assign SSIS User Variable (DOUBLE) from Execute SQL Task

  • July 27, 2011 at 8:07 am

    #240594

    I'm fairly new to SSIS so I'm a bit stumped on how to get this conversion to go through.

    I have, what appears like it should be a simple task - Populate a user variable using the ResultSet from an Execute SQL task.

    SELECT MainID FROM TableA WHERE TableA_ID = 1 (This will only return one Column and one Row).

    MainID is currently stored as a user-defined datatype in the database. The value is stored in the format XXXX.Y (e.g. 8008.1).

    However, no matter what I try and CONVERT/CAST the returning column to, I can't assign the value if the User Variable Datatype is DOUBLE. I get the following error message:

    [Execute SQL Task] Error: An error occurred while assigning a value to variable "dUserVariable": "The type of the value being assigned to variable "User::dUserVariable" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".

    Just to verify that it is a datatype issue, I tested this using a UserVariable which was a String datatype and it works fine. Unfortunately, this user variable comes from a 3rd party and in order to get it to work, it has to be a DOUBLE.

    Any ideas

  • July 27, 2011 at 8:41 am

    #1359316

    Is it a CLR user-defined type (UDT), or a T-SQL UDT?

    Try capturing the value as an SSIS double with this as your Execute SQL Task source:

    SELECT CAST(MainID AS FLOAT) AS MainID

    FROM TableA

    WHERE TableA_ID = 1

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • July 27, 2011 at 9:17 am

    #1359363

    Unfortunately, I have tried that and get the same results.

    I have tried converting and casting to NUMERIC, FLOAT and STRING values pulling back from the SQL Server.

    When I attempt to update the UserVariable, it fails with the error noted.

    Also - it is a TSQL UDT.

  • July 27, 2011 at 9:32 am

    #1359390

    What did you mean by this?

    Unfortunately, this user variable comes from a 3rd party and in order to get it to work, it has to be a DOUBLE.

    Can you post the package as an attachment to this thread?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • July 27, 2011 at 9:38 am

    #1359401

    What I meant by that statement, is that the UserVariable is used by a Web Service and it requires the variable to be a DOUBLE datatype. Thus, I don't have the option of changing the datatype to something else, like STRING.

    I have verified that if I change the datatype to a STRING I can populate it using the EXECUTE SQL Task. But then the Web Service Task fails.

    Also, I won't be able to post the package up to this site. Not all if it is my code.

  • July 27, 2011 at 9:44 am

    #1359416

    Understood. What is the base type of the T-SQL UDT?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • July 27, 2011 at 9:45 am

    #1359418

    Another option is to capture the result into a string in SSIS and then setup User::dUserVariable as an expression that will convert your string variable to a double.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • July 27, 2011 at 9:46 am

    #1359419

    The base is numeric(12,1)

  • July 27, 2011 at 9:47 am

    #1359421

    That sounds like a reasonable workaround.

    I haven't done much with Expressions, any hints on how something like that may look?

  • July 27, 2011 at 10:04 am

    #1359444

    Have a look at the attachment. It contains two packages.

    capture_float_as_double.dtsx:

    Shows how to do the type conversion in SQL on the way out. I am stumped as to why this technique does not work for you. It should. FLOAT maps to DT_8 (i.e. double) in SSIS.

    capture_float_as_string_and_convert_to_double.dtsx:

    Shows how to capture the resultset to a string variable in SSIS and then have the double variable pickup it's value from the string variable using an Expression. Look into the properties of dUserVariable.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • July 27, 2011 at 3:13 pm

    #1359703

    Problem solved.

    I created a new variable which was a String Datatype (sUserVar)

    I loaded the ResultSet into the variable (sUserVar)

    I then modified the DOUBLE variable to use an Expression:

    (DT_R8) @[User::sUserVar].

    At this point both the conversion completed and the Web Service ran without issue.

    Thanks again for helping steer me in the correct direction.

  • July 27, 2011 at 3:15 pm

    #1359707

    Anytime, HTH 🙂

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 12 posts - 1 through 12 (of 12 total)

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