API Sample – Conditional Split

Published Mar 25 2019 02:23 PM 97 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();
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");
}
%3CLINGO-SUB%20id%3D%22lingo-sub-387556%22%20slang%3D%22en-US%22%3EAPI%20Sample%20%E2%80%93%20Conditional%20Split%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-387556%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Dec%2030%2C%202008%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EThis%20sample%20creates%20a%20data%20flow%20package%20with%20an%20OLEDB%20source%20feeding%20into%20a%20Conditional%20Split.%3C%2FP%3E%0A%20%20%3CP%3EItems%20of%20interest%3A%3C%2FP%3E%0A%20%20%3CUL%3E%0A%20%20%20%3CLI%3EThe%20conditional%20split%20expression%20is%20set%20as%20a%20property%20on%20the%20transform%E2%80%99s%20output.%20The%20transform%20also%20has%20a%20default%20output.%20You%20will%20get%20a%20COM%20exception%20if%20you%20try%20to%20set%20an%20expression%20on%20the%20default%20output.%20See%20the%20comments%20in%20the%20code.%3C%2FLI%3E%0A%20%20%3C%2FUL%3Estatic%20void%20Main(string%5B%5D%20args)%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20Package%20package%20%3D%20new%20Package()%3B%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20Add%20Data%20Flow%20Task%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Executable%20dataFlowTask%20%3D%20package.Executables.Add(%22STOCK%3APipelineTask%22)%3B%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20Set%20the%20name%20(otherwise%20it%20will%20be%20a%20random%20GUID%20value)%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20TaskHost%20taskHost%20%3D%20dataFlowTask%20as%20TaskHost%3B%20%3CBR%20%2F%3E%20taskHost.Name%20%3D%20%22Data%20Flow%20Task%22%3B%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20We%20need%20a%20reference%20to%20the%20InnerObject%20to%20add%20items%20to%20the%20data%20flow%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20MainPipe%20pipeline%20%3D%20taskHost.InnerObject%20as%20MainPipe%3B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20Add%20connection%20manager%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20ConnectionManager%20connection%20%3D%20package.Connections.Add(%22OLEDB%22)%3B%20%3CBR%20%2F%3E%20connection.Name%20%3D%20%22localhost%22%3B%20%3CBR%20%2F%3E%20connection.ConnectionString%20%3D%20%22Data%20Source%3Dlocalhost%3BInitial%20Catalog%3DAdventureWorksDW2008%3BProvider%3DSQLNCLI10.1%3BIntegrated%20Security%3DSSPI%3BAuto%20Translate%3DFalse%3B%22%3B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20Add%20OLEDB%20Source%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20IDTSComponentMetaData100%20srcComponent%20%3D%20pipeline.ComponentMetaDataCollection.New()%3B%20%3CBR%20%2F%3E%20srcComponent.ComponentClassID%20%3D%20%22DTSAdapter.OleDbSource%22%3B%20%3CBR%20%2F%3E%20srcComponent.ValidateExternalMetadata%20%3D%20true%3B%20%3CBR%20%2F%3E%20IDTSDesigntimeComponent100%20srcDesignTimeComponent%20%3D%20srcComponent.Instantiate()%3B%20%3CBR%20%2F%3E%20srcDesignTimeComponent.ProvideComponentProperties()%3B%20%3CBR%20%2F%3E%20srcComponent.Name%20%3D%20%22OleDb%20Source%22%3B%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20Configure%20it%20to%20read%20from%20the%20given%20table%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20srcDesignTimeComponent.SetComponentProperty(%22AccessMode%22%2C%200)%3B%20%3CBR%20%2F%3E%20srcDesignTimeComponent.SetComponentProperty(%22OpenRowset%22%2C%20%22%5BDimProduct%5D%22)%3B%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20Set%20the%20connection%20manager%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20srcComponent.RuntimeConnectionCollection%5B0%5D.ConnectionManager%20%3D%20DtsConvert.GetExtendedInterface(connection)%3B%20%3CBR%20%2F%3E%20srcComponent.RuntimeConnectionCollection%5B0%5D.ConnectionManagerID%20%3D%20connection.ID%3B%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20Retrieve%20the%20column%20metadata%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20srcDesignTimeComponent.AcquireConnections(null)%3B%20%3CBR%20%2F%3E%20srcDesignTimeComponent.ReinitializeMetaData()%3B%20%3CBR%20%2F%3E%20srcDesignTimeComponent.ReleaseConnections()%3B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20Add%20conditional%20split%20transform%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20IDTSComponentMetaData100%20splitComponent%20%3D%20pipeline.ComponentMetaDataCollection.New()%3B%20%3CBR%20%2F%3E%20splitComponent.ComponentClassID%20%3D%20%22DTSTransform.ConditionalSplit%22%3B%20%3CBR%20%2F%3E%20splitComponent.Name%20%3D%20%22Conditional%20Split%22%3B%20%3CBR%20%2F%3E%20splitComponent.Description%20%3D%20%22Conditional%20Split%20Transform%22%3B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20CManagedComponentWrapper%20splitWrapper%20%3D%20splitComponent.Instantiate()%3B%20%3CBR%20%2F%3E%20splitWrapper.ProvideComponentProperties()%3B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20Connect%20the%20source%20and%20the%20transform%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20IDTSPath100%20path%20%3D%20pipeline.PathCollection.New()%3B%20%3CBR%20%2F%3E%20path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection%5B0%5D%2C%20splitComponent.InputCollection%5B0%5D)%3B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20Configure%20the%20transform%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%2F%2F%20The%20transform%20will%20have%20a%20single%20output%20when%20first%20created.%20This%20is%20the%20default%20output%20and%20will%20handle%20any%20%3CBR%20%2F%3E%20%2F%2F%20rows%20that%20aren't%20matched%20by%20any%20of%20the%20expressions%20on%20the%20other%20outputs.%20Here%20we%20are%20adding%20our%20new%20outputs%20%3CBR%20%2F%3E%20%2F%2F%20before%20this%20one%2C%20so%20our%20default%20output%20will%20always%20be%20the%20last%20one%20in%20the%20collection.%20We%20can%20also%20control%20%3CBR%20%2F%3E%20%2F%2F%20which%20output%20is%20the%20default%20one%20by%20setting%20the%20IsDefaultOut%20property%20to%20true.%20%3CBR%20%2F%3E%20IDTSOutput100%20splitOutput%20%3D%20splitWrapper.InsertOutput(DTSInsertPlacement.IP_BEFORE%2C%20splitComponent.OutputCollection%5B0%5D.ID)%3B%20%3CBR%20%2F%3E%20splitOutput.Name%20%3D%20%22Split%20Output%201%22%3B%20%3CBR%20%2F%3E%20splitOutput.Description%20%3D%20%22Handles%20rows%20that%20have%20a%20product%20key%20less%20than%20or%20equal%20to%20800%22%3B%20%3CBR%20%2F%3E%20splitOutput.IsErrorOut%20%3D%20false%3B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%2F%2F%20We%20need%20to%20set%20a%20column's%20usage%20type%20before%20we%20can%20use%20it%20in%20an%20expression.%20%3CBR%20%2F%3E%20%2F%2F%20The%20code%20here%20will%20make%20all%20of%20the%20input%20columns%20available%2C%20but%20we%20could%20also%20%3CBR%20%2F%3E%20%2F%2F%20restrict%20it%20to%20just%20the%20columns%20that%20we%20need%20in%20the%20conditional%20split%20expression(s).%20%3CBR%20%2F%3E%20IDTSInput100%20splitInput%20%3D%20splitComponent.InputCollection%5B0%5D%3B%20%3CBR%20%2F%3E%20IDTSInputColumnCollection100%20splitInputColumns%20%3D%20splitInput.InputColumnCollection%3B%20%3CBR%20%2F%3E%20IDTSVirtualInput100%20splitVirtualInput%20%3D%20splitInput.GetVirtualInput()%3B%20%3CBR%20%2F%3E%20IDTSVirtualInputColumnCollection100%20splitVirtualInputColumns%20%3D%20splitVirtualInput.VirtualInputColumnCollection%3B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20int%20columnCount%20%3D%20splitVirtualInputColumns.Count%3B%20%3CBR%20%2F%3E%20for%20(int%20i%20%3D%200%3B%20i%20%26lt%3B%20columnCount%3B%20i%2B%2B)%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20splitWrapper.SetUsageType(splitInput.ID%2C%20splitVirtualInput%2C%20splitVirtualInputColumns%5Bi%5D.LineageID%2C%20DTSUsageType.UT_READONLY)%3B%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%2F%2F%20Note%3A%20You%20will%20get%20an%20exception%20if%20you%20try%20to%20set%20these%20properties%20on%20the%20Default%20Output.%20%3CBR%20%2F%3EsplitWrapper.SetOutputProperty(splitOutput.ID%2C%20%22EvaluationOrder%22%2C%200)%3B%20%3CBR%20%2F%3E%20splitWrapper.SetOutputProperty(splitOutput.ID%2C%20%22FriendlyExpression%22%2C%20%22%5BProductKey%5D%20%26lt%3B%3D%20800%22)%3B%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%3CA%20href%3D%22http%3A%2F%2F11011.net%2Fsoftware%2Fvspaste%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-387556%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Dec%2030%2C%202008%20This%20sample%20creates%20a%20data%20flow%20package%20with%20an%20OLEDB%20source%20feeding%20into%20a%20Conditional%20Split.%3C%2FLINGO-TEASER%3E
Version history
Last update:
‎Mar 25 2019 02:23 PM
Updated by: