SSIS Team Blog

Helpful information and examples on how to use SQL Server Integration Services.

API Sample – Row Count Transform

API Sample – Row Count Transform

  • Comments 3

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]);
  • This is an index post for the series of posts with examples on how to create packages programmatically.

  • Hi!

    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.



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

Page 1 of 1 (3 items)
Leave a Comment
  • Please add 7 and 8 and type the answer here:
  • Post