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