HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
scripterrorsqlssisdateusingloadvariableexecute

Problem

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!

Solution

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.

Code Snippets

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

Context

StackExchange Database Administrators Q#45385, answer score: 5

Revisions (0)

No revisions yet.