Post reply

[Execute SQL Task] Error: An error occurred while assigning a value to variable "SqlVersion": "Unsupported data type on result set binding SqlVersionFromQuery.".

  • March 3, 2009 at 9:46 am

    #207076

    I have an SSIS package level variable User::SqlVersion String

    I have an execute sql task

    SET NOCOUNT ON

    SELECT SERVERPROPERTY('productversion')

    as SqlVersionFromQuery

    I have the ResultSet = Single_Row and the variable above as the result set with a name of 0

    I have the forceExecutionValue type = String and forceexecutionvalue=True and I'm getting the error:

    Unsupported data typ eon result set binding SqlVersionFromQuery

    Any ideas on what I may be doing wrong? The only way I've been able to set any variables is with variable type of Object and then using a for each loop to shred it - but when I only want a single value it just seems like a waste to do that.

  • March 4, 2009 at 6:59 am

    #953131

    Based on my understanding of SSIS, you would need to use an Object Data Type as you are returning a ROW not a value.

    You can do this (I tested it):

      1. Create a variable to hold the version

      2. In the Execute SQL Task set a parameter using the variable created in step 1 with it's direction as Output and set to map to parameter 0

      3. Set the ResultSet property to None.

      4. Set the SQL Statement to:

      Select ? = CONVERT(NVARCHAR(20), SERVERPROPERTY('productversion'))

    You may not need the convert, but ServerProperty returns a SQL_VARIANT and when run in SSMS you need the CONVERT to assign it to an NVARCHAR data type.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

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

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