Microsoft BI SSIS Series-variable Int64 cannot save bigint type data in version 2008

Introduction to the outset

This is a bug that was found today when looking for a 2008R2 version of the project, which may have been resolved in some versions of SQL SERVER 2012, but it still exists on the forums.

In SQL SERVER version R2, such as taking a BIGINT type of data from a database table, assigning a variable to a Int64 type is certainly correct in our understanding.

But the result is an error-

Cause of error: inconsistent type.

[Execute SQL Task] Error:an error occurred while assigning a value to variable "max_id": "The type of the value being assigned to variable" USER::MAX_ID "differs from the current variable type. Variables is not a change type during execution. Variable types is strict, except for variables of type Object.

".

It was also very strange, that there is no problem with the configuration here, the suspicion of the colleague machine environment problems, so in my native SQL SERVER 2012 environment test.

The same logic is passed.

Later to the online check, sure enough is a SQL SERVER version of R2 a bug, starting from 2005, very ashamed, I only now found that this is a bug.

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

Why can ' t I store my BIGINT result in an Int64 variable?

The Native providers (OLE DB, ODBC, ado–ado.net doesn ' t has this problem) in the Execute SQL Task return the BIGINT type As a String, and not the Int64 as you ' d expect. Attempting to store the result of a Int64 variable gives you error along the lines of:

Although this behavior are documented in the Books Online entry, we don ' t explain what it was doing this to. Being somewhat new to the SSIS development team, I had to doing some digging to find out for myself. It turns out of that on the time this is implemented, there wasn ' t cross platform support for the 8-byte integer type (vt_i8 /VT_UI8) in variants--specifically, on Windows 2000. Now this supporting Win2K is no longer a issue (for Katmai), we ' re free to change the behavior (while maintaining Backwar DS Compatibility for packages that is expecting the value as a string, of course).

Expect this as a likely a upcoming release.

Https://connect.microsoft.com/SQLServer/feedback/details/260967/ssis-sql-server-bigint-doesnt-map-to-int64

How to solve

The version of SQL Server 2012 has been corrected, so there should be no problem with SQL Server 2012. If you still use SQL Server R2, the workaround is to change the data type to String, which is used to accept data from the BIGINT data type in SQL Server.

In addition, you may be asked-how does this variable exactly write to a data table of type column BIGINT?

This is not a problem, if the type of COLUMN is numeric, date, the string type will automatically convert the insert, so the variable of string will be automatically converted to the corresponding BIGINT inserted into the data table.

For more bi articles, see the Bi-series essay list (SSIS, SSRS, SSAS, MDX, SQL Server) If you feel this article has helped you, please help recommend it to make it easy for others to see these articles quickly in the Biwork blog recommendations Bar.

Microsoft BI SSIS Series-variable Int64 cannot save bigint type data in version 2008