Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
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? –  Jon of All Trades Jun 27 '13 at 22:05
    
If I have to I will. However I would rather not go through the extra step. –  Kenneth Fisher Jun 27 '13 at 22:12

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
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. –  Kenneth Fisher Jun 27 '13 at 22:56
    
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? –  SQLFox Jun 28 '13 at 3:05
    
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. –  Kenneth Fisher Jun 28 '13 at 3:12
    
That worked. Awesome, thanks! –  Kenneth Fisher Jun 28 '13 at 16:11
    
@SQLFox That's for you, thanks! –  Marian Oct 11 '13 at 6:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.