OLE DB Source Error Output

In the Input and Output Properties tab under Advance Editor for OLE DB Source, I cannot remove columns. I copied this Source from a standard template and have made the normal changes to make it work. However I keep getting this error...

Error: 0xC020837B at Load Server Security, OLE DB Source [1]: The output column "DBName" (1632) on the error output has no corresponding output column on the non-error output.

Error: 0xC004706B at Load Server Security, DTS.Pipeline: "component "OLE DB Source" (1)" failed validation and returned validation status "VS_ISBROKEN".

DBName of course is one of the columns that no longer exist, but I can't remove. Whenever I try to remove one of the columns, I get this error...

Error at Load Server Security [OLE DB Source[1]]: The column cannot be deleted. The component does not allow columns to be deleted from this input or output. Is there anything that I can do to remove the columns? Is there just a simple setting that I can change to make this work?
If you just double click on the OLE DB source, you can choose "Columns" on the left hand side and unselect the columns you do not want in the data flow.|||There is a problem with that though because I can't make any changes within the source. I always get the error...

Invalid object name 'tempdb.dbo.Server_Roles. This is because the table isn't created yet. It gets created when the connection manager connects to each server. It is a temporary table. Thus, it won't let me make any changes that I can't make in advance editor.

-Kyle
|||

Why don't you create the table somewhere else, get it configured properly, then change the table name afterward?

|||

That doesn't work. Currently I created a table in the temp directory and pointed the connection manager towards that DB and server. I still can't click on column and I can't change anything unless I hit cancel. My guess is that it may not be connecting to the server and DB correctly. I don't get any explanation for the error, its just one of the H errors.

-Kyle

|||I guess I don't understand what you are trying to do. Please explain further with more detail.

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.