How do I assign numeric value of an SQL query result to a package variable of data type Double?

Question

I have what 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).

If I change the UserVariable to a String datatype, I can assign the value without any problems either CASTing or CONVERTing the ResultSet to STRING/NUMERIC/FLOAT.

SELECT CAST(MainID as NUMERIC(9,1)) as 'MainID' FROM TableA WHERE TableA_ID = 1;
SELECT CAST(MainID as FLOAT) as 'MainID' FROM TableA WHERE TableA_ID = 1;
SELECT CAST(MainID as VARCHAR(10)) as 'MainID' FROM TableA WHERE TableA_ID = 1;

or

SELECT CONVERT(NUMERIC(9,1), MainID) as 'MainID' FROM TableA WHERE TableA_ID = 1;
SELECT CONVERT(FLOAT, MainID) as 'MainID' FROM TableA WHERE TableA_ID = 1;
SELECT CONVERT(VARCHAR(10), MainID) as 'MainID' FROM TableA WHERE TableA_ID = 1;

However, 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. ".

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

Any ideas?

Answer 1

I am not sure if the following option will work in Web Service Task but you can give it a try.

I think that the issue you have described is due to a limitation in SSIS while assigning numeric value to an SSIS package variable of data type Double. Refer this link to MSDN Connect where this issue has been reported.

Here is a simple step-by-step example that illustrates an option that you can try. This example was created in SSIS 2008 R2.

Create an SSIS package. I have named the package in the format YYYYMMDD_hhmm in the beginning followed by SO stands for Stack Overflow, followed by the SO question id, and finally a description. This is for me to easily refer this back later. Refer screenshot #1.

On the package, create two variables namely StringValue and DoubleValue. Set the variable StringValue to numeric value (say 0). This is necessary so that the expression we are going to configure in the variable DoubleValue doesn't error out. Select the variable DouleValue and press F4 to view the properties. Set the property EvaluateAsExpression to True and set the Expression property to (DT_DECIMAL, 2) @[User::StringValue] NOTE: At this point the Data Type on the variable DoubleValue will change to Cell is not a string. Refer screenshots #2 and #3.

On the package, create an OLE DB connection manager to some SQL Server database. I have created a connection to Adventure Works database that I have on my local machine. Refer screenshot #4.

On the Control Flow tab, place an Execute SQL Task and configure it as shown in screenshots #5 and #6. Double-click on the Execute SQL Task to bring the Execute SQL Task Editor. Set the ResultSet to SingleRow because we will be fetching only one value. Set the connection string to the OLE DB connection manager. The SQL Statement should be set to SELECT CAST('3.14' AS NUMERIC(10,2)) AS ValueOfPi. Result Set should be set to the variable StringValue.

On the Control Flow tab, place a Script Task after the Execute SQL Task. This script task is just to show the value that will be assigned to the DoubleValue variable due to the expression that we just configured. Double-click on the Script Task to bring the Script Task Editor. Click on the Edit Script... button to bring the VSTA editor. Replace the Main() method with code given under Script task code section. Refer screenshots #7 and #8.

Execute the package. The value in the variable DoubleValue should be displayed in a message box. Refer screenshot #9.

Hope that helps.

Script task code:

C# code that can be used only in SSIS 2008 and above.

public void Main()
{
    Variables varCollection = null;
    Dts.VariableDispenser.LockForWrite("User::DoubleValue");
    Dts.VariableDispenser.GetVariables(ref varCollection);

    MessageBox.Show(varCollection["User::DoubleValue"].Value.ToString());

    Dts.TaskResult = (int)ScriptResults.Success;
}

Screenshots:

#1:

#2:

#3:

#4:

#5:

#6:

#7:

#8:

#9: