OLE DB Command transform and Output columns.

Hi All,

I have an OLE DB transform with a SQL Command of:

sp_get_sponsor_parent ?,? OUTPUT

where sp_get_sponsor_parent is defined like:

CREATE PROCEDURE [dbo].[sp_get_sponsor_parent]

@.pEID int,

@.results int OUTPUT

AS

BEGIN

.

.

.

END

I map the columns, refresh & OK out of the component without trouble, but on executing the package it fails during validation on this component. I'm utterly stumped.

Any light shed would be greatly appreciated.

Many thanks in advance,

Tamim.

I don't see any 'EXEC' in your sql commnad...may this be the problem?|||

This wasn't the problem Rafael - the EXEC is optional.

I resolved the issue however, by trialing just this one thing - outputting to a (derived) column within OLE DB Command - in a new/clean package. To that end I would like to bring the following example to everyone's attention: it's concise, comprehensive and clear, and thus can be considered canonical. No doubt there are other such examples out there, but this particular one helped me to push forwards, and thus deserves the publicity:

http://wiki.sqlis.com/default.aspx/SQLISWiki/OLEDBCommandTransformationAndIdentityColumns.html?diff=y

Thanks very much for your input Rafael.

Cheers,

Tamim.