API Sample – Row Count Transform

This sample creates a data flow package with an OLEDB Source that feeds into a Row Count transform. The Row Count transform is configured to store the result in the RowCountVar package variable.

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 a package variable to store the row count value

    package.Variables.Add("RowCountVar", false, "User", 0);

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

    // Add Row Count transform

    IDTSComponentMetaData100 rowCount = pipeline.ComponentMetaDataCollection.New();
    rowCount.ComponentClassID = "DTSTransform.RowCount";
    CManagedComponentWrapper instance = rowCount.Instantiate();

    // Set the variable name property
    instance.SetComponentProperty("VariableName", "User::RowCountVar");

    // Connect the OLEDB Source and the Row Count

    IDTSPath100 path = pipeline.PathCollection.New();
    path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], rowCount.InputCollection[0]);

Comments (3)

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

  2. mandar.patki@tcs.com says:


    Thanks alot for such a good collection of API samples.

    I am developing such Transformation operators using APIs. I want to know that how can I get the no. of rows at runtime (while using APIs) transferred between Data-Source and Transformation component or between Transformation component and destination component?

    I tried to explore ‘IDTSPath100’ path object during runtime(debug mode), but could not find the count of rows getting passed from Transformation Component to Destination Component.

    Your help will be appreciated.



  3. 这篇文章中我们将继续和大家分享一些使用SSIS API构建SSIS包的示例 1. 构建一个Row Count转换 下面的代码可以构建一个包含OLEDB数据源和Row Count转换(包含在一个数据流任务重)的SSIS包。其中Row