How to map a decimal data type to an SSIS variable

Just in case you have to.

Some Einstein on another team decided to switch all of his surrogate IDs from integer to DECIMAL (38,0) in his table.  Brilliant.  Wait what?  Why on earth someone would do this I have no idea. Maybe we all need to find out what he/she is smoking and get some. I cannot think of a reason why you need a 17-byte number for some ID.  In case you run into this brilliance, here’s what to do.  I had an idea but never tested it, and fortunately it worked.

Remember that SSIS variables don’t have an option for decimal or numeric, and all of the other number types are too small, especially for this bad boy.  Create a couple of variables and drop down the “Data Type” to verify:

You can see a partial listing of variables – no decimal in sight. Fortunately we do have ‘String’, so that’s what we’re going to try and use. Let’s set up the scenario. First, I run the following to create a table and insert a few rows of data.

BEGIN TRY
    DROP TABLE dbo.TestDecimal
END TRY

BEGIN CATCH
END CATCH

CREATE TABLE dbo.TestDecimal
    (ID decimal (38,0)
    ,value char (10)
    )
INSERT INTO dbo.TestDecimal VALUES (9999999999999999999999999999999999999,'A')
INSERT INTO dbo.TestDecimal VALUES (9999999999999999999999999999999999998,'A')
INSERT INTO dbo.TestDecimal VALUES (9999999999999999999999999999999999997,'A')
GO
 
SELECT * FROM dbo.TestDecimal

Click on the above picture. I have created two variables with types of object and string, an EXEC SQL Task, and connected it to a ForEach container with a script task inside.  Here are some screenshots of these objects and the settings.  Notice that I cast the ID to a varchar, and this value is what will go into the LIST variable of type Object. From there, the ForEach iterate over this variable and place the string value one at a time into the tempVar variable (of type String).

I won’t past all three rows sent to a message box, so here’s first.

Bottom line, if you have some Einstein making decimal (38,0) for a surrogate key,  or any other decimal value that you need to map to an SSIS variable, map it to a string.  And you should go ahead and cast it to a char, varchar, or maybe nvarchar before attempting to assign it to the variable.

Happy decimal mapping!

Lee

-----------------------------

Maybe we tell the dude that the cool new thing is to make every column nvarchar (255).