debugsqlMinor
Error using an Execute SQL script to load a date variable in SSIS
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:
The SQL Statement is
and it works fine if I remove the
The error I'm recieving is this:
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
I'm completely baffled. Thanks for any help!
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 Idand 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
Also, make sure your Result Set is set to
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 IdAlso, 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 IdContext
StackExchange Database Administrators Q#45385, answer score: 5
Revisions (0)
No revisions yet.