I have a package that I developed in SSIS 2005 and recently ported to 2008. Everything runs great except an OLE DB Command component that calls a stored procedure that uses output parameters. When I try to run this, I get this error: Error: 0xC0202009 at Data Flow Task, OLE DB Command [100]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error". Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: component "OLE DB Command" (100) failed the pre-execute phase and returned error code 0xC0202009. When I do the same thing in SSIS2005, it works fine. So I setup a new package with 1 data flow. In that data flow, I added a source component, an OLE DB Command, and a destination. I then tested this with a stored procedure that took no parameters. It worked fine. I then tested it with a stored procedure with 1 parameter defined as OUTPUT. This generated the error. Is there an issue with output parameters in an OLE DB Command in SSIS 2008? Thanks!http://bobp1339.blogspot.com

Im not sure if this worked in 2005 but ideally OLEDB Command is not meant for such an operation. As the output parameter would not be used any where in the Data Flow. And do remember that the OLEDB Component will be executed per record in your DFT.

Hi BobP, There must be an issue with the way the output parameter is set (hence the syntax error is). Please follow this article to make sure that is done in accordance to what is in it: http://technet.microsoft.com/en-us/library/ms141773.aspxArthur

Actually, in 2005, it can be used in the data flow by adding derived columns and mapping the output parameters to the derived columns. And I need it executed once per record. That is the desired behavior. BobPhttp://bobp1339.blogspot.com

It is setup correctly. I have setup one in 2008 and one in 2005. They are exactly the same. The 2005 works, 2008 does not. BobPhttp://bobp1339.blogspot.com

I am curious if you could show the technical aspects too, including the mapping?Arthur

Stored Proc: exec smpTest @Var=? OUTPUT (this is a varchar(10) parameter) Input Column: Status varchar(10) from an OLEDB Input Component They are mapped in the OLD DB Command. Both columns appear in the Input/Output tab. @Var in the External Columns folder and Status in the Input Columns folder. Again, I can set this up in a few mouse clicks in SSIS 2005. When I repeat the exact same steps in SSIS 2008, this fails. It will not validate prior to running. Thanks BobPhttp://bobp1339.blogspot.com

Can you try coding that as follows: EXEC ? = smpTest ?, ? OUTPUT Arthur

No, that does not work. I have changed how I am doing this to do it all in a stored procedure, since this does not seem to be working in 2008. I will continue to work on this when I have time, as I would to get it working again. BobPhttp://bobp1339.blogspot.com

What does not work? And one more question: your OLE DB, what provider does it use?Arthur http://geekswithblogs.net/Compudicted

Can you try coding that as follows: EXEC ? = smpTest ?, ? OUTPUT Arthur That is what does not work. I am using SQLNCLI10.1. BobPhttp://bobp1339.blogspot.com

So, you have tried to map your parameters in the SQL task using EXEC ? = smpTest ?, ? OUTPUT and did you get an error?Arthur http://www.geekswithblogs.net/Compudicted

Could you please share with us ur requirement as to why you want the OLEDB Command to return the value? Where would you use this value??

Mapping is not an issue. I mapped the parameters and the return value fine. But when I try to execute, I get the validation error posted in the first post. But I only get it in SSIS 2008. If I do the exact same steps in SSIS 2005sp3, it works exactly as planned. BobPhttp://bobp1339.blogspot.com

Same thing here. Any resolution yet?

So you are having the same issue? Cool... I thought I might have been going crazy. No, I did not find a resolution. What I did was create a new stored procedure that did all of the work that I was doing in SSIS and called it. That way I didn't have to use output parms. I am going to get around to opening a Connect case for it soon. BobPhttp://bobp1339.blogspot.com

I guess I have the same / a similar problem. I found your post searching the error message 0x80004005 in Google. I'm trying to get the ResultValue from a Stored Procedure Call without Parameters: EXEC ? = smpTest Provider is Microsoft SQL Server Native Client 10.0 / OLEDB. Server is SQL Server 2008 SP1 - 10.0.2531.0 Clicking on "Parse Query" gives me the same error code. Best, peter

I have same problem with SQL 2008 R2 . Has anyone found a workaround for this problem. Looks like stored proc in OLE DB Component / Source cannot be parameterized.

Me too ... I have a simple data flow: An OLE DB Source -> OLE DB Command 1 -> OLE DB Command 2 Both the Commands executes stored procedures. Command 1 does not work ... Command 2 works fine ! The command 1 executes SP: EXECUTE SYSpEmpAddUpdate ?,?,?,?,?,?,?,?,?,?,?,?,?,?,? Command 2 executes SP: EXEC SYSpUserCreate ? ,? The command 2 reads the meta data fine ... command 1 doesn't :( The only difference is, that SP 1 takes a DATETIME as paramter .. If I remove that param, everything works fine ... must be something with codepage/collation!!

I have just tried to reproduce this issue, but all worked for me. I cannot imagine there is a large number of more users out there with this often-used feature not working and they are not complaining. Is that possible that you have a missing service pack or some other updates? Could run an update to see what is missing from this prospective?Arthur My Blog

Make sure your provider is compatible with the version of SQL Server you are connecting too...thus check the type of SQL Server Client version 10.0 or 10.1 Did you try this in a completely new SSIS 2008 Bids solution or did you add a new dtsx to the converted SSIS 2005 solution? If all else fails then you can build your SQL statement in a script and output that as a variable then pass that to your OLE DB SQLSourceType as Variable...which I have done in some cases.

This is a thread from almost a year and a half ago.Chuck