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.

OLE DB Command Transform

Hi All ,

I am creating packages from a template package whicg I have built ,I have managed to implement basically everything sucessfully .Setting Properties on all the different tasks ,Connections etc except for the OLE DB Command transform.

I have not been sucessfull in getting to the properties or collections which allows me to do the mapping of the Command to parameters (Command Below),I am aware that the command executes for every row . I really need help with how to now do the mapping between the columns and the Paramaters programmatically in c#

I have set the sql command properties of the OLE DB Command Transform,as below

//Setting Update Comand ComponentProperties

IDTSComponentMetaData90 oledbCMDUpdate = dataflow.ComponentMetaDataCollection[0];

oledbCMDUpdate.Name = "name" ;

oledbCMDUpdate.RuntimeConnectionCollection[0].ConnectionManagerID = pack.Connections[0].ID;

CManagedComponentWrapper instanceCMD = oledbCMDUpdate.Instantiate();

instanceCMD.SetComponentProperty("SqlCommand", GetUpdateSQL(tablename)) ;

The Sql that is returned by the GetUpdateSQL(tablename)) method is below

UPDATE [ADM_AdjustmentAction]
SET AdjustmentActionCode = ?
,AdjustmentActionName = ?
,AdjustmentActionDescription = ?
,AdjustmentActionEFD = ?
,AdjustmentActionETD = ?
,UserID = ?
,ProcessDatetime = ?
,ModuleID = ?
WHERE AdjustmentActionID = ?

Thanks in Advance

Cedric

[Microsoft follow-up] I've trawled thru MSDN to try and find an answer to this problem but it proved fruitless. Can anyone from MSFT help?

-Jamie

|||

1. After setting up the properties, including SqlCommand, you can call ReinitializeMetadata on the Command transform.

2. If the provider can derive parameter info for the command, the transform will create external columns, one for each parameter, on its input. If the provider cannot derive parameter info, you will need to manually add the external columns.

3. Each external column has a custom property, DBParamInfoFlags, which specifies whether the parameter is in, out or in out. The value of that property corresponds to OLE DB's paraminfo flag.

4. You then map input columns to the external columns. This mapping will establish which column in the data flow buffer corresponds to which parameter.

You can look in the advanced UI for the command transform to see how the external and input columns should be set up. You can also see the DBParamInfoFlags in the UI as well.

|||

Great answer as usual Ted. Thanks.

OLE DB Command and Select

I am trying to run a select command on an OLE DB Command transform such as:

Select * from table where id = ?

I have the mapping to the parameter working and the command is working but how to map the select output to columns when you can't create output columns on the OLE DB Command?

Is this the correct transform to use?

Thanks

No, it isn't the correct transformation.

You want to use the Lookup transformation instead.

The SQL for the lookup will just be: Select * from table
Then you'll map the incoming column to the id column in the lookup table. (No parameters necessary)

Also, FYI - you'll be better off performing a "select column1, column2, column3, ... from table" than using a "select *" format.|||

thanks for the quick response. I tried that but didn't look far enough into the columns tab to see what it was doing.

Thanks