API Sample – Conditional Split
Published Mar 25 2019 02:23 PM 193 Views
Not applicable
First published on MSDN on Dec 30, 2008

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");
Version history
Last update:
‎Mar 25 2019 02:23 PM
Updated by: