Source input column- Empid(int)
Destination column - RowKey(int)
Bellow the my code but that gives the error "Validation error. This is a programmed
DataFlowTask Derived Column [2]: Attempt to find the input column named "Empid" failed with error code 0xC0010009. The input column specified was not found in the input column collection."
My Code is-
TaskHost TKHSQLHost = (TaskHost)exe;
TKHSQLHost.Name = "Dataflow Task";
MainPipe dataFlowTask = (MainPipe)TKHSQLHost.InnerObject;
// Create the source component.
IDTSComponentMetaData100 source =dataFlowTask.ComponentMetaDataCollection.New();
source.ComponentClassID = "DTSAdapter.OleDbSource";
CManagedComponentWrapper srcDesignTime = source.Instantiate();
srcDesignTime.ProvideComponentProperties();
// Assign the connection manager.
if (source.RuntimeConnectionCollection.Count > 0)
{
source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(ConnMgrAdvent);
source.RuntimeConnectionCollection[0].ConnectionManagerID =pkg.Connections["EmpCon"].ID;
}
// Set the custom properties of the source.
srcDesignTime.SetComponentProperty("AccessMode", 0);
srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Employee]");
// Connect to the data source, and then update the metadata for the source.
srcDesignTime.AcquireConnections(null);
srcDesignTime.ReinitializeMetaData();
srcDesignTime.ReleaseConnections();
// Create the destination component.
IDTSComponentMetaData100 destination =dataFlowTask.ComponentMetaDataCollection.New();
destination.ComponentClassID = "DTSAdapter.OleDbDestination";
CManagedComponentWrapper destDesignTime = destination.Instantiate();
destDesignTime.ProvideComponentProperties();
// Assign the connection manager.
destination.RuntimeConnectionCollection[0].ConnectionManager =DtsConvert.GetExtendedInterface(ConnMgrImport_DB);
if (destination.RuntimeConnectionCollection.Count > 0)
{
destination.RuntimeConnectionCollection[0].ConnectionManager =DtsConvert.GetExtendedInterface(ConnMgrImport_DB);
destination.RuntimeConnectionCollection[0].ConnectionManagerID =pkg.Connections["stgEmpCon"].ID;
}
// Set the custom properties of the destination
destDesignTime.SetComponentProperty("AccessMode", 0);
destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[emp12]");
// Connect to the data source, and then update the metadata for the source.
destDesignTime.AcquireConnections(null);
destDesignTime.ReinitializeMetaData();
destDesignTime.ReleaseConnections();
//Derived Column
IDTSComponentMetaData100 derived =dataFlowTask.ComponentMetaDataCollection.New();
derived.Name = "Derived Column Component";
derived.ComponentClassID = "DTSTransform.DerivedColumn.3";
CManagedComponentWrapper DesignDerivedColumns = derived.Instantiate();
DesignDerivedColumns.ProvideComponentProperties(); //design time
derived.InputCollection[0].ExternalMetadataColumnCollection.IsUsed = false;
derived.InputCollection[0].HasSideEffects = false;
//update the metadata for the derived columns
DesignDerivedColumns.AcquireConnections(null);
DesignDerivedColumns.ReinitializeMetaData();
DesignDerivedColumns.ReleaseConnections();
//Create the path from source to derived columns
IDTSPath100 SourceToDerivedPath = dataFlowTask.PathCollection.New();
SourceToDerivedPath.AttachPathAndPropagateNotifications(source.OutputCollection[0], derived.InputCollection[0]);
//Create the path from derived to desitination
IDTSPath100 DerivedToDestinationPath = dataFlowTask.PathCollection.New(); DerivedToDestinationPath.AttachPathAndPropagateNotifications(derived.OutputCollection[0], destination.InputCollection[0]);
**IDTSOutputColumn100 myCol = derived.OutputCollection[0].OutputColumnCollection.New();
myCol.Name = "RowKey"; myCol.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4,0 , 0, 0,0);
myCol.ExternalMetadataColumnID = 0;
myCol.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
myCol.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
IDTSCustomProperty100 myProp = myCol.CustomPropertyCollection.New();
myProp.Name = "Expression";
myProp.Value = "Empid";
myProp = myCol.CustomPropertyCollection.New();**strong text**
myProp.Name = "FriendlyExpression";
myProp.Value = "Empid";**
IDTSInput100 dInput = derived.InputCollection[0];
IDTSVirtualInput100 vdInput = dInput.GetVirtualInput();//Get this components default input and virtual input
//Create the input columns for the transformation component
IDTSInput100 input = derived.InputCollection[0];
IDTSVirtualInput100 derivedInputVirtual = input.GetVirtualInput();
input.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
input.ErrorOrTruncationOperation = "";
DesignDerivedColumns.ReleaseConnections();
// Get the destination's default input and virtual input.
IDTSInput100 destinationinput = destination.InputCollection[0];
int destinationInputID = input.ID;
IDTSVirtualInput100 vdestinationinput = destinationinput.GetVirtualInput();
//Iterate through the virtual input column collection.
foreach (IDTSVirtualInputColumn100 vColumn in vdestinationinput.VirtualInputColumnCollection)
{
IDTSInputColumn100 vCol = destDesignTime.SetUsageType(destinationinput.ID, vdestinationinput, vColumn.LineageID, DTSUsageType.UT_READWRITE);
String cinputColumnName = vColumn.Name;
var columnExist = (from item in destinationinput.ExternalMetadataColumnCollection.Cast<IDTSExternalMetadataColumn100>()
where item.Name == cinputColumnName
select item).Count();
if (columnExist > 0)
destDesignTime.MapInputColumn(destinationinput.ID, vCol.ID, destinationinput.ExternalMetadataColumnCollection[vColumn.Name].ID);
}
app.SaveToXml(@"D:\TestEmp.dtsx", pkg, null);
