SSIS 执行 SQL 任务错误:多步 OLE DB 操作生成错误——可能是由于参数映射 [英] SSIS Execute SQL Task error: Multiple-step OLE DB operation generated errors -- probably due to parameter mapping

问题描述

我在 SQL Server 2008 和 BIDS (SSIS) 中工作.我正在尝试为执行包时生成加载 ID"并将该 ID 存储在加载历史记录表中(然后填充后续表).

I am working in SQL Server 2008 and BIDS (SSIS). I am trying to generate a "load ID" for when a package is executed and store that ID in a load history table (which then populates subsequent tables).

我的基本 SSIS 控制流程如下:执行SQL任务、数据流任务

My basic SSIS control flow is the following: Execute SQL Task, Data Flow Task

通过以下方式创建负载表:

The load table is created via the following:

CREATE TABLE dbo.LoadHistory
(
    LoadHistoryId int identity(1,1) NOT NULL PRIMARY KEY,
    LoadDate datetime NOT NULL
);

执行SQL任务的编辑器如下:

The editor for the Execute SQL Task is as follows:

一般:

结果集 = 无

ConnectionType = OLE DB

ConnectionType = OLE DB

SQL 语句:

INSERT INTO dbo.LoadHistory (LoadDate) VALUES(@[System::StartTime]);
SELECT ? = SCOPE_IDENTITY()

参数映射:

变量名 = User::LoadID

Variable Name = User::LoadID

方向 = 输出

数据类型 = LONG

Data Type = LONG

参数名称 = 0

参数大小 = -1

SSIS 抛出以下错误:

SSIS is throwing the following error:

[执行 SQL 任务] 错误:执行查询INSERT INTO dbo.LoadHistory"..."失败并出现以下错误:多步 OLE DB 操作生成错误.检查每个 OLE DB 状态值(如果可用).未完成任何工作.".可能的失败原因:查询有问题、ResultSet"属性设置不正确、参数设置不正确或连接未正确建立.

[Execute SQL Task] Error: Executing the query "INSERT INTO dbo.LoadHistory ..." failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

此错误消息并不能真正帮助我找到问题.我最好的猜测是这是由于参数映射，但我没有看到我的错误.有人能指出我的问题并提供修复吗?

This error message doesn't really help me find the problem. My best guess is that it's due to the parameter mapping, but I don't see my mistake. Can anybody point out my problem and provide the fix?

推荐答案

我发现了我的问题.System::StartTime 需要将 DATE 作为其数据类型，而不是 DBTIMESTAMP.

I figured out my problem. System::StartTime needs to have DATE as its data type, not DBTIMESTAMP.

这篇关于SSIS 执行 SQL 任务错误:多步 OLE DB 操作生成错误——可能是由于参数映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！