I'm trying to populate a variable using an Execute SQL task in SSIS. The table structure is as follows:
CREATE TABLE BuildControl
(Id INT NOT NULL Identity, BuildDate Date,
Country char(2), BuildStatus varchar(20),
BuildStatusDate DateTime)
The SQL Statement is
SELECT TOP 1 ? = Id, ? = BuildDate
FROM BuildControl
WHERE BuildStatus IS NULL
ORDER BY Id
and it works fine if I remove the ? = BuildDate
. The parameter mapping tab looks like this:
The error I'm recieving is this:
SSIS package "WFG Statement Build.dtsx" starting.
Error: 0xC002F210 at Get Build Data, Execute SQL Task: Executing the query "SELECT TOP 1 ? = Id, ? = BuildDate
FROM BuildContr..." failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Get Build Data
Warning: 0x80019002 at WFG Statement Build: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "WFG Statement Build.dtsx" finished: Failure.
I believe the problem is with the date output variable since when I remove it I don't have a problem. Both the server and the version of SSIS I'm using are SQL Server 2008 R2. The Connection Manager is an OLE DB connection using the Native OLE DB\SQL Server Native Client 10.0
provider. I've tried changing the table structure to use a DateTime with no success, and tried DBDate and DBDateTime as Data Types in the Parameter Mapping page also with no success.
I'm completely baffled. Thanks for any help!