SSIS任务中的范围标识错误(Scope Identity error in SSIS Task)

SSIS任务中的范围标识错误(Scope Identity error in SSIS Task) CREATE TABLE SSIS_AuditResults( AuditID INT IDENTITY(1,1), PackageName varchar(35), ExecutionTime datetime, Filename_Path varchar(100), RecordCount integer, EndTime datetime, PackageStatus varchar(10) )

上面是我的表。 我使用下面的查询来执行SQL TASK

INSERT INTO SSIS_AuditResults(PackageName,ExecutionTime) SELECT ? ,? select scope_identity()

在参数I中映射packagename和executuion时间。 在结果映射中，我正在创建一个名为的新变量

Identity_Generator data type int 64,value 0

执行我的包后，我收到以下错误

[执行SQL任务]错误：为变量“Identity_Generator”赋值时发生错误：“分配给变量的值的类型”User :: Identity_Generator“与当前变量类型不同。变量在执行期间可能不会更改类型。变量类型是严格的，除了Object类型的变量。

我正在给INT，但它给我一个错误。 有人可以在我犯错的地方帮助我吗？

CREATE TABLE SSIS_AuditResults( AuditID INT IDENTITY(1,1), PackageName varchar(35), ExecutionTime datetime, Filename_Path varchar(100), RecordCount integer, EndTime datetime, PackageStatus varchar(10) )

Above is my Table. I am using the query below to execute SQL TASK

INSERT INTO SSIS_AuditResults(PackageName,ExecutionTime) SELECT ? ,? select scope_identity()

In parameter I am mapping packagename and executuion time. In result mapping I am creating a new variable with name

Identity_Generator data type int 64,value 0

After executing my package i am getting the error below

[Execute SQL Task] Error: An error occurred while assigning a value to variable "Identity_Generator": "The type of the value being assigned to variable "User::Identity_Generator" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

I am giving INT but it is showing me an error. Can someone help me where I am making a mistake?

最满意答案

SSIS中的数据类型不允许更改，除非它是Object类型。 您的表将AuditID定义为int。 SQL Server int数据类型与SSIS中的Int32数据类型相关联。

您的包将Identity_Generator SSIS变量定义为Int64类型，类似于SQL Server数据类型中的bigint。

要解决此问题，您需要使类型对齐：将SQL Server类型更改为bigint或将Variable更改为Int32。 正确的方法取决于您的环境的具体情况。

Data types in SSIS are not allowed to change, unless it's an Object type. Your table defined AuditID as int. A SQL Server int data type correlates to the Int32 data type in SSIS.

Your package defined the Identity_Generator SSIS Variable as type Int64 which is analogous to bigint in SQL Server data types.

To resolve this, you will need to make your types align: either change your SQL Server types to bigint or change your Variable to Int32. The correct approach will depend upon the specifics of your environment.