Error using an Execute SQL script to load a date variable in SSIS

Question

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:

enter image description here

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!

• Jon of All Trades: I sympathize, working with variables in SSIS is a massive PITA. Can you pull the date value into a text variable instead, and work with it from there?
• Kenneth Fisher: If I have to I will. However I would rather not go through the extra step.

Answer 1

Try mapping your result set, not parameters. The setting is similar; the Result Names are 0 and 1 and the ? = are not necessary in your select statement.

SELECT TOP 1 Id, BuildDate
FROM BuildControl
WHERE BuildStatus IS NULL
ORDER BY Id

Also, make sure your Result Set is set to Single row.

• Kenneth Fisher: I'll give it a shot. I've done result sets before but not single row ones. I'm still curious why the other method is failing though.
• SQLFox: Looking at your settings again, I see what you were doing--I missed the output direction setting. I can't say why that shouldn't work, other than that SSIS SQL task parameters are flaky as heck. Possibly this?
• Kenneth Fisher: I noticed a few things saying that changing the OUTPUT setting fixed this particular problem, unfortunately I need the setting that way so that I can get the value out of the query, and not pass one in. I'm going to try the result set idea in the morning.
• Marian: @SQLFox That's for you, thanks!