API Sample - OleDB source and OleDB destination

API Sample - OleDB source and OleDB destination

  • Comments 7

 

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

Value Text Related Property Description
0 Open Rowset OpenRowset You provide the name of a table or view.
1 Open Rowset from Variable OpenRowsetVariable You provide the name of a variable that contains the name of a table or view.
2 SQL Command SqlCommand

You provide a SQL statement.

3 SQL Command from Variable SqlCommandVariable You provide the name of a variable that contains the SQL statement you wish to execute.

OLEDB Destination

Value Text Related Property Description
0 Open Rowset OpenRowset

You provide the name of a table or view.

1 Open Rowset from Variable OpenRowsetVariable You provide the name of a variable that contains the name of a table or view.
2 SQL Command SqlCommand

You provide a SQL statement.

3 Open Rowset Using Fastload OpenRowset You provide the name of a table or view.
4 Open Rowset Using Fastload from Variable OpenRowsetVariable

You provide the name of a variable that contains the name of a table or view.

  • 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);
            }
        }
    }
}
Leave a Comment
  • Please add 1 and 8 and type the answer here:
  • Post
  • This sample creates a data flow package with an ADO.Net source. Items of interest: The AccessMode property

  • This is an index post for the series of posts with examples on how to create packages programmatically.

  • why was SQL Command from variable not present in OLEDB Destination

  • And now what ... how do I now trasfer the data ... what's the command/method/function to execute the dataFlowTask?

  • Dynamically create data flow dest table if it does not exist?

    (suppose i have "Table_1" when i am execute the package i want to destination table "Table_2" again execute the package "Table_3")

    Please any one give me answer.................

  • How to capture the error records from source and write into different destination?.

  • Thanks  for your code. it is really good

Page 1 of 1 (7 items)