This sample creates a data flow package with an OleDB Source component which reads from a local AdventureWorksDW2008 database, and writes to a different table in the same database using an OleDB Destination.

Items of interest:

• The AccessMode property is an int value which controls how you’re retrieving/sending data. Once you set a value, you need to set a value for the related property (see the table below). For example, if you set the AccessMode for the OLEDB Source to 2 (SQL Command), you should set a SQL statement value for the SqlCommand property.

OLEDB Source

OLEDB Destination

• Calling ReinitializeMetadata() causes the component to fetch the table metadata. This should only be called after setting the AccessMode and related property.

/// <summary>
/// Creates a data flow package with a source and destination sharing a connection manager. 
/// The source reads all columns from the [DimCustomer] table, and the destination writes
/// them to the [DimCustomer_Copy] table.
/// </summary>
static void Main(string[] args)
{
    Package package = new Package();

    // Add Data Flow Task
    Executable dataFlowTask = package.Executables.Add("STOCK:PipelineTask");

    // Set the name (otherwise it will be a random GUID value)
    TaskHost taskHost = dataFlowTask as TaskHost;
    taskHost.Name = "Data Flow Task";

    // We need a reference to the InnerObject to add items to the data flow
    MainPipe pipeline = taskHost.InnerObject as MainPipe;

    //
    // Add connection manager
    //

    ConnectionManager connection = package.Connections.Add("OLEDB");
    connection.Name = "localhost";
    connection.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorksDW2008;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;";

    //
    // Add OLEDB Source
    //

    IDTSComponentMetaData100 srcComponent = pipeline.ComponentMetaDataCollection.New();
    srcComponent.ComponentClassID = "DTSAdapter.OleDbSource";
    srcComponent.ValidateExternalMetadata = true;
    IDTSDesigntimeComponent100 srcDesignTimeComponent = srcComponent.Instantiate();
    srcDesignTimeComponent.ProvideComponentProperties();
    srcComponent.Name = "OleDb Source";

    // Configure it to read from the given table
    srcDesignTimeComponent.SetComponentProperty("AccessMode", 0);
    srcDesignTimeComponent.SetComponentProperty("OpenRowset", "[DimCustomer]");

    // Set the connection manager
    srcComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connection);
    srcComponent.RuntimeConnectionCollection[0].ConnectionManagerID = connection.ID;

    // Retrieve the column metadata
    srcDesignTimeComponent.AcquireConnections(null);
    srcDesignTimeComponent.ReinitializeMetaData();
    srcDesignTimeComponent.ReleaseConnections();

    //
    // Add OLEDB Destination
    //

    IDTSComponentMetaData100 destComponent = pipeline.ComponentMetaDataCollection.New();
    destComponent.ComponentClassID = "DTSAdapter.OleDbDestination";
    destComponent.ValidateExternalMetadata = true;

    IDTSDesigntimeComponent100 destDesignTimeComponent = destComponent.Instantiate();
    destDesignTimeComponent.ProvideComponentProperties();
    destComponent.Name = "OleDb Destination";

    destDesignTimeComponent.SetComponentProperty("AccessMode", 3);
    destDesignTimeComponent.SetComponentProperty("OpenRowset", "[DimCustomer_Copy]");

    // set connection
    destComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connection);
    destComponent.RuntimeConnectionCollection[0].ConnectionManagerID = connection.ID;

    // get metadata
    destDesignTimeComponent.AcquireConnections(null);
    destDesignTimeComponent.ReinitializeMetaData();
    destDesignTimeComponent.ReleaseConnections();

    //
    // Connect source and destination
    //

    IDTSPath100 path = pipeline.PathCollection.New();
    path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], destComponent.InputCollection[0]);

    //
    // Configure the destination
    // 

    IDTSInput100 destInput = destComponent.InputCollection[0];
    IDTSVirtualInput100 destVirInput = destInput.GetVirtualInput();
    IDTSInputColumnCollection100 destInputCols = destInput.InputColumnCollection;
    IDTSExternalMetadataColumnCollection100 destExtCols = destInput.ExternalMetadataColumnCollection;
    IDTSOutputColumnCollection100 sourceColumns = srcComponent.OutputCollection[0].OutputColumnCollection;

    // The OLEDB destination requires you to hook up the external columns
    foreach (IDTSOutputColumn100 outputCol in sourceColumns)
    {
        // Get the external column id
        IDTSExternalMetadataColumn100 extCol = (IDTSExternalMetadataColumn100)destExtCols[outputCol.Name];
        if (extCol != null)
        {
            // Create an input column from an output col of previous component.
            destVirInput.SetUsageType(outputCol.ID, DTSUsageType.UT_READONLY);
            IDTSInputColumn100 inputCol = destInputCols.GetInputColumnByLineageID(outputCol.ID);
            if (inputCol != null)
            {
                // map the input column with an external metadata column
                destDesignTimeComponent.MapInputColumn(destInput.ID, inputCol.ID, extCol.ID);
            }
        }
    }
}