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.