API Sample – Conditional Split

This sample creates a data flow package with an OLEDB source feeding into a Conditional Split.

Items of interest:

  • The conditional split expression is set as a property on the transform’s output. The transform also has a default output. You will get a COM exception if you try to set an expression on the default output. See the comments in the code.
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;

    // 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", "[DimProduct]");

    // Set the connection manager
    srcComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connection);
    srcComponent.RuntimeConnectionCollection[0].ConnectionManagerID = connection.ID;

    // Retrieve the column metadata

    // Add conditional split transform

    IDTSComponentMetaData100 splitComponent = pipeline.ComponentMetaDataCollection.New();
    splitComponent.ComponentClassID = "DTSTransform.ConditionalSplit";
    splitComponent.Name = "Conditional Split";
    splitComponent.Description = "Conditional Split Transform";

    CManagedComponentWrapper splitWrapper = splitComponent.Instantiate();

    // Connect the source and the transform

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

    // Configure the transform

    // The transform will have a single output when first created. This is the default output and will handle any
    // rows that aren't matched by any of the expressions on the other outputs. Here we are adding our new outputs
    // before this one, so our default output will always be the last one in the collection. We can also control 
    // which output is the default one by setting the IsDefaultOut property to true.
    IDTSOutput100 splitOutput = splitWrapper.InsertOutput(DTSInsertPlacement.IP_BEFORE, splitComponent.OutputCollection[0].ID);
    splitOutput.Name = "Split Output 1";
    splitOutput.Description = "Handles rows that have a product key less than or equal to 800";
    splitOutput.IsErrorOut = false;

    // We need to set a column's usage type before we can use it in an expression.
    // The code here will make all of the input columns available, but we could also
    // restrict it to just the columns that we need in the conditional split expression(s).
    IDTSInput100 splitInput = splitComponent.InputCollection[0];
    IDTSInputColumnCollection100 splitInputColumns = splitInput.InputColumnCollection;
    IDTSVirtualInput100 splitVirtualInput = splitInput.GetVirtualInput();
    IDTSVirtualInputColumnCollection100 splitVirtualInputColumns = splitVirtualInput.VirtualInputColumnCollection;

    int columnCount = splitVirtualInputColumns.Count;
    for (int i = 0; i < columnCount; i++)
        splitWrapper.SetUsageType(splitInput.ID, splitVirtualInput, splitVirtualInputColumns[i].LineageID, DTSUsageType.UT_READONLY);

    // Note: You will get an exception if you try to set these properties on the Default Output.
    splitWrapper.SetOutputProperty(splitOutput.ID, "EvaluationOrder", 0);
    splitWrapper.SetOutputProperty(splitOutput.ID, "FriendlyExpression", "[ProductKey] <= 800");

Comments (1)

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

Skip to main content