SSIS Execute SQL Task error: Multiple-step OLE DB operation generated errors — probably due to parameter mapping

Question

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).

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
);

The editor for the Execute SQL Task is as follows:

General:

ResultSet = None

ConnectionType = OLE DB

SQLStatement:

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

Parameter Mapping:

Variable Name = User::LoadID

Direction = Output

Data Type = LONG

Parameter Name = 0

Parameter Size = -1

SSIS is throwing the following error:

[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?

Answer 1

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

Answer 2

I was passing three parameters. In the Parameter Name property I had:

0
1
3

Corrected it to:

0
1
2

It works now, no multiple-step operation generated errors message.