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();
srcDesignTimeComponent.ProvideComponentProperties();
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
srcDesignTimeComponent.AcquireConnections(null);
srcDesignTimeComponent.ReinitializeMetaData();
srcDesignTimeComponent.ReleaseConnections();
//
// 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();
splitWrapper.ProvideComponentProperties();
//
// 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");
}
