API Sample – OleDB source and OleDB destination


 


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);
}
}
}
}

Comments (9)

  1. This sample creates a data flow package with an ADO.Net source. Items of interest: The AccessMode property

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

  3. sumanth says:

    why was SQL Command from variable not present in OLEDB Destination

  4. Bidyut says:

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

  5. Nagaraju says:

    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……………..

  6. Bala Chennai says:

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

  7. Venkat says:

    Thanks  for your code. it is really good

  8. Diego says:

    THANK YOU THANK YOU THANK YOU THANK YOU

  9. Dharam says:

    Can you please provide the code to pass parameter to stored procedure ?

    Here is the code which i am using

    srcDesignTimeComponent.SetComponentProperty("AccessMode", 2);

               Guid variableGuid = new Guid(Dts.Variables["FirstName"].ID);

               String ParamProperty = @"""@param:Input"",{" + variableGuid.ToString().ToUpper() + @"};";

               srcDesignTimeComponent.SetComponentProperty("SqlCommand", "EXEC Test @param=?");

               srcDesignTimeComponent.SetComponentProperty("ParameterMapping", ParamProperty);