How to retrieve a bigint from a database and place it into an Int64 in SSIS

Question

I ran into this problem a couple years back and am hoping there has been a fix and I just don't know about it. I am using an 'Execute SQL Task' in the Control Flow of an SSIS package to retrieve a 'bigint' ID value. The task is supposed to place this in an Int64 SSIS variable but I getting the error: "The type of the value being assigned to variable "User::AuditID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object."

When I brought this to MS' attention a couple years back they stated that I had to 'work around' this by placing the bigint into an SSIS object variable and then converting the value to Int64 as needed. Does anyone know if this has been fixed or do I still have to 'work around' this mess?

---

edit:

Server Stats

• Product: Microsoft SQL Server Enterprise Edition
• Operating System: Microsoft Windows NT 5.2 (3790)
• Platform: NT INTEL X86
• Version: 9.00.1399.06

Answer 1

I was just able to pull in a BIGINT into an Int64 without a problem using an ExecuteSQL task. If your result set is a single row, make sure that you set it as such in your ExecuteSQL task. If your result set is multiple rows then you need to use an object type variable for the result set and then you would set the variable within your looping structure. I don't think this is a "workaround" because a set of BIGINT values is different than a BIGINT.

I'm on SQL 2008, but SSIS should handle things (mostly) the same regardless of the source data.

Answer 2

You need to read the BIGINT value into a string variable - not an Object variable: Why can’t I store my BIGINT result in an Int64 variable?