Hi,
I'm trying to create a SSIS package. I have used OLEDBSource adapter to get the source table's data and transferring the data to an OLEDBDestination adapter. I tried but I'm facing the problem in mapping the metadata contents.
My code:
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
static void Main(string[] args)
{
// Create a new package
Package package = new Package();
package.Name = "OLE DB Transfer";
// Add a Data Flow task
TaskHost taskHost = package.Executables.Add("DTS.Pipeline") as TaskHost;
taskHost.Name = "Transfer Table";
IDTSPipeline90 pipeline = taskHost.InnerObject as MainPipe;
// Get the pipeline's component metadata collection
IDTSComponentMetaDataCollection90 componentMetadataCollection = pipeline.ComponentMetaDataCollection;
// Add a new component metadata object to the data flow
IDTSComponentMetaData90 oledbSourceMetadata = componentMetadataCollection.New();
// Associate the component metadata object with the OLE DB Source Adapter
oledbSourceMetadata.ComponentClassID = "DTSAdapter.OLEDBSource";
// Instantiate the OLE DB Source adapter
IDTSDesigntimeComponent90 oledbSourceComponent = oledbSourceMetadata.Instantiate();
// Ask the component to set up its component metadata object
oledbSourceComponent.ProvideComponentProperties();
// Add an OLE DB connection manager
ConnectionManager connectionManagerSource = package.Connections.Add("OLEDB");
connectionManagerSource.Name = "OLEDBSource";
// Set the connection string
connectionManagerSource.ConnectionString = "provider=sqlncli;server=HSCHBSCGN25008;integrated security=sspi;database=Muthu_SSIS_Testing";
// Set the connection manager as the OLE DB Source adapter's runtime connection
IDTSRuntimeConnection90 runtimeConnectionSource = oledbSourceMetadata.RuntimeConnectionCollection["OleDbConnection"];
runtimeConnectionSource.ConnectionManagerID = connectionManagerSource.ID;
// Tell the OLE DB Source adapter to use the SQL Command access mode.
oledbSourceComponent.SetComponentProperty("AccessMode", 2);
// Set up the SQL command
oledbSourceComponent.SetComponentProperty("SqlCommand", "select * from EmployeeTable");
// Set up the connection manager object
runtimeConnectionSource.ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerSource);
// Establish the database connection
oledbSourceComponent.AcquireConnections(null);
// Set up the column metadata
oledbSourceComponent.ReinitializeMetaData();
// Release the database connection
oledbSourceComponent.ReleaseConnections();
// Release the connection manager
runtimeConnectionSource.ReleaseConnectionManager();
// Add a new component metadata object to the data flow
IDTSComponentMetaData90 oledbDestinationMetadata = componentMetadataCollection.New();
//Associate the component metadata object with the OLE DB Destination Adapter
oledbDestinationMetadata.ComponentClassID = "DTSAdapter.OLEDBDestination";
// Instantiate the OLE DB Destination adapter
IDTSDesigntimeComponent90 oledbDestinationComponent = oledbDestinationMetadata.Instantiate();
// Ask the component to set up its component metadata object
oledbDestinationComponent.ProvideComponentProperties();
// Add an OLE DB connection manager
ConnectionManager connectionManagerDestination = package.Connections.Add("OLEDB");
connectionManagerDestination.Name = "OLEDBDestination";
// Set the connection string
connectionManagerDestination.ConnectionString = "provider=sqlncli;server=HSCHBSCGN25008;integrated security=sspi;database=Muthu_SSIS_Testing";
// Set the connection manager as the OLE DB Destination adapter's runtime connection
IDTSRuntimeConnection90 runtimeConnectionDestination = oledbDestinationMetadata.RuntimeConnectionCollection["OleDbConnection"];
runtimeConnectionDestination.ConnectionManagerID = connectionManagerDestination.ID;
// Tell the OLE DB Destination adapter to use the SQL Command access mode.
oledbDestinationComponent.SetComponentProperty("AccessMode", 2);
// Set up the SQL command
oledbDestinationComponent.SetComponentProperty("SqlCommand", "select from EmplTable");
// Set up the connection manager object
runtimeConnectionDestination.ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerDestination);
// Get the standard output of the OLE DB Source adapter
IDTSOutput90 oledbSourceOutput = oledbSourceMetadata.OutputCollection["OLE DB Source Output"];
// Get the input of the OLE DB Destination adapter
IDTSInput90 oledbDestinationInput = oledbDestinationMetadata.InputCollection["OLE DB Destination Input"];
// Create a new path object
IDTSPath90 path = pipeline.PathCollection.New();
// Connect the source and destination adapters
path.AttachPathAndPropagateNotifications(oledbSourceOutput, oledbDestinationInput);
IDTSInput90 input = oledbDestinationInput;
IDTSVirtualInput90 vInput = input.GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the destination
// to add each available virtual input column as an input column.
oledbDestinationComponent.SetUsageType(
input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
foreach (IDTSInputColumn90 col in oledbDestinationInput.InputColumnCollection)
{
IDTSExternalMetadataColumn90 exCol = oledbDestinationInput.ExternalMetadataColumnCollection[col.Name];
oledbDestinationComponent.MapInputColumn(oledbDestinationInput.ID, col.ID, exCol.ID);
}
Console.WriteLine("done");
Console.ReadKey();
// Save the package
//Application application = new Application();
//application.SaveToXml(@."c:\OLEDBTransfer.dtsx", package, null);
}
While debugging, I'm getting exception (ELEMENTNOTFOUND) at this line
IDTSExternalMetadataColumn90 exCol = oledbDestinationInput.ExternalMetadataColumnCollection[col.Name];
because metadata have no appropriate column to map with the destination component.
Any one help me to resolve this issue.
Regards,
kris
Hi,
I have resolved my issues.
For destination component, I initialized the connection and reinitialized metadata and done the mapping then released the connection for destination component.
Now my code is working.
Thanks for your helps..
Regards,
kris
