Blog

March 24, 2015
|

How to loop over a SQL Server BIGINT collection using a SSIS Int64 Variable

When you work with SSIS in a Data Warehouse environment as I do you’ll often need to process data on a daily base. Therefore your loading processes need to be able to loop over a bunch of data from a source system. This could be the case when you do an initial load or simply when a source system had some FTP problems and sends missed files when they see it Monday morning…

 

So far so easy – but I recently tried to involve an Int64 Variable as additional loop value. What I got was a strange error message:

 

 

 

 

After A few type checks I was sure that I’m trying something that should work – but no chance. A quick Bing!!! research led me to these sites:

http://blogs.msdn.com/b/mattm/archive/2007/04/18/why-can-t-i-store-my-bigint-result-in-an-int64-variable.aspx

https://connect.microsoft.com/SQLServer/feedback/details/732413/foreach-ado-enumerator-returns-error-when-bigint-and-int64-are-used

http://stackoverflow.com/questions/2951017/how-to-retrieve-a-bigint-from-a-database-and-place-it-into-an-int64-in-ssis

 

But there was no detailed description how to solve the issue so I decided to write a short blog post on that. Here are the steps to reproduce the issue…

 

First we need some Test data. You could use this script if you want:

 

 

 

 

Then I implemented the most simple setup I could imagine. A simple Execute SQL Task followed by a ForEach Loop:

clip_image002

 

Execute SQL Task

In “Select Records” I did a simple selection. Please notice that you need to switch the ResultSet property to “Full result set”. The listed selection will create a Result Set with 3 rows having 1 BIGINT column.

 

 

 

Ex_OK

 

Then you need to map your Result Set to a single variable “User::RecordsObject” of type “Object”. This is it for the Execute SQL Task.

clip_image002[8]

 

ForEach Loop

The ForEach Loop maps the rows in the first table to the previously allocated Object variable “User::RecordsObject”.

clip_image002[10]

 

In the Variable Mappings tab, use the “Add Variable” function to create a new Int64 Variable called “User::BigIntId”. We allocate the column index “0” to that variable – so in our selection with 1 column it is the first and only value in a row.

clip_image002[14]

clip_image002[12]

 

This is the full setup needed to reproduce the issue. Try it your own, run it, you’ll have the same error as I had.

 

The fix

The fix is relatively easy. You just have to switch from an Int64 variable to a String variable. Not really nice but most of the time you could work without converting it back implicitly.

In the Execute SQL Task, change the SQL statement to select the BIGINT column as VARCHAR(20):

 

 

In the ForEach Loop you could create another variable, “User::BigIntIdString” in my case and declare it as String. If you try to run the Package now it will work without any type conversion issue.

clip_image002[16]

clip_image002[18]

 

If you want to use the string as a Parameter for a SQL statement or stored procedure call you could just pass it as Data Type “LONG”. The value will be implicitly converted by the engine. Alternatively you could explicitly convert the value using a Script Task for example. There are a lot of possibilities…

clip_image002[20]

Tags:


1 Comment


Would you like to share your thoughts?

Would you like to share your thoughts?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.