API Sample – ADO.Net Source

API Sample – ADO.Net Source

Rate This
  • Comments 3

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

Items of interest:

  • The AccessMode property is similar to that of the OleDB Source, 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();
}
Leave a Comment
  • Please add 8 and 4 and type the answer here:
  • Post
  • This is an index post for the series of posts with examples on how to create packages programmatically.

  • 1. 构建一个包含数据流任务(Data Flow Task)的包 下面的代码可以生成一个包含数据流任务的包,这个包不执行任何实际的工作,但是可以作为构建一个更复杂的包的基础: static void Main(

  • Thank you so much.  This was a huge help when I needed it the most.  It is not nearly as straightforward as I though going into the project.

Page 1 of 1 (3 items)