Creating SSIS package failed

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