API Sample – ADO.Net Source

First published on MSDN on Dec 30, 2008

This sample creates a data flow package with an ADO.Net source.

Items of interest:

• The AccessMode property is similar to that of the http://blogs.msdn.com/mattm/archive/2008/12/30/api-sample-oledb-source-and-oledb-destination.aspx , except it only has two values – 0 for TableOrViewName, and 2 for SqlCommand. Examples of both are included in the sample (one is commented out).
• To be able to reference the DataReaderSourceAdapter (the class name of the ADO.Net Source), you’ll need to add a reference to the ADONETSrc assembly - C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents\Microsoft.SqlServer.ADONETSrc.dll

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;

// Create ADO.Net connection manager
string connectionType = string.Format("ADO.NET:{0}", typeof(SqlConnection).AssemblyQualifiedName);
ConnectionManager connection = package.Connections.Add(connectionType);
connection.Name = "localhost";
connection.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorksDW2008;Integrated Security=True;";

// Add the ADO.NET Source
IDTSComponentMetaData100 src = pipeline.ComponentMetaDataCollection.New();
src.Name = "AdoNet Source";
src.ComponentClassID = typeof(DataReaderSourceAdapter).AssemblyQualifiedName;
src.ValidateExternalMetadata = true;
IDTSDesigntimeComponent100 instance = src.Instantiate();
instance.ProvideComponentProperties();

// Configure the source
//instance.SetComponentProperty("AccessMode", 2);
//instance.SetComponentProperty("SqlCommand", "select * from [DimCustomer]");
instance.SetComponentProperty("AccessMode", 0);
instance.SetComponentProperty("TableOrViewName", "\"dbo\".\"DimCustomer\"");

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

// Retrieve the column metadata
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();
}
http://11011.net/software/vspaste