Programming Conditional Split Transformation

Home > SSIS, SSIS programming > Programming Conditional Split Transformation

Programming Conditional Split Transformation

May 9th, 2009 Arun Mallick Leave a comment Go to comments

There may be some circumstances in a sanctuary where it becomes necessary to keep a leash of foxes and a pack of wolves separate so that they must not fight with each other and make things a mess. Similarly, in the database jungle we do the same things by using some conditional statements in order to keep things distinct. SSIS provides a nice transformation to do the same, i.e. Conditional Split Transformation.

By using this conditional split transformation, the incoming data can be split into multiple outputs as per the conditions specified. It has a default output too that handles all the rows that do not match to any conditions.

For Example:

Let us have an employee table in our OLTP database which contains information about employees along with the departmental information. Our requirement is that we need to split the data of employees into our OLAP database to two separate tables; one containing information about sales employees and second containing all other employees’ information.

The table structure of the employee table in OLTP database is:

Column_name	Type
employee_Sl_No	int
Department	varchar
emp_name	varchar
emp_id	int
emp_DOB	datetime

The employee table contains the below data:

employee_Sl_No	Department	emp_name	emp_id	emp_DOB
1	sales	Arun	101	1984-03-16 00:00:00.000
2	sales	Akash	102	1982-01-31 00:00:00.000
3	marketing	Ram	103	1986-03-03 00:00:00.000
4	marketing	Rasi	104	1986-04-25 00:00:00.000
5	manufacture	viswa	106	1982-04-22 00:00:00.000
6	finance	Kishore	107	1980-08-26 00:00:00.000

The logical diagram of the above requirement is:

The dataflow component to achieve the above logic with conditional split transformation is:

The condition for the above logic is given below:

Note: For detail about the other components of the package, please refer to my earlier post named “SSIS Programming Basic”

After successful completion of the package, we will have 2 rows in salesEmployeeDim table and the remaining 4 in OthersEmployeeDim.

This can be done very easily by Integration programming. For this we have to create a package first, and then have to add the tasks and the precedence constraints accordingly. Then we have to add the package variable. In the dataflow task, we have to add the data flow source, the conditional split transformation and two data flow destination components.

The creation name for conditional split transformation is “DTSTransform.ConditionalSplit.1” and the component class ID is “{53A228EE-EBFA-48D6-A1AC-5269E5824A2C}”.

The code below shows the above package programmatically and the code itself is self-descriptive. The code for SQL server 2005 and SQL Server 2008 are a bit different; both the approaches are given below.

Add the below References before developing the code.

Microsoft.SqlServer.Dts.Design
Microsoft.SqlServer.DTSPipelineWrap
Microsoft.SQLServer.DTSRuntimeWrap
Microsoft.SQLServer.ManagedDTS

Programming Integration Service in SQL Server 2005

C# .Net Expand

[ Copy to Clipboard ] | [ View Source ]

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Data.SqlClient;
using System.Xml;
 
 
//The below code will create a SSIS package with CONDITIONAL SPLIT TRANSFORMATION for SQL Server 2005
 
namespace CreatePackageSample
{
    class Program
    {
        static void Main(string[] args)
        {
            //To Create a package named [Sample Package]
            Package package = new Package();
            package.Name = "Sample Package (Conditional Split)";
            package.PackageType = DTSPackageType.DTSDesigner90;
            package.VersionBuild = 1;
 
            //To add Connection Manager to the package
            //For source database (OLTP)
            ConnectionManager OLTP = package.Connections.Add("OLEDB");
            OLTP.ConnectionString = "Data Source=.;Initial Catalog=OLTP;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";
            OLTP.Name = "LocalHost.OLTP";
            //For destination database (OLAP)
            ConnectionManager OLAP = package.Connections.Add("OLEDB");
            OLAP.ConnectionString = "Data Source=.;Initial Catalog=OLAP;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";
            OLAP.Name = "LocalHost.OLAP";
 
            //To add package variables
            Variable TransactionID = package.Variables.Add("TransactionID", false, "User", 0);
            TransactionID.Name = @"TransactionID";
 
            Variable RowCountVar = package.Variables.Add("RowCountVar", false, "User", 0);
            RowCountVar.Name = @"RowCountVar";
 
            //To add Package Start Indicator (Execute Sql task )
            TaskHost PackageStartIndicatorTask = (TaskHost)package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
            PackageStartIndicatorTask.Name = @"Package Start Indicator";
            PackageStartIndicatorTask.Description = @"Execute SQL Task Description";
            XmlDocument PackageStartIndicatorTaskdoc = new XmlDocument();
            PackageStartIndicatorTaskdoc.LoadXml(@"<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.InitTransaction"" SQLTask:ResultType=""ResultSetType_SingleRow"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ResultBinding SQLTask:ResultName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" /></SQLTask:SqlTaskData>");
            ((IDTSComponentPersist)PackageStartIndicatorTask.InnerObject).LoadFromXML(PackageStartIndicatorTaskdoc.DocumentElement, null);
 
            //To add Load Employee Dim to the package [Data Flow Task]
            TaskHost dataFlowTaskHost = (TaskHost)package.Executables.Add("DTS.Pipeline.1");
            dataFlowTaskHost.Name = @"Load Employee Dim";
            dataFlowTaskHost.FailPackageOnFailure = true;
            dataFlowTaskHost.FailParentOnFailure = true;
            dataFlowTaskHost.DelayValidation = false;
            dataFlowTaskHost.Description = @"Data Flow Task";
 
            //-----------Data Flow Inner component starts----------------
            MainPipe dataFlowTask = dataFlowTaskHost.InnerObject as MainPipe;
            // Source OLE DB connection manager to the package.
            ConnectionManager SconMgr = package.Connections["LocalHost.OLTP"];
            // Destination OLE DB connection manager to the package.
            ConnectionManager DconMgr = package.Connections["LocalHost.OLAP"];
 
            // Create and configure an OLE DB source component.  
            IDTSComponentMetaData90 source = dataFlowTask.ComponentMetaDataCollection.New();
            source.ComponentClassID = "DTSAdapter.OLEDBSource.1";
 
            // Create the design-time instance of the source.
            CManagedComponentWrapper srcDesignTime = source.Instantiate();
            // The ProvideComponentProperties method creates a default output.
            srcDesignTime.ProvideComponentProperties();
            source.Name = "Employee Dim from OLTP";
 
            // Assign the connection manager.
            source.RuntimeConnectionCollection[0].ConnectionManagerID = SconMgr.ID;
            source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(SconMgr);
            // Set the custom properties of the source.
            srcDesignTime.SetComponentProperty("AccessMode", 0); // Mode 0 : OpenRowset / Table - View
            srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Employee_Dim]");
            // Connect to the data source, and then update the metadata for the source.
            srcDesignTime.AcquireConnections(null);
            srcDesignTime.ReinitializeMetaData();
            srcDesignTime.ReleaseConnections();
 
            //Add an Conditional Split Transformation
            IDTSComponentMetaData90 conditionalSplitComponent = dataFlowTask.ComponentMetaDataCollection.New();
            conditionalSplitComponent.Name = "Conditional Split";
            conditionalSplitComponent.ComponentClassID = "DTSTransform.ConditionalSplit.1";
            CManagedComponentWrapper conditionalSplitDesignTime = conditionalSplitComponent.Instantiate();
            conditionalSplitDesignTime.ProvideComponentProperties();
            //conditionalSplitDesignTime.SetComponentProperty("VariableName", "RowCountVar");
            conditionalSplitDesignTime.AcquireConnections(null);
            conditionalSplitDesignTime.ReinitializeMetaData();
            conditionalSplitDesignTime.ReleaseConnections();
 
            // Create the path from source to Conditional Split Transformation.
            IDTSPath90 pathSource_ConditionalSplit = dataFlowTask.PathCollection.New();
            pathSource_ConditionalSplit.AttachPathAndPropagateNotifications(source.OutputCollection[0], conditionalSplitComponent.InputCollection[0]);
 
 
 
            //Set properties for Conditional Split transformation (condition statments)
            IDTSOutput90 conditionalSplitSalesDeptOutput = conditionalSplitDesignTime.InsertOutput(DTSInsertPlacement.IP_BEFORE, conditionalSplitComponent.OutputCollection[0].ID);
            conditionalSplitSalesDeptOutput.Name = "Sales Dept";
            conditionalSplitSalesDeptOutput.Description = "Sales Dept";
            IDTSInput90 conditionalsplitInput = conditionalSplitComponent.InputCollection[0];
            IDTSVirtualInput90 conditionalSplitVirtualInput = conditionalsplitInput.GetVirtualInput();
            IDTSVirtualInputColumnCollection90 conditionalSplitVirtualInputColumns = conditionalSplitVirtualInput.VirtualInputColumnCollection;
            //Here only the columns that are used in the expressions are going to be fetched in input collection
            int columnCount = conditionalSplitVirtualInputColumns.Count;
            for (int i = 0; i < columnCount; i++)
            {
                if (conditionalSplitVirtualInputColumns[i].Name == "Department")
                    conditionalSplitDesignTime.SetUsageType(conditionalsplitInput.ID, conditionalSplitVirtualInput, conditionalSplitVirtualInputColumns[i].LineageID, DTSUsageType.UT_READONLY);
            }
            conditionalSplitDesignTime.SetOutputProperty(conditionalSplitSalesDeptOutput.ID, "FriendlyExpression", "[Department] == "sales"");
 
 
            //Give a user friendly name to the default output
            conditionalSplitComponent.OutputCollection["Conditional Split Default Output"].Name = "Other Depts"; 
            IDTSOutput90 conditionalSplitDefaultOutput = conditionalSplitComponent.OutputCollection["Other Depts"]; 
 
 
 
            // Create and configure an OLE DB destination component for Sales Dept.
            IDTSComponentMetaData90 destinationSales = dataFlowTask.ComponentMetaDataCollection.New();
            destinationSales.ComponentClassID = "DTSAdapter.OLEDBDestination.1";
            // Create the design-time instance of the destination.
            CManagedComponentWrapper destDesignTime = destinationSales.Instantiate();
            // The ProvideComponentProperties method creates a default input.
            destDesignTime.ProvideComponentProperties();
            destinationSales.Name = "SalesEmployee Dim (OLAP)";
            // Assign the connection manager.
            destinationSales.RuntimeConnectionCollection[0].ConnectionManagerID = DconMgr.ID;
            destinationSales.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(DconMgr);
            // Set the custom properties.
            destDesignTime.SetComponentProperty("AccessMode", 3); // Mode 3 : OpenRowset Using FastLoad  / Table - View fast load
            destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[SalesEmployeeDim]");
            destDesignTime.AcquireConnections(null);
            destDesignTime.ReinitializeMetaData();
            destDesignTime.ReleaseConnections();
 
            // Create and configure an OLE DB destination component for Other Dept.
            IDTSComponentMetaData90 destinationOther = dataFlowTask.ComponentMetaDataCollection.New();
            destinationOther.ComponentClassID = "DTSAdapter.OLEDBDestination.1";
            // Create the design-time instance of the destination.
            CManagedComponentWrapper destOtherDesignTime = destinationOther.Instantiate();
            // The ProvideComponentProperties method creates a default input.
            destOtherDesignTime.ProvideComponentProperties();
            destinationOther.Name = "OthersEmployeeDim (OLAP)";
            // Assign the connection manager.
            destinationOther.RuntimeConnectionCollection[0].ConnectionManagerID = DconMgr.ID;
            destinationOther.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(DconMgr);
            // Set the custom properties.
            destOtherDesignTime.SetComponentProperty("AccessMode", 3); // Mode 3 : OpenRowset Using FastLoad  / Table - View fast load
            destOtherDesignTime.SetComponentProperty("OpenRowset", "[dbo].[OthersEmployeeDim]");
            destOtherDesignTime.AcquireConnections(null);
            destOtherDesignTime.ReinitializeMetaData();
            destOtherDesignTime.ReleaseConnections();
 
            // Create the path from Condtional Split transformation to destination.
            IDTSPath90 pathConditionalSplit_DestSales = dataFlowTask.PathCollection.New();
            pathConditionalSplit_DestSales.Name = "Sales Dept";
            pathConditionalSplit_DestSales.AttachPathAndPropagateNotifications(conditionalSplitSalesDeptOutput, destinationSales.InputCollection[0]);
 
            IDTSPath90 pathConditionalSplit_DestOthers = dataFlowTask.PathCollection.New();
            pathConditionalSplit_DestOthers.AttachPathAndPropagateNotifications(conditionalSplitDefaultOutput, destinationOther.InputCollection[0]);
            pathConditionalSplit_DestOthers.Name = "Other Depts";
 
 
            // Get the destination's default input and virtual input for sales dept.
            IDTSInput90 inputsales = destinationSales.InputCollection[0];
            IDTSVirtualInput90 vInputsales = inputsales.GetVirtualInput();
            // Iterate through the virtual input column collection.
            foreach (IDTSVirtualInputColumn90 vColumn in vInputsales.VirtualInputColumnCollection)
            {
                // Call the SetUsageType method of the destination
                // to add each available virtual input column as an input column.
                destDesignTime.SetUsageType(
                   inputsales.ID, vInputsales, vColumn.LineageID, DTSUsageType.UT_READONLY);
            }
            //map external metadata to the inputcolumn
            int indexsales = 0;
            foreach (IDTSInputColumn90 inputColumn in inputsales.InputColumnCollection)
            {
                IDTSExternalMetadataColumn90 exMetaColumn = (IDTSExternalMetadataColumn90)inputsales.ExternalMetadataColumnCollection[indexsales++];
                inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
            }
 
 
 
            // Get the destination's default input and virtual input for others dept.
            IDTSInput90 inputothers = destinationOther.InputCollection[0];
            IDTSVirtualInput90 vInputothers = inputothers.GetVirtualInput();
            // Iterate through the virtual input column collection.
            foreach (IDTSVirtualInputColumn90 vColumn in vInputothers.VirtualInputColumnCollection)
            {
                // Call the SetUsageType method of the destination
                //  to add each available virtual input column as an input column.
                destOtherDesignTime.SetUsageType(
                   inputothers.ID, vInputothers, vColumn.LineageID, DTSUsageType.UT_READONLY);
            }
 
            //map external metadata to the inputcolumn
            int indexothers = 0;
            foreach (IDTSInputColumn90 inputColumn in inputothers.InputColumnCollection)
            {
                IDTSExternalMetadataColumn90 exMetaColumn = (IDTSExternalMetadataColumn90)inputothers.ExternalMetadataColumnCollection[indexothers++];
                inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
            }
 
 
 
 
            //-----------Data Flow Inner component ends-------------------
 
            // Precedence constraints from "Package Start Indicator" to "Load Employee Dim"
            PrecedenceConstraint PackageStartIndicatorTask_dataFlowTaskHost = package.PrecedenceConstraints.Add(package.Executables[@"Package Start Indicator"], package.Executables[@"Load Employee Dim"]);
            PackageStartIndicatorTask_dataFlowTaskHost.Name = @"Package Start Indicator" + @"Load Employee Dim";
 
 
            //To add Package End Indicator (Execute Sql task )
            TaskHost PackageEndIndicatorTask = (TaskHost)package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
            PackageEndIndicatorTask.Name = @"Package End Indicator";
            PackageEndIndicatorTask.Description = @"Execute SQL Task Description";
            XmlDocument PackageEndIndicatorTaskdoc = new XmlDocument();
            PackageEndIndicatorTaskdoc.LoadXml(@"<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.EndTransaction ?, ?"" SQLTask:ResultType=""ResultSetType_None"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ParameterBinding SQLTask:ParameterName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /><SQLTask:ParameterBinding SQLTask:ParameterName=""1"" SQLTask:DtsVariableName=""User::RowCountVar"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /></SQLTask:SqlTaskData>");
            ((IDTSComponentPersist)PackageEndIndicatorTask.InnerObject).LoadFromXML(PackageEndIndicatorTaskdoc.DocumentElement, null);
 
            // Precedence constraints from "Load Employee Dim" to "Package End Indicator"
            PrecedenceConstraint dataFlowTaskHost_PackageEndIndicatorTask = package.PrecedenceConstraints.Add(package.Executables[@"Load Employee Dim"], package.Executables[@"Package End Indicator"]);
            dataFlowTaskHost_PackageEndIndicatorTask.Name = @"Load Employee Dim" + @"Package End Indicator";
 
            //Saving the package
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
            app.SaveToXml(@"I:Site workSamplePackageSamplePackageSamplePackage.dtsx", package, null);
        }
    }
}

VB .Net Expand

[ Copy to Clipboard ] | [ View Source ]

Imports System
Imports System.Collections.Generic
Imports System.Text
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports System.Data.SqlClient
Imports System.Xml
 
 
'The below code will create a SSIS package with CONDITIONAL SPLIT TRANSFORMATION for SQL Server 2005
 
Module Module1
    Sub Main(ByVal args As String())
        'To Create a package named [Sample Package]
        Dim package As New Package()
        package.Name = "Sample Package (Conditional Split)"
        package.PackageType = DTSPackageType.DTSDesigner90
        package.VersionBuild = 1
 
        'To add Connection Manager to the package
        'For source database (OLTP)
        Dim OLTP As ConnectionManager = package.Connections.Add("OLEDB")
        OLTP.ConnectionString = "Data Source=.;Initial Catalog=OLTP;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"
        OLTP.Name = "LocalHost.OLTP"
        'For destination database (OLAP)
        Dim OLAP As ConnectionManager = package.Connections.Add("OLEDB")
        OLAP.ConnectionString = "Data Source=.;Initial Catalog=OLAP;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"
        OLAP.Name = "LocalHost.OLAP"
 
        'To add package variables
        Dim TransactionID As Variable = package.Variables.Add("TransactionID", False, "User", 0)
        TransactionID.Name = "TransactionID"
 
        Dim RowCountVar As Variable = package.Variables.Add("RowCountVar", False, "User", 0)
        RowCountVar.Name = "RowCountVar"
 
        'To add Package Start Indicator (Execute Sql task )
        Dim PackageStartIndicatorTask As TaskHost = DirectCast(package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"), TaskHost)
        PackageStartIndicatorTask.Name = "Package Start Indicator"
        PackageStartIndicatorTask.Description = "Execute SQL Task Description"
        Dim PackageStartIndicatorTaskdoc As New XmlDocument()
        PackageStartIndicatorTaskdoc.LoadXml("<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.InitTransaction"" SQLTask:ResultType=""ResultSetType_SingleRow"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ResultBinding SQLTask:ResultName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" /></SQLTask:SqlTaskData>")
        DirectCast(PackageStartIndicatorTask.InnerObject, IDTSComponentPersist).LoadFromXML(PackageStartIndicatorTaskdoc.DocumentElement, Nothing)
 
        'To add Load Employee Dim to the package [Data Flow Task]
        Dim dataFlowTaskHost As TaskHost = DirectCast(package.Executables.Add("DTS.Pipeline.1"), TaskHost)
        dataFlowTaskHost.Name = "Load Employee Dim"
        dataFlowTaskHost.FailPackageOnFailure = True
        dataFlowTaskHost.FailParentOnFailure = True
        dataFlowTaskHost.DelayValidation = False
        dataFlowTaskHost.Description = "Data Flow Task"
 
        '-----------Data Flow Inner component starts----------------
        Dim dataFlowTask As MainPipe = TryCast(dataFlowTaskHost.InnerObject, MainPipe)
        ' Source OLE DB connection manager to the package.
        Dim SconMgr As ConnectionManager = package.Connections("LocalHost.OLTP")
        ' Destination OLE DB connection manager to the package.
        Dim DconMgr As ConnectionManager = package.Connections("LocalHost.OLAP")
 
        ' Create and configure an OLE DB source component. 
        Dim source As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.[New]()
        source.ComponentClassID = "DTSAdapter.OLEDBSource.1"
 
        ' Create the design-time instance of the source.
        Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate()
        ' The ProvideComponentProperties method creates a default output.
        srcDesignTime.ProvideComponentProperties()
        source.Name = "Employee Dim from OLTP"
 
        ' Assign the connection manager.
        source.RuntimeConnectionCollection(0).ConnectionManagerID = SconMgr.ID
        source.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(SconMgr)
        ' Set the custom properties of the source.
        srcDesignTime.SetComponentProperty("AccessMode", 0)
        ' Mode 0 : OpenRowset / Table - View
        srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Employee_Dim]")
        ' Connect to the data source, and then update the metadata for the source.
        srcDesignTime.AcquireConnections(Nothing)
        srcDesignTime.ReinitializeMetaData()
        srcDesignTime.ReleaseConnections()
 
        'Add an Conditional Split Transformation
        Dim conditionalSplitComponent As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.[New]()
        conditionalSplitComponent.Name = "Conditional Split"
        conditionalSplitComponent.ComponentClassID = "DTSTransform.ConditionalSplit.1"
        Dim conditionalSplitDesignTime As CManagedComponentWrapper = conditionalSplitComponent.Instantiate()
        conditionalSplitDesignTime.ProvideComponentProperties()
        'conditionalSplitDesignTime.SetComponentProperty("VariableName", "RowCountVar");
        conditionalSplitDesignTime.AcquireConnections(Nothing)
        conditionalSplitDesignTime.ReinitializeMetaData()
        conditionalSplitDesignTime.ReleaseConnections()
 
        ' Create the path from source to Conditional Split Transformation.
        Dim pathSource_ConditionalSplit As IDTSPath90 = dataFlowTask.PathCollection.[New]()
        pathSource_ConditionalSplit.AttachPathAndPropagateNotifications(source.OutputCollection(0), conditionalSplitComponent.InputCollection(0))
 
 
 
        'Set properties for Conditional Split transformation (condition statments)
        Dim conditionalSplitSalesDeptOutput As IDTSOutput90 = conditionalSplitDesignTime.InsertOutput(DTSInsertPlacement.IP_BEFORE, conditionalSplitComponent.OutputCollection(0).ID)
        conditionalSplitSalesDeptOutput.Name = "Sales Dept"
        conditionalSplitSalesDeptOutput.Description = "Sales Dept"
        Dim conditionalsplitInput As IDTSInput90 = conditionalSplitComponent.InputCollection(0)
        Dim conditionalSplitVirtualInput As IDTSVirtualInput90 = conditionalsplitInput.GetVirtualInput()
        Dim conditionalSplitVirtualInputColumns As IDTSVirtualInputColumnCollection90 = conditionalSplitVirtualInput.VirtualInputColumnCollection
        'Here only the columns that are used in the expressions are going to be fetched in input collection
        Dim columnCount As Integer = conditionalSplitVirtualInputColumns.Count
        For i As Integer = 0 To columnCount - 1
            If conditionalSplitVirtualInputColumns(i).Name = "Department" Then
                conditionalSplitDesignTime.SetUsageType(conditionalsplitInput.ID, conditionalSplitVirtualInput, conditionalSplitVirtualInputColumns(i).LineageID, DTSUsageType.UT_READONLY)
            End If
        Next
        conditionalSplitDesignTime.SetOutputProperty(conditionalSplitSalesDeptOutput.ID, "FriendlyExpression", "[Department] == ""sales""")
 
 
        'Give a user friendly name to the default output
        conditionalSplitComponent.OutputCollection("Conditional Split Default Output").Name = "Other Depts"
        Dim conditionalSplitDefaultOutput As IDTSOutput90 = conditionalSplitComponent.OutputCollection("Other Depts")
 
 
 
        ' Create and configure an OLE DB destination component for Sales Dept.
        Dim destinationSales As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.[New]()
        destinationSales.ComponentClassID = "DTSAdapter.OLEDBDestination.1"
        ' Create the design-time instance of the destination.
        Dim destDesignTime As CManagedComponentWrapper = destinationSales.Instantiate()
        ' The ProvideComponentProperties method creates a default input.
        destDesignTime.ProvideComponentProperties()
        destinationSales.Name = "SalesEmployee Dim (OLAP)"
        ' Assign the connection manager.
        destinationSales.RuntimeConnectionCollection(0).ConnectionManagerID = DconMgr.ID
        destinationSales.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(DconMgr)
        ' Set the custom properties.
        destDesignTime.SetComponentProperty("AccessMode", 3)
        ' Mode 3 : OpenRowset Using FastLoad / Table - View fast load
        destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[SalesEmployeeDim]")
        destDesignTime.AcquireConnections(Nothing)
        destDesignTime.ReinitializeMetaData()
        destDesignTime.ReleaseConnections()
 
        ' Create and configure an OLE DB destination component for Other Dept.
        Dim destinationOther As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.[New]()
        destinationOther.ComponentClassID = "DTSAdapter.OLEDBDestination.1"
        ' Create the design-time instance of the destination.
        Dim destOtherDesignTime As CManagedComponentWrapper = destinationOther.Instantiate()
        ' The ProvideComponentProperties method creates a default input.
        destOtherDesignTime.ProvideComponentProperties()
        destinationOther.Name = "OthersEmployeeDim (OLAP)"
        ' Assign the connection manager.
        destinationOther.RuntimeConnectionCollection(0).ConnectionManagerID = DconMgr.ID
        destinationOther.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(DconMgr)
        ' Set the custom properties.
        destOtherDesignTime.SetComponentProperty("AccessMode", 3)
        ' Mode 3 : OpenRowset Using FastLoad / Table - View fast load
        destOtherDesignTime.SetComponentProperty("OpenRowset", "[dbo].[OthersEmployeeDim]")
        destOtherDesignTime.AcquireConnections(Nothing)
        destOtherDesignTime.ReinitializeMetaData()
        destOtherDesignTime.ReleaseConnections()
 
        ' Create the path from Condtional Split transformation to destination.
        Dim pathConditionalSplit_DestSales As IDTSPath90 = dataFlowTask.PathCollection.[New]()
        pathConditionalSplit_DestSales.Name = "Sales Dept"
        pathConditionalSplit_DestSales.AttachPathAndPropagateNotifications(conditionalSplitSalesDeptOutput, destinationSales.InputCollection(0))
 
        Dim pathConditionalSplit_DestOthers As IDTSPath90 = dataFlowTask.PathCollection.[New]()
        pathConditionalSplit_DestOthers.AttachPathAndPropagateNotifications(conditionalSplitDefaultOutput, destinationOther.InputCollection(0))
        pathConditionalSplit_DestOthers.Name = "Other Depts"
 
 
        ' Get the destination's default input and virtual input for sales dept.
        Dim inputsales As IDTSInput90 = destinationSales.InputCollection(0)
        Dim vInputsales As IDTSVirtualInput90 = inputsales.GetVirtualInput()
        ' Iterate through the virtual input column collection.
        For Each vColumn As IDTSVirtualInputColumn90 In vInputsales.VirtualInputColumnCollection
            ' Call the SetUsageType method of the destination
            ' to add each available virtual input column as an input column.
            destDesignTime.SetUsageType(inputsales.ID, vInputsales, vColumn.LineageID, DTSUsageType.UT_READONLY)
        Next
        'map external metadata to the inputcolumn
        Dim indexsales As Integer = 0
        For Each inputColumn As IDTSInputColumn90 In inputsales.InputColumnCollection
            Dim exMetaColumn As IDTSExternalMetadataColumn90 = DirectCast(inputsales.ExternalMetadataColumnCollection(indexsales), IDTSExternalMetadataColumn90)
            indexsales = indexsales + 1
            inputColumn.ExternalMetadataColumnID = exMetaColumn.ID
        Next
 
 
 
        ' Get the destination's default input and virtual input for others dept.
        Dim inputothers As IDTSInput90 = destinationOther.InputCollection(0)
        Dim vInputothers As IDTSVirtualInput90 = inputothers.GetVirtualInput()
        ' Iterate through the virtual input column collection.
        For Each vColumn As IDTSVirtualInputColumn90 In vInputothers.VirtualInputColumnCollection
            ' Call the SetUsageType method of the destination
            ' to add each available virtual input column as an input column.
            destOtherDesignTime.SetUsageType(inputothers.ID, vInputothers, vColumn.LineageID, DTSUsageType.UT_READONLY)
        Next
 
        'map external metadata to the inputcolumn
        Dim indexothers As Integer = 0
        For Each inputColumn As IDTSInputColumn90 In inputothers.InputColumnCollection
            Dim exMetaColumn As IDTSExternalMetadataColumn90 = DirectCast(inputothers.ExternalMetadataColumnCollection(indexothers), IDTSExternalMetadataColumn90)
            indexothers = indexothers + 1
            inputColumn.ExternalMetadataColumnID = exMetaColumn.ID
        Next
 
 
 
 
        '-----------Data Flow Inner component ends-------------------
 
        ' Precedence constraints from "Package Start Indicator" to "Load Employee Dim"
        Dim PackageStartIndicatorTask_dataFlowTaskHost As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Package Start Indicator"), package.Executables("Load Employee Dim"))
        PackageStartIndicatorTask_dataFlowTaskHost.Name = "Package Start Indicator" & "Load Employee Dim"
 
 
        'To add Package End Indicator (Execute Sql task )
        Dim PackageEndIndicatorTask As TaskHost = DirectCast(package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"), TaskHost)
        PackageEndIndicatorTask.Name = "Package End Indicator"
        PackageEndIndicatorTask.Description = "Execute SQL Task Description"
        Dim PackageEndIndicatorTaskdoc As New XmlDocument()
        PackageEndIndicatorTaskdoc.LoadXml("<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.EndTransaction ?, ?"" SQLTask:ResultType=""ResultSetType_None"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ParameterBinding SQLTask:ParameterName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /><SQLTask:ParameterBinding SQLTask:ParameterName=""1"" SQLTask:DtsVariableName=""User::RowCountVar"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /></SQLTask:SqlTaskData>")
        DirectCast(PackageEndIndicatorTask.InnerObject, IDTSComponentPersist).LoadFromXML(PackageEndIndicatorTaskdoc.DocumentElement, Nothing)
 
        ' Precedence constraints from "Load Employee Dim" to "Package End Indicator"
        Dim dataFlowTaskHost_PackageEndIndicatorTask As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Load Employee Dim"), package.Executables("Package End Indicator"))
        dataFlowTaskHost_PackageEndIndicatorTask.Name = "Load Employee Dim" & "Package End Indicator"
 
        'Saving the package
        Dim app As New Microsoft.SqlServer.Dts.Runtime.Application()
        app.SaveToXml("I:Site workSamplePackageSamplePackageSamplePackage.dtsx", package, Nothing)
    End Sub
End Module

Programming Integration Service in SQL Server 2008

C# .Net Expand

[ Copy to Clipboard ] | [ View Source ]

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Data.SqlClient;
using System.Xml;
 
namespace SamplePackageCSharp2008
{
    class Program
    {
        static void Main(string[] args)
        {
            //To Create a package named [Sample Package]
            Package package = new Package();
            package.Name = "Sample Package";
            package.PackageType = DTSPackageType.DTSDesigner100;
            package.VersionBuild = 1;
 
            //To add Connection Manager to the package
            //For source database (OLTP)
            ConnectionManager OLTP = package.Connections.Add("OLEDB");
            OLTP.ConnectionString = "Data Source=.;Initial Catalog=OLTP;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
            OLTP.Name = "LocalHost.OLTP";
            //For destination database (OLAP)
            ConnectionManager OLAP = package.Connections.Add("OLEDB");
            OLAP.ConnectionString = "Data Source=.;Initial Catalog=OLAP;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
            OLAP.Name = "LocalHost.OLAP";
 
            //To add package variables
            Variable TransactionID = package.Variables.Add("TransactionID", false, "User", 0);
            TransactionID.Name = @"TransactionID";
 
            Variable RowCountVar = package.Variables.Add("RowCountVar", false, "User", 0);
            RowCountVar.Name = @"RowCountVar";
 
            //To add Package Start Indicator (Execute Sql task )
            TaskHost PackageStartIndicatorTask = (TaskHost)package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
            PackageStartIndicatorTask.Name = @"Package Start Indicator";
            PackageStartIndicatorTask.Description = @"Execute SQL Task Description";
            XmlDocument PackageStartIndicatorTaskdoc = new XmlDocument();
            PackageStartIndicatorTaskdoc.LoadXml(@"<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.InitTransaction"" SQLTask:ResultType=""ResultSetType_SingleRow"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ResultBinding SQLTask:ResultName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" /></SQLTask:SqlTaskData>");
            ((IDTSComponentPersist)PackageStartIndicatorTask.InnerObject).LoadFromXML(PackageStartIndicatorTaskdoc.DocumentElement, null);
 
            //To add Load Employee Dim to the package [Data Flow Task]
            TaskHost dataFlowTaskHost = (TaskHost)package.Executables.Add("SSIS.Pipeline.2");
            dataFlowTaskHost.Name = @"Load Employee Dim";
            dataFlowTaskHost.FailPackageOnFailure = true;
            dataFlowTaskHost.FailParentOnFailure = true;
            dataFlowTaskHost.DelayValidation = false;
            dataFlowTaskHost.Description = @"Data Flow Task";
 
            //-----------Data Flow Inner component starts----------------
            MainPipe dataFlowTask = dataFlowTaskHost.InnerObject as MainPipe;
            // Source OLE DB connection manager to the package.
            ConnectionManager SconMgr = package.Connections["LocalHost.OLTP"];
            // Destination OLE DB connection manager to the package.
            ConnectionManager DconMgr = package.Connections["LocalHost.OLAP"];
 
            // Create and configure an OLE DB source component.  
            IDTSComponentMetaData100 source = dataFlowTask.ComponentMetaDataCollection.New();
            source.ComponentClassID = "DTSAdapter.OLEDBSource.2";
 
            // Create the design-time instance of the source.
            CManagedComponentWrapper srcDesignTime = source.Instantiate();
            // The ProvideComponentProperties method creates a default output.
            srcDesignTime.ProvideComponentProperties();
            source.Name = "Employee Dim from OLTP";
 
            // Assign the connection manager.
            source.RuntimeConnectionCollection[0].ConnectionManagerID = SconMgr.ID;
            source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(SconMgr);
            // Set the custom properties of the source.
            srcDesignTime.SetComponentProperty("AccessMode", 0); // Mode 0 : OpenRowset / Table - View
            srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Employee_Dim]");
            // Connect to the data source, and then update the metadata for the source.
            srcDesignTime.AcquireConnections(null);
            srcDesignTime.ReinitializeMetaData();
            srcDesignTime.ReleaseConnections();
 
            //Add an Conditional Split Transformation
            IDTSComponentMetaData100 conditionalSplitComponent = dataFlowTask.ComponentMetaDataCollection.New();
            conditionalSplitComponent.Name = "Conditional Split";
            conditionalSplitComponent.ComponentClassID = "DTSTransform.ConditionalSplit.2";
            CManagedComponentWrapper conditionalSplitDesignTime = conditionalSplitComponent.Instantiate();
            conditionalSplitDesignTime.ProvideComponentProperties();
            //conditionalSplitDesignTime.SetComponentProperty("VariableName", "RowCountVar");
            conditionalSplitDesignTime.AcquireConnections(null);
            conditionalSplitDesignTime.ReinitializeMetaData();
            conditionalSplitDesignTime.ReleaseConnections();
 
            // Create the path from source to Conditional Split Transformation.
            IDTSPath100 pathSource_ConditionalSplit = dataFlowTask.PathCollection.New();
            pathSource_ConditionalSplit.AttachPathAndPropagateNotifications(source.OutputCollection[0], conditionalSplitComponent.InputCollection[0]);
 
 
 
            //Set properties for Conditional Split transformation (condition statments)
            IDTSOutput100 conditionalSplitSalesDeptOutput = conditionalSplitDesignTime.InsertOutput(DTSInsertPlacement.IP_BEFORE, conditionalSplitComponent.OutputCollection[0].ID);
            conditionalSplitSalesDeptOutput.Name = "Sales Dept";
            conditionalSplitSalesDeptOutput.Description = "Sales Dept";
            IDTSInput100 conditionalsplitInput = conditionalSplitComponent.InputCollection[0];
            IDTSVirtualInput100 conditionalSplitVirtualInput = conditionalsplitInput.GetVirtualInput();
            IDTSVirtualInputColumnCollection100 conditionalSplitVirtualInputColumns = conditionalSplitVirtualInput.VirtualInputColumnCollection;
            //Here only the columns that are used in the expressions are going to be fetched in input collection
            int columnCount = conditionalSplitVirtualInputColumns.Count;
            for (int i = 0; i < columnCount; i++)
            {
                if (conditionalSplitVirtualInputColumns[i].Name == "Department")
                    conditionalSplitDesignTime.SetUsageType(conditionalsplitInput.ID, conditionalSplitVirtualInput, conditionalSplitVirtualInputColumns[i].LineageID, DTSUsageType.UT_READONLY);
            }
            conditionalSplitDesignTime.SetOutputProperty(conditionalSplitSalesDeptOutput.ID, "FriendlyExpression", "[Department] == "sales"");
 
 
            //Give a user friendly name to the default output
            conditionalSplitComponent.OutputCollection["Conditional Split Default Output"].Name = "Other Depts";
            IDTSOutput100 conditionalSplitDefaultOutput = conditionalSplitComponent.OutputCollection["Other Depts"];
 
 
 
            // Create and configure an OLE DB destination component for Sales Dept.
            IDTSComponentMetaData100 destinationSales = dataFlowTask.ComponentMetaDataCollection.New();
            destinationSales.ComponentClassID = "DTSAdapter.OLEDBDestination.2";
            // Create the design-time instance of the destination.
            CManagedComponentWrapper destDesignTime = destinationSales.Instantiate();
            // The ProvideComponentProperties method creates a default input.
            destDesignTime.ProvideComponentProperties();
            destinationSales.Name = "SalesEmployee Dim (OLAP)";
            // Assign the connection manager.
            destinationSales.RuntimeConnectionCollection[0].ConnectionManagerID = DconMgr.ID;
            destinationSales.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(DconMgr);
            // Set the custom properties.
            destDesignTime.SetComponentProperty("AccessMode", 3); // Mode 3 : OpenRowset Using FastLoad  / Table - View fast load
            destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[SalesEmployeeDim]");
            destDesignTime.AcquireConnections(null);
            destDesignTime.ReinitializeMetaData();
            destDesignTime.ReleaseConnections();
 
            // Create and configure an OLE DB destination component for Other Dept.
            IDTSComponentMetaData100 destinationOther = dataFlowTask.ComponentMetaDataCollection.New();
            destinationOther.ComponentClassID = "DTSAdapter.OLEDBDestination.2";
            // Create the design-time instance of the destination.
            CManagedComponentWrapper destOtherDesignTime = destinationOther.Instantiate();
            // The ProvideComponentProperties method creates a default input.
            destOtherDesignTime.ProvideComponentProperties();
            destinationOther.Name = "OthersEmployeeDim (OLAP)";
            // Assign the connection manager.
            destinationOther.RuntimeConnectionCollection[0].ConnectionManagerID = DconMgr.ID;
            destinationOther.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(DconMgr);
            // Set the custom properties.
            destOtherDesignTime.SetComponentProperty("AccessMode", 3); // Mode 3 : OpenRowset Using FastLoad  / Table - View fast load
            destOtherDesignTime.SetComponentProperty("OpenRowset", "[dbo].[OthersEmployeeDim]");
            destOtherDesignTime.AcquireConnections(null);
            destOtherDesignTime.ReinitializeMetaData();
            destOtherDesignTime.ReleaseConnections();
 
            // Create the path from Condtional Split transformation to destination.
            IDTSPath100 pathConditionalSplit_DestSales = dataFlowTask.PathCollection.New();
            pathConditionalSplit_DestSales.Name = "Sales Dept";
            pathConditionalSplit_DestSales.AttachPathAndPropagateNotifications(conditionalSplitSalesDeptOutput, destinationSales.InputCollection[0]);
 
            IDTSPath100 pathConditionalSplit_DestOthers = dataFlowTask.PathCollection.New();
            pathConditionalSplit_DestOthers.AttachPathAndPropagateNotifications(conditionalSplitDefaultOutput, destinationOther.InputCollection[0]);
            pathConditionalSplit_DestOthers.Name = "Other Depts";
 
 
            // Get the destination's default input and virtual input for sales dept.
            IDTSInput100 inputsales = destinationSales.InputCollection[0];
            IDTSVirtualInput100 vInputsales = inputsales.GetVirtualInput();
            // Iterate through the virtual input column collection.
            foreach (IDTSVirtualInputColumn100 vColumn in vInputsales.VirtualInputColumnCollection)
            {
                // Call the SetUsageType method of the destination
                // to add each available virtual input column as an input column.
                destDesignTime.SetUsageType(
                   inputsales.ID, vInputsales, vColumn.LineageID, DTSUsageType.UT_READONLY);
            }
            //map external metadata to the inputcolumn
            int indexsales = 0;
            foreach (IDTSInputColumn100 inputColumn in inputsales.InputColumnCollection)
            {
                IDTSExternalMetadataColumn100 exMetaColumn = (IDTSExternalMetadataColumn100)inputsales.ExternalMetadataColumnCollection[indexsales++];
                inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
            }
 
 
 
            // Get the destination's default input and virtual input for others dept.
            IDTSInput100 inputothers = destinationOther.InputCollection[0];
            IDTSVirtualInput100 vInputothers = inputothers.GetVirtualInput();
            // Iterate through the virtual input column collection.
            foreach (IDTSVirtualInputColumn100 vColumn in vInputothers.VirtualInputColumnCollection)
            {
                // Call the SetUsageType method of the destination
                //  to add each available virtual input column as an input column.
                destOtherDesignTime.SetUsageType(
                   inputothers.ID, vInputothers, vColumn.LineageID, DTSUsageType.UT_READONLY);
            }
 
            //map external metadata to the inputcolumn
            int indexothers = 0;
            foreach (IDTSInputColumn100 inputColumn in inputothers.InputColumnCollection)
            {
                IDTSExternalMetadataColumn100 exMetaColumn = (IDTSExternalMetadataColumn100)inputothers.ExternalMetadataColumnCollection[indexothers++];
                inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
            }
 
 
 
 
            //-----------Data Flow Inner component ends-------------------
 
            // Precedence constraints from "Package Start Indicator" to "Load Employee Dim"
            PrecedenceConstraint PackageStartIndicatorTask_dataFlowTaskHost = package.PrecedenceConstraints.Add(package.Executables[@"Package Start Indicator"], package.Executables[@"Load Employee Dim"]);
            PackageStartIndicatorTask_dataFlowTaskHost.Name = @"Package Start Indicator" + @"Load Employee Dim";
 
 
            //To add Package End Indicator (Execute Sql task )
            TaskHost PackageEndIndicatorTask = (TaskHost)package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
            PackageEndIndicatorTask.Name = @"Package End Indicator";
            PackageEndIndicatorTask.Description = @"Execute SQL Task Description";
            XmlDocument PackageEndIndicatorTaskdoc = new XmlDocument();
            PackageEndIndicatorTaskdoc.LoadXml(@"<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.EndTransaction ?, ?"" SQLTask:ResultType=""ResultSetType_None"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ParameterBinding SQLTask:ParameterName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /><SQLTask:ParameterBinding SQLTask:ParameterName=""1"" SQLTask:DtsVariableName=""User::RowCountVar"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /></SQLTask:SqlTaskData>");
            ((IDTSComponentPersist)PackageEndIndicatorTask.InnerObject).LoadFromXML(PackageEndIndicatorTaskdoc.DocumentElement, null);
 
            // Precedence constraints from "Load Employee Dim" to "Package End Indicator"
            PrecedenceConstraint dataFlowTaskHost_PackageEndIndicatorTask = package.PrecedenceConstraints.Add(package.Executables[@"Load Employee Dim"], package.Executables[@"Package End Indicator"]);
            dataFlowTaskHost_PackageEndIndicatorTask.Name = @"Load Employee Dim" + @"Package End Indicator";
 
            //Saving the package
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
            app.SaveToXml(@"I:Site workSamplePackage 2008SamplePackageSamplePackage.dtsx", package, null);
        }
    }
}

VB .Net Expand

[ Copy to Clipboard ] | [ View Source ]

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports System.Data.SqlClient
Imports System.Xml
 
'The below code will create a SSIS package for SQL Server 2008 with CONDITIONAL SPLIT TRANSFORMATION
Module Module1
 
    Sub Main(ByVal args As String())
        'To Create a package named [Sample Package]
        Dim package As New Package()
        package.Name = "Sample Package"
        package.PackageType = DTSPackageType.DTSDesigner100
        package.VersionBuild = 1
 
        'To add Connection Manager to the package
        'For source database (OLTP)
        Dim OLTP As ConnectionManager = package.Connections.Add("OLEDB")
        OLTP.ConnectionString = "Data Source=.;Initial Catalog=OLTP;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
        OLTP.Name = "LocalHost.OLTP"
        'For destination database (OLAP)
        Dim OLAP As ConnectionManager = package.Connections.Add("OLEDB")
        OLAP.ConnectionString = "Data Source=.;Initial Catalog=OLAP;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
        OLAP.Name = "LocalHost.OLAP"
 
        'To add package variables
        Dim TransactionID As Variable = package.Variables.Add("TransactionID", False, "User", 0)
        TransactionID.Name = "TransactionID"
 
        Dim RowCountVar As Variable = package.Variables.Add("RowCountVar", False, "User", 0)
        RowCountVar.Name = "RowCountVar"
 
        'To add Package Start Indicator (Execute Sql task )
        Dim PackageStartIndicatorTask As TaskHost = DirectCast(package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"), TaskHost)
        PackageStartIndicatorTask.Name = "Package Start Indicator"
        PackageStartIndicatorTask.Description = "Execute SQL Task Description"
        Dim PackageStartIndicatorTaskdoc As New XmlDocument()
        PackageStartIndicatorTaskdoc.LoadXml("<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.InitTransaction"" SQLTask:ResultType=""ResultSetType_SingleRow"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ResultBinding SQLTask:ResultName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" /></SQLTask:SqlTaskData>")
        DirectCast(PackageStartIndicatorTask.InnerObject, IDTSComponentPersist).LoadFromXML(PackageStartIndicatorTaskdoc.DocumentElement, Nothing)
 
        'To add Load Employee Dim to the package [Data Flow Task]
        Dim dataFlowTaskHost As TaskHost = DirectCast(package.Executables.Add("SSIS.Pipeline.2"), TaskHost)
        dataFlowTaskHost.Name = "Load Employee Dim"
        dataFlowTaskHost.FailPackageOnFailure = True
        dataFlowTaskHost.FailParentOnFailure = True
        dataFlowTaskHost.DelayValidation = False
        dataFlowTaskHost.Description = "Data Flow Task"
 
        '-----------Data Flow Inner component starts----------------
        Dim dataFlowTask As MainPipe = TryCast(dataFlowTaskHost.InnerObject, MainPipe)
        ' Source OLE DB connection manager to the package.
        Dim SconMgr As ConnectionManager = package.Connections("LocalHost.OLTP")
        ' Destination OLE DB connection manager to the package.
        Dim DconMgr As ConnectionManager = package.Connections("LocalHost.OLAP")
 
        ' Create and configure an OLE DB source component. 
        Dim source As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection.[New]()
        source.ComponentClassID = "DTSAdapter.OLEDBSource.2"
 
        ' Create the design-time instance of the source.
        Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate()
        ' The ProvideComponentProperties method creates a default output.
        srcDesignTime.ProvideComponentProperties()
        source.Name = "Employee Dim from OLTP"
 
        ' Assign the connection manager.
        source.RuntimeConnectionCollection(0).ConnectionManagerID = SconMgr.ID
        source.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.GetExtendedInterface(SconMgr)
        ' Set the custom properties of the source.
        srcDesignTime.SetComponentProperty("AccessMode", 0)
        ' Mode 0 : OpenRowset / Table - View
        srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Employee_Dim]")
        ' Connect to the data source, and then update the metadata for the source.
        srcDesignTime.AcquireConnections(Nothing)
        srcDesignTime.ReinitializeMetaData()
        srcDesignTime.ReleaseConnections()
 
        'Add an Conditional Split Transformation
        Dim conditionalSplitComponent As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection.[New]()
        conditionalSplitComponent.Name = "Conditional Split"
        conditionalSplitComponent.ComponentClassID = "DTSTransform.ConditionalSplit.2"
        Dim conditionalSplitDesignTime As CManagedComponentWrapper = conditionalSplitComponent.Instantiate()
        conditionalSplitDesignTime.ProvideComponentProperties()
        'conditionalSplitDesignTime.SetComponentProperty("VariableName", "RowCountVar");
        conditionalSplitDesignTime.AcquireConnections(Nothing)
        conditionalSplitDesignTime.ReinitializeMetaData()
        conditionalSplitDesignTime.ReleaseConnections()
 
        ' Create the path from source to Conditional Split Transformation.
        Dim pathSource_ConditionalSplit As IDTSPath100 = dataFlowTask.PathCollection.[New]()
        pathSource_ConditionalSplit.AttachPathAndPropagateNotifications(source.OutputCollection(0), conditionalSplitComponent.InputCollection(0))
 
 
 
        'Set properties for Conditional Split transformation (condition statments)
        Dim conditionalSplitSalesDeptOutput As IDTSOutput100 = conditionalSplitDesignTime.InsertOutput(DTSInsertPlacement.IP_BEFORE, conditionalSplitComponent.OutputCollection(0).ID)
        conditionalSplitSalesDeptOutput.Name = "Sales Dept"
        conditionalSplitSalesDeptOutput.Description = "Sales Dept"
        Dim conditionalsplitInput As IDTSInput100 = conditionalSplitComponent.InputCollection(0)
        Dim conditionalSplitVirtualInput As IDTSVirtualInput100 = conditionalsplitInput.GetVirtualInput()
        Dim conditionalSplitVirtualInputColumns As IDTSVirtualInputColumnCollection100 = conditionalSplitVirtualInput.VirtualInputColumnCollection
        'Here only the columns that are used in the expressions are going to be fetched in input collection
        Dim columnCount As Integer = conditionalSplitVirtualInputColumns.Count
        For i As Integer = 0 To columnCount - 1
            If conditionalSplitVirtualInputColumns(i).Name = "Department" Then
                conditionalSplitDesignTime.SetUsageType(conditionalsplitInput.ID, conditionalSplitVirtualInput, conditionalSplitVirtualInputColumns(i).LineageID, DTSUsageType.UT_READONLY)
            End If
        Next
        conditionalSplitDesignTime.SetOutputProperty(conditionalSplitSalesDeptOutput.ID, "FriendlyExpression", "[Department] == ""sales""")
 
 
        'Give a user friendly name to the default output
        conditionalSplitComponent.OutputCollection("Conditional Split Default Output").Name = "Other Depts"
        Dim conditionalSplitDefaultOutput As IDTSOutput100 = conditionalSplitComponent.OutputCollection("Other Depts")
 
 
 
        ' Create and configure an OLE DB destination component for Sales Dept.
        Dim destinationSales As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection.[New]()
        destinationSales.ComponentClassID = "DTSAdapter.OLEDBDestination.2"
        ' Create the design-time instance of the destination.
        Dim destDesignTime As CManagedComponentWrapper = destinationSales.Instantiate()
        ' The ProvideComponentProperties method creates a default input.
        destDesignTime.ProvideComponentProperties()
        destinationSales.Name = "SalesEmployee Dim (OLAP)"
        ' Assign the connection manager.
        destinationSales.RuntimeConnectionCollection(0).ConnectionManagerID = DconMgr.ID
        destinationSales.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.GetExtendedInterface(DconMgr)
        ' Set the custom properties.
        destDesignTime.SetComponentProperty("AccessMode", 3)
        ' Mode 3 : OpenRowset Using FastLoad / Table - View fast load
        destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[SalesEmployeeDim]")
        destDesignTime.AcquireConnections(Nothing)
        destDesignTime.ReinitializeMetaData()
        destDesignTime.ReleaseConnections()
 
        ' Create and configure an OLE DB destination component for Other Dept.
        Dim destinationOther As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection.[New]()
        destinationOther.ComponentClassID = "DTSAdapter.OLEDBDestination.2"
        ' Create the design-time instance of the destination.
        Dim destOtherDesignTime As CManagedComponentWrapper = destinationOther.Instantiate()
        ' The ProvideComponentProperties method creates a default input.
        destOtherDesignTime.ProvideComponentProperties()
        destinationOther.Name = "OthersEmployeeDim (OLAP)"
        ' Assign the connection manager.
        destinationOther.RuntimeConnectionCollection(0).ConnectionManagerID = DconMgr.ID
        destinationOther.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.GetExtendedInterface(DconMgr)
        ' Set the custom properties.
        destOtherDesignTime.SetComponentProperty("AccessMode", 3)
        ' Mode 3 : OpenRowset Using FastLoad / Table - View fast load
        destOtherDesignTime.SetComponentProperty("OpenRowset", "[dbo].[OthersEmployeeDim]")
        destOtherDesignTime.AcquireConnections(Nothing)
        destOtherDesignTime.ReinitializeMetaData()
        destOtherDesignTime.ReleaseConnections()
 
        ' Create the path from Condtional Split transformation to destination.
        Dim pathConditionalSplit_DestSales As IDTSPath100 = dataFlowTask.PathCollection.[New]()
        pathConditionalSplit_DestSales.Name = "Sales Dept"
        pathConditionalSplit_DestSales.AttachPathAndPropagateNotifications(conditionalSplitSalesDeptOutput, destinationSales.InputCollection(0))
 
        Dim pathConditionalSplit_DestOthers As IDTSPath100 = dataFlowTask.PathCollection.[New]()
        pathConditionalSplit_DestOthers.AttachPathAndPropagateNotifications(conditionalSplitDefaultOutput, destinationOther.InputCollection(0))
        pathConditionalSplit_DestOthers.Name = "Other Depts"
 
 
        ' Get the destination's default input and virtual input for sales dept.
        Dim inputsales As IDTSInput100 = destinationSales.InputCollection(0)
        Dim vInputsales As IDTSVirtualInput100 = inputsales.GetVirtualInput()
        ' Iterate through the virtual input column collection.
        For Each vColumn As IDTSVirtualInputColumn100 In vInputsales.VirtualInputColumnCollection
            ' Call the SetUsageType method of the destination
            ' to add each available virtual input column as an input column.
            destDesignTime.SetUsageType(inputsales.ID, vInputsales, vColumn.LineageID, DTSUsageType.UT_READONLY)
        Next
        'map external metadata to the inputcolumn
        Dim indexsales As Integer = 0
        For Each inputColumn As IDTSInputColumn100 In inputsales.InputColumnCollection
            Dim exMetaColumn As IDTSExternalMetadataColumn100 = DirectCast(inputsales.ExternalMetadataColumnCollection(indexsales), IDTSExternalMetadataColumn100)
            indexsales = indexsales + 1
            inputColumn.ExternalMetadataColumnID = exMetaColumn.ID
        Next
 
 
 
        ' Get the destination's default input and virtual input for others dept.
        Dim inputothers As IDTSInput100 = destinationOther.InputCollection(0)
        Dim vInputothers As IDTSVirtualInput100 = inputothers.GetVirtualInput()
        ' Iterate through the virtual input column collection.
        For Each vColumn As IDTSVirtualInputColumn100 In vInputothers.VirtualInputColumnCollection
            ' Call the SetUsageType method of the destination
            ' to add each available virtual input column as an input column.
            destOtherDesignTime.SetUsageType(inputothers.ID, vInputothers, vColumn.LineageID, DTSUsageType.UT_READONLY)
        Next
 
        'map external metadata to the inputcolumn
        Dim indexothers As Integer = 0
        For Each inputColumn As IDTSInputColumn100 In inputothers.InputColumnCollection
            Dim exMetaColumn As IDTSExternalMetadataColumn100 = DirectCast(inputothers.ExternalMetadataColumnCollection(indexothers), IDTSExternalMetadataColumn100)
            indexothers = indexothers + 1
            inputColumn.ExternalMetadataColumnID = exMetaColumn.ID
        Next
 
 
 
 
        '-----------Data Flow Inner component ends-------------------
 
        ' Precedence constraints from "Package Start Indicator" to "Load Employee Dim"
        Dim PackageStartIndicatorTask_dataFlowTaskHost As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Package Start Indicator"), package.Executables("Load Employee Dim"))
        PackageStartIndicatorTask_dataFlowTaskHost.Name = "Package Start Indicator" & "Load Employee Dim"
 
 
        'To add Package End Indicator (Execute Sql task )
        Dim PackageEndIndicatorTask As TaskHost = DirectCast(package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"), TaskHost)
        PackageEndIndicatorTask.Name = "Package End Indicator"
        PackageEndIndicatorTask.Description = "Execute SQL Task Description"
        Dim PackageEndIndicatorTaskdoc As New XmlDocument()
        PackageEndIndicatorTaskdoc.LoadXml("<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.EndTransaction ?, ?"" SQLTask:ResultType=""ResultSetType_None"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ParameterBinding SQLTask:ParameterName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /><SQLTask:ParameterBinding SQLTask:ParameterName=""1"" SQLTask:DtsVariableName=""User::RowCountVar"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /></SQLTask:SqlTaskData>")
        DirectCast(PackageEndIndicatorTask.InnerObject, IDTSComponentPersist).LoadFromXML(PackageEndIndicatorTaskdoc.DocumentElement, Nothing)
 
        ' Precedence constraints from "Load Employee Dim" to "Package End Indicator"
        Dim dataFlowTaskHost_PackageEndIndicatorTask As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Load Employee Dim"), package.Executables("Package End Indicator"))
        dataFlowTaskHost_PackageEndIndicatorTask.Name = "Load Employee Dim" & "Package End Indicator"
 
        'Saving the package
        Dim app As New Microsoft.SqlServer.Dts.Runtime.Application()
        app.SaveToXml("I:Site workSamplePackage 2008SamplePackageSamplePackage.dtsx", package, Nothing)
    End Sub
End Module

Like the above we can create any number of outputs with an expression that evaluates to a Boolean for each condition.

To make your work more easy and effortless, SQL Lion team comes up with a unique tool named “SSIS Component Explorer” that will help you in retrieving the Creation Name, Component Class ID, etc about any component like Control Flow Tasks, Data Flow Transformations, Connections Managers, Log Providers, etc in SSIS package. It also provides code snippets for each component.
Download the free version from here.

Reference:
http://msdn.microsoft.com/en-us/library/ms137886.aspx

Categories: SSIS, SSIS programming

Comments (2) Trackbacks (0) Leave a comment Trackback

Jorge Novo

December 22nd, 2011 at 22:36 | #1

Reply | Quote

Thanks for your examples and code, you help me understand the proper way of doing the Conditional Split.
Assaf Shalem

February 8th, 2013 at 22:50 | #2

Reply | Quote

Many Thanks, great articles!!!!!!

No trackbacks yet.

Optimizing TempDB in SQL server 2005 SSIS Programming Basic

All Posts

Faster Extraction & Loading by SSIS: Best practices and innovative ideas to make SSIS package faster, simpler and more productive.
SQL Server 2005 Vs SQL Server 2008: Briefly describes the upgraded features of SQL server 2008 over SQL server 2005.
SSIS Programming Basic: Programming Integration service by C# .Net or VB .Net.
Programming Conditional Split Transformation: Iincoming data can be split into multiple outputs by Integration Programming.
Optimizing TempDB (SQL 2005): Optimization techniques for tempdb database to boost database performance.
Expert Logger: Batch logging of all the SSIS DTS packages.
Monitoring Tempdb (SQL 2005): Provides various parameters and performance majors to optimize and monitor tempdb database.
Programming Lookup Transformation: Programming SSIS Lookup transformation using C# .Net or VB .Net in both SQL Server 2005/2008.
GROUP BY Clause: Enhanced features (SQL Server 2005 Vs 2008), including CUBE, ROLLUP, GROUPING SETS, GROUPING_ID, etc.
Programming SSIS Logging: Programming logging mechanism in SSIS by using C# .net and VB .Net.
Delete Duplicate Records: Describes various ways to handle redundancy in the database tables.
SSIS Component Explorer: Unique utility to develop SSIS package programmatically with more ease and effectiveness.
Programming Sequence Container: Sequence container in SSIS package by C# .Net and VB .Net.
Duplicate Remover: Tool to search and delete duplicate data (rows) in your SQL Server database.
Programming For Loop Container: For Loop Container in SSIS package by C# .Net and VB .Net.
Extreme Catalog Pro: Tool for maintaining and organizing collection of your CD-ROMs, audio CDs, hard disks, removable disks, etc.
Enumerating Excel Files: Usage of Foreach Loop Container with enumeration of Excel Files in SSIS
Enumerating Excel Sheets: Foreach Loop Container with Foreach ADO.NET Schema Rowset Enumerator in SSIS
TRANSACTION Isolation Levels: All Transaction Isolation levels including detail description of database locks
SQL Server Indexes (Part 1): Concept of indexes with real background operation carried on during index operations.
SQL Server Indexes (Part 2): Different types of indexes with pros and cons at different levels like Cluster index Vs. Non-Cluster index.
SQL Server Indexes (Part 3): Optimizing techniques for better usage of index in order to boost performance of the database, monitoring indexes and best practices for indexing.
SQL Driver Issues Resolver: Tool to remove discrepancy in SQL Server 2005 / 2008 like Missing foreach enumerators (File enumerator, Item enumerator), Missing OLE DB or Ado .Net Drivers.

SQL Lion

Programming Conditional Split Transformation

All Posts

Recent Posts

Categories

Archives

Recent Comments