API Sample – ADO.Net Source

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

    // 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

Comments (3)

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

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

  3. Eric says:

    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.