Returning numeric value in Execute SQL task

Yesterday night a question on MSDN fourm drew my attention towards a wired but ture fact that SSIS does not accept numeric value as double in Execute SQL task.

So how do we use our numeric value in Execute SQL Task

SSIS considers the numeric values as string when they are returned from Execute SQL task, so to use that value we have to follow two steps

1) Map the value to a String or Object datatype variable
2) Convert this value to double and store in other variable

Let me take an example to show how it works

I will consider below table for the example and extract maximum value of Num column
CREATE TABLE tblAbc
(
[Id] INT IDENTITY (1,1),
[Num] NUMERIC(10,4)
)
GO

INSERT INTO tblAbc VALUES (123.23)
INSERT INTO tblAbc VALUES (555.23)
GO
--query we will use to extract max of Num
SELECT Max(Num)as Num FROM tblAbc

Now its implementation
A) Declare two variables 1) objNum as object and 2) dblNum as double datatype



B) Use Execute SQL task to extract numeric value and map it to objNum variable


map result to objNum variable


C) Use script task to convert objNum value into double and assign it to dblNum variable
a) specify
Readonlyvariable as ObjNum
Readwritevariable as dblNum

b) Write below code as script

Dts.Variables("dblNum").Value = CDbl(Dts.Variables("objNum").Value
MsgBox(Dts.Variables("dblNum").Value, , "dblNum")

D) Excute the package


Now we have dblNum having our numeric value and can use it for any calculation purpose