Programming Sequence Container

Home > SSIS, SSIS programming > Programming Sequence Container

Programming Sequence Container

June 1st, 2009 Arun Mallick Leave a comment Go to comments

While building an ETL package, it’s very important to control the flow of work as per the requirement. And this can be done by using a SSIS component called Sequence container. The sequence container is a part of Control Flow and a sub part of the global container i.e. Control Flow container. Here we can set the flow of our package by precedence constraints.

The Sequence Container is basically used for grouping same type of tasks and to control them under a separate group. It can group the whole contents of the package into multiple separate control flows. The sequence container can be nested to any level to give more clarity to the process flow.

Here in this article, we will see the steps to control the tasks using Sequence container as well as how to do the same by programming Integration Services.

It comes with some additional plus points to control the components of the package like:

While debugging, one can disable or enable the whole set of tasks under a sequence container at a time.
One can set properties to all the tasks under a sequence container by simply setting the properties on the master sequence container.
Sequence container has its own scope, so the variables inside a sequence container have different scope than the variables outside.
It can be used along with Transaction property to have more granular control on the transaction done by the package. For example, if there are two tasks inside a sequence container, one task for withdrawing money from an account and another for depositing, then we can configure the sequence container to ensure that the task for withdrawing money will be rolled back if the task for depositing one fails.

Let’s have a simple example to see how the sequence containers are useful to guide the flow of the package. Here, our requirement is to get the information about the cars in separate tables categorized by fuel types. For details about the tasks used in this example, please refer to the earlier article “SSIS Programming Basic“.

The logic to implement the same and build the SSIS package is given below:

programmingsequencecont1

Here, we have two Execute SQL Tasks, one to log the starting information of the package and the other to log the package ending information. In between, we used a sequence container to hold the data flow tasks of both the categories. Each data flow task is surrounded by its sequence container. We can add any no. of tasks inside the sequence container and can guide the execution of these tasks by using precedence constraints. Here, both the data flow tasks will run parallel in order to increase the performance of the package.

Here the source table named “dbo.Car_Info” has the following data:

CarCompany	CarBodyType	CarName	EngineType
Maruti	small	Maruti-800	petrol
Maruti	small	Waganor Duo	petrol
Honda	sedan	City	petrol
TATA	small	indica	diesel
Mahindra	SUV	Scorpio	diesel
TATA	SUV	Sumo	diesel
Maruti	sedan	SX4	petrol
Maruti	sedan	Swift-Dzire	diesel
TATA	small	Nano	petrol

And after executing the above package, our destination tables have the following data:

Table: dbo.DieselCars

CarCompany	CarBodyType	CarName	EngineType
TATA	small	indica	diesel
Mahindra	SUV	Scorpio	diesel
TATA	SUV	Sumo	diesel
Maruti	sedan	Swift-Dzire	diesel

Table: dbo.PetrolCars

CarCompany	CarBodyType	CarName	EngineType
Maruti	small	Maruti-800	petrol
Maruti	small	Waganor Duo	petrol
Honda	sedan	City	petrol
Maruti	sedan	SX4	petrol
TATA	small	Nano	petrol

So we successfully separated the information about the cars as per the fuel type. Let’s do the same in Integration programming. The code below is done in both C# .net and VB .net as well as in both the versions of SSIS i.e. SQL Server 2005 and SQL Server 2008.

Here, we have to create the package first, followed by creation of tasks for start and end indicator of the package. And then the sequence containers have to be created as per the logic where the data flow tasks are embedded. The logic to extract data separately is given inside the data flow tasks itself.

The creation name for Sequence container is “STOCK:SEQUENCE” (Both for SQL Server 2005 and SQL Server 2008).

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

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 Sequence Container 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";
            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";
 
            /*////////////////////////////////////////////////////////
            ////////   First Level- Outer Scope   //////////////////
            ///////////////////////////////////////////////////////*/
 
            //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);
 
            //Global Sequence Container (Sequence Container)
            Sequence GlobalSequenceContainer = (Sequence)package.Executables.Add("STOCK:SEQUENCE");
            GlobalSequenceContainer.FailPackageOnFailure = true;
            GlobalSequenceContainer.FailParentOnFailure = true;
            GlobalSequenceContainer.Name = @"Global Sequence Container";
            GlobalSequenceContainer.Description = @"This is the outer sequence container.";
 
            //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);
 
 
            //To set the precedence for the above tasks and containers
            //Precedence from "Package Start Indicator" to "Global Sequence Container"
            PrecedenceConstraint PkgStrtInd_GloSeqCont = package.PrecedenceConstraints.Add(package.Executables[@"Package Start Indicator"], package.Executables[@"Global Sequence Container"]);
            PkgStrtInd_GloSeqCont.Name = @"PkgStrtInd_GloSeqCont";
 
            // Precedence from "Global Sequence Container" to "Package End Indicator"
            PrecedenceConstraint GloSeqCont_PkgEndInd = package.PrecedenceConstraints.Add(package.Executables[@"Global Sequence Container"], package.Executables[@"Package End Indicator"]);
            GloSeqCont_PkgEndInd.Name = @"GloSeqCont_PkgEndInd";
 
 
 
 
            /*////////////////////////////////////////////////////////
            ////////   Second Level- Inner Scope   //////////////////
            ///////////////////////////////////////////////////////*/
 
 
            // To add "Load Petrol Type Cars" (sequence container) inside "Global Sequence Container"
            Sequence LoadPetrolTypeCarsSequenceContainer = (Sequence)GlobalSequenceContainer.Executables.Add("STOCK:SEQUENCE");
            LoadPetrolTypeCarsSequenceContainer.FailPackageOnFailure = true;
            LoadPetrolTypeCarsSequenceContainer.FailParentOnFailure = true;
            LoadPetrolTypeCarsSequenceContainer.Name = @"Load Petrol Type Cars";
            LoadPetrolTypeCarsSequenceContainer.Description = @"This is the inner sequence container to load Petrol Cars.";
 
            // To add "Load Diesel Type Cars" (sequence container) inside "Global Sequence Container"
            Sequence LoadDieselTypeCarsSequenceContainer = (Sequence)GlobalSequenceContainer.Executables.Add("STOCK:SEQUENCE");
            LoadDieselTypeCarsSequenceContainer.FailPackageOnFailure = true;
            LoadDieselTypeCarsSequenceContainer.FailParentOnFailure = true;
            LoadDieselTypeCarsSequenceContainer.Name = @"Load Diesel Type Cars";
            LoadDieselTypeCarsSequenceContainer.Description = @"This is the inner sequence container to load Diesel Cars.";
 
            // The above two containers will run parallel, so there is no need to put precedence constrainsts between them.
 
            /*///////////////////////////////////////////////////////////////////////////
            //////// Third Level- Data flow tasks inside inner sequence containers //////
            ///////////////////////////////////////////////////////////////////////////*/
 
            //To add "Load Cars(Petrol)"(Data Flow Task) inside "Load Petrol Type Cars" (sequence container)
            TaskHost PetrolCarsDataFlowTask = (TaskHost)LoadPetrolTypeCarsSequenceContainer.Executables.Add("DTS.Pipeline.1");
            PetrolCarsDataFlowTask.Name = @"Load Cars(Petrol)";
            PetrolCarsDataFlowTask.FailPackageOnFailure = true;
            PetrolCarsDataFlowTask.FailParentOnFailure = true;
            PetrolCarsDataFlowTask.DelayValidation = false;
            PetrolCarsDataFlowTask.Description = @"Data Flow Task to load Petrol Cars";
 
            //-----------Data Flow Inner component starts----------------
            MainPipe PetrolCarsMainPipeDataFlowTask = PetrolCarsDataFlowTask.InnerObject as MainPipe;
            // Source OLE DB connection manager to the package.
            ConnectionManager PetrolCarSconMgr = package.Connections["LocalHost.OLTP"];
            // Destination OLE DB connection manager to the package.
            ConnectionManager PetrolCarDconMgr = package.Connections["LocalHost.OLAP"];
 
            // Create and configure an OLE DB source component.  
            IDTSComponentMetaData90 PetrolCarsource = PetrolCarsMainPipeDataFlowTask.ComponentMetaDataCollection.New();
            PetrolCarsource.ComponentClassID = "DTSAdapter.OLEDBSource.1";
            // Create the design-time instance of the source.
            CManagedComponentWrapper PetrolCarsrcDesignTime = PetrolCarsource.Instantiate();
            // The ProvideComponentProperties method creates a default output.
            PetrolCarsrcDesignTime.ProvideComponentProperties();
            PetrolCarsource.Name = "Employee Dim from OLTP";
 
            // Assign the connection manager.
            PetrolCarsource.RuntimeConnectionCollection[0].ConnectionManagerID = PetrolCarSconMgr.ID;
            PetrolCarsource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(PetrolCarSconMgr);
            // Set the custom properties of the source.
            PetrolCarsrcDesignTime.SetComponentProperty("AccessMode", 2);  // Mode 0 : SqlCommand
            PetrolCarsrcDesignTime.SetComponentProperty("SqlCommand", @"SELECT     CarCompany, CarBodyType, CarName, EngineType
FROM         Car_Info
WHERE     (EngineType = 'petrol')");
            // Connect to the data source, and then update the metadata for the source.
            PetrolCarsrcDesignTime.AcquireConnections(null);
            PetrolCarsrcDesignTime.ReinitializeMetaData();
            PetrolCarsrcDesignTime.ReleaseConnections();
 
            // Add an Row Count to the data flow.
            IDTSComponentMetaData90 PetrolCarRowCountComponent = PetrolCarsMainPipeDataFlowTask.ComponentMetaDataCollection.New();
            PetrolCarRowCountComponent.Name = "Row Count";
            PetrolCarRowCountComponent.ComponentClassID = "DTSTransform.RowCount.1"; //Public Token is: "{DE50D3C7-41AF-4804-9247-CF1DEB147971}";
            CManagedComponentWrapper PetrolCarrowCountDesignTime = PetrolCarRowCountComponent.Instantiate();
            PetrolCarrowCountDesignTime.ProvideComponentProperties();
            PetrolCarrowCountDesignTime.SetComponentProperty("VariableName", "RowCountVar");
            PetrolCarrowCountDesignTime.AcquireConnections(null);
            PetrolCarrowCountDesignTime.ReinitializeMetaData();
            PetrolCarrowCountDesignTime.ReleaseConnections();
 
            // Create the path from source to Row Count Transformation.
            IDTSPath90 PetrolCarpathSource_RowCount = PetrolCarsMainPipeDataFlowTask.PathCollection.New();
            PetrolCarpathSource_RowCount.AttachPathAndPropagateNotifications(PetrolCarsource.OutputCollection[0], PetrolCarRowCountComponent.InputCollection[0]);
 
            // Create and configure an OLE DB destination component.
            IDTSComponentMetaData90 PetrolCardestination = PetrolCarsMainPipeDataFlowTask.ComponentMetaDataCollection.New();
            PetrolCardestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";
            // Create the design-time instance of the destination.
            CManagedComponentWrapper PetrolCardestDesignTime = PetrolCardestination.Instantiate();
            // The ProvideComponentProperties method creates a default input.
            PetrolCardestDesignTime.ProvideComponentProperties();
            PetrolCardestination.Name = "Employee Dim from OLAP";
            // Assign the connection manager.
            PetrolCardestination.RuntimeConnectionCollection[0].ConnectionManagerID = PetrolCarDconMgr.ID;
            PetrolCardestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(PetrolCarDconMgr);
            // Set the custom properties.
            PetrolCardestDesignTime.SetComponentProperty("AccessMode", 3); // Mode 3 : OpenRowset Using FastLoad  / Table - View fast load
            PetrolCardestDesignTime.SetComponentProperty("OpenRowset", "dbo.PetrolCars");
            PetrolCardestDesignTime.AcquireConnections(null);
            PetrolCardestDesignTime.ReinitializeMetaData();
            PetrolCardestDesignTime.ReleaseConnections();
 
 
            // Create the path from Rowcount to destination.
            IDTSPath90 PetrolCarpathRowCount_Dest = PetrolCarsMainPipeDataFlowTask.PathCollection.New();
            PetrolCarpathRowCount_Dest.AttachPathAndPropagateNotifications(PetrolCarRowCountComponent.OutputCollection[0], PetrolCardestination.InputCollection[0]);
 
 
            // Get the destination's default input and virtual input.
            IDTSInput90 PetrolCarinput = PetrolCardestination.InputCollection[0];
            IDTSVirtualInput90 PetrolCarvInput = PetrolCarinput.GetVirtualInput();
            // Iterate through the virtual input column collection.
            foreach (IDTSVirtualInputColumn90 vColumn in PetrolCarvInput.VirtualInputColumnCollection)
            {
                // Call the SetUsageType method of the destination
                //  to add each available virtual input column as an input column.
                PetrolCardestDesignTime.SetUsageType(
                   PetrolCarinput.ID, PetrolCarvInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
            }
 
            //map external metadata to the inputcolumn
            foreach (IDTSInputColumn90 inputColumn in PetrolCarinput.InputColumnCollection)
            {
                IDTSExternalMetadataColumn90 exMetaColumn = (IDTSExternalMetadataColumn90)PetrolCarinput.ExternalMetadataColumnCollection[inputColumn.Name];
                inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
            }
 
            //-----------Data Flow Inner component ends-------------------
 
            ///////////////////////////////////////////////////////////////////////////////////////////////
            //To add "Load Cars(Diesel)"(Data Flow Task) inside "Load Diesel Type Cars" (sequence container)
            TaskHost DieselCarsDataFlowTask = (TaskHost)LoadDieselTypeCarsSequenceContainer.Executables.Add("DTS.Pipeline.1");
            DieselCarsDataFlowTask.Name = @"Load Cars(Diesel)";
            DieselCarsDataFlowTask.FailPackageOnFailure = true;
            DieselCarsDataFlowTask.FailParentOnFailure = true;
            DieselCarsDataFlowTask.DelayValidation = false;
            DieselCarsDataFlowTask.Description = @"Data Flow Task to load Diesel Cars";
 
            //-----------Data Flow Inner component starts----------------
            MainPipe DieselCarsMainPipeDataFlowTask = DieselCarsDataFlowTask.InnerObject as MainPipe;
            // Source OLE DB connection manager to the package.
            ConnectionManager DieselCarsconMgr = package.Connections["LocalHost.OLTP"];
            // Destination OLE DB connection manager to the package.
            ConnectionManager DieselCarDconMgr = package.Connections["LocalHost.OLAP"];
 
            // Create and configure an OLE DB source component.  
            IDTSComponentMetaData90 DieselCarsource = DieselCarsMainPipeDataFlowTask.ComponentMetaDataCollection.New();
            DieselCarsource.ComponentClassID = "DTSAdapter.OLEDBSource.1";
            // Create the design-time instance of the source.
            CManagedComponentWrapper DieselCarsrcDesignTime = DieselCarsource.Instantiate();
            // The ProvideComponentProperties method creates a default output.
            DieselCarsrcDesignTime.ProvideComponentProperties();
            DieselCarsource.Name = "Employee Dim from OLTP";
 
            // Assign the connection manager.
            DieselCarsource.RuntimeConnectionCollection[0].ConnectionManagerID = DieselCarsconMgr.ID;
            DieselCarsource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(DieselCarsconMgr);
            // Set the custom properties of the source.
            DieselCarsrcDesignTime.SetComponentProperty("AccessMode", 2);  // Mode 0 : SqlCommand
            DieselCarsrcDesignTime.SetComponentProperty("SqlCommand", @"SELECT     CarCompany, CarBodyType, CarName, EngineType
FROM         Car_Info
WHERE     (EngineType = 'diesel')");
            // Connect to the data source, and then update the metadata for the source.
            DieselCarsrcDesignTime.AcquireConnections(null);
            DieselCarsrcDesignTime.ReinitializeMetaData();
            DieselCarsrcDesignTime.ReleaseConnections();
 
            // Add an Row Count to the data flow.
            IDTSComponentMetaData90 DieselCarRowCountComponent = DieselCarsMainPipeDataFlowTask.ComponentMetaDataCollection.New();
            DieselCarRowCountComponent.Name = "Row Count";
            DieselCarRowCountComponent.ComponentClassID = "DTSTransform.RowCount.1"; //Public Token is: "{DE50D3C7-41AF-4804-9247-CF1DEB147971}";
            CManagedComponentWrapper DieselCarrowCountDesignTime = DieselCarRowCountComponent.Instantiate();
            DieselCarrowCountDesignTime.ProvideComponentProperties();
            DieselCarrowCountDesignTime.SetComponentProperty("VariableName", "RowCountVar");
            DieselCarrowCountDesignTime.AcquireConnections(null);
            DieselCarrowCountDesignTime.ReinitializeMetaData();
            DieselCarrowCountDesignTime.ReleaseConnections();
 
            // Create the path from source to Row Count Transformation.
            IDTSPath90 DieselCarpathSource_RowCount = DieselCarsMainPipeDataFlowTask.PathCollection.New();
            DieselCarpathSource_RowCount.AttachPathAndPropagateNotifications(DieselCarsource.OutputCollection[0], DieselCarRowCountComponent.InputCollection[0]);
 
            // Create and configure an OLE DB destination component.
            IDTSComponentMetaData90 DieselCardestination = DieselCarsMainPipeDataFlowTask.ComponentMetaDataCollection.New();
            DieselCardestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";
            // Create the design-time instance of the destination.
            CManagedComponentWrapper DieselCardestDesignTime = DieselCardestination.Instantiate();
            // The ProvideComponentProperties method creates a default input.
            DieselCardestDesignTime.ProvideComponentProperties();
            DieselCardestination.Name = "Employee Dim from OLAP";
            // Assign the connection manager.
            DieselCardestination.RuntimeConnectionCollection[0].ConnectionManagerID = DieselCarDconMgr.ID;
            DieselCardestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(DieselCarDconMgr);
            // Set the custom properties.
            DieselCardestDesignTime.SetComponentProperty("AccessMode", 3); // Mode 3 : OpenRowset Using FastLoad  / Table - View fast load
            DieselCardestDesignTime.SetComponentProperty("OpenRowset", "dbo.DieselCars");
            DieselCardestDesignTime.AcquireConnections(null);
            DieselCardestDesignTime.ReinitializeMetaData();
            DieselCardestDesignTime.ReleaseConnections();
 
 
            // Create the path from Rowcount to destination.
            IDTSPath90 DieselCarpathRowCount_Dest = DieselCarsMainPipeDataFlowTask.PathCollection.New();
            DieselCarpathRowCount_Dest.AttachPathAndPropagateNotifications(DieselCarRowCountComponent.OutputCollection[0], DieselCardestination.InputCollection[0]);
 
 
            // Get the destination's default input and virtual input.
            IDTSInput90 DieselCarinput = DieselCardestination.InputCollection[0];
            IDTSVirtualInput90 DieselCarvInput = DieselCarinput.GetVirtualInput();
            // Iterate through the virtual input column collection.
            foreach (IDTSVirtualInputColumn90 vColumn in DieselCarvInput.VirtualInputColumnCollection)
            {
                // Call the SetUsageType method of the destination
                //  to add each available virtual input column as an input column.
                DieselCardestDesignTime.SetUsageType(
                   DieselCarinput.ID, DieselCarvInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
            }
 
            //map external metadata to the inputcolumn
            foreach (IDTSInputColumn90 inputColumn in DieselCarinput.InputColumnCollection)
            {
                IDTSExternalMetadataColumn90 exMetaColumn = (IDTSExternalMetadataColumn90)DieselCarinput.ExternalMetadataColumnCollection[inputColumn.Name];
                inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
            }
 
            //-----------Data Flow Inner component ends-------------------
 
 
 
 
            //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 Sequence Container 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"
        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"
 
        '////////////////////////////////////////////////////////
        ' //////// First Level- Outer Scope //////////////////
        ' ///////////////////////////////////////////////////////
 
 
        '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)
 
        'Global Sequence Container (Sequence Container)
        Dim GlobalSequenceContainer As Sequence = DirectCast(package.Executables.Add("STOCK:SEQUENCE"), Sequence)
        GlobalSequenceContainer.FailPackageOnFailure = True
        GlobalSequenceContainer.FailParentOnFailure = True
        GlobalSequenceContainer.Name = "Global Sequence Container"
        GlobalSequenceContainer.Description = "This is the outer sequence container."
 
        '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)
 
 
        'To set the precedence for the above tasks and containers
        'Precedence from "Package Start Indicator" to "Global Sequence Container"
        Dim PkgStrtInd_GloSeqCont As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Package Start Indicator"), package.Executables("Global Sequence Container"))
        PkgStrtInd_GloSeqCont.Name = "PkgStrtInd_GloSeqCont"
 
        ' Precedence from "Global Sequence Container" to "Package End Indicator"
        Dim GloSeqCont_PkgEndInd As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Global Sequence Container"), package.Executables("Package End Indicator"))
        GloSeqCont_PkgEndInd.Name = "GloSeqCont_PkgEndInd"
 
 
 
 
        '////////////////////////////////////////////////////////
        ' //////// Second Level- Inner Scope //////////////////
        ' ///////////////////////////////////////////////////////
 
 
 
        ' To add "Load Petrol Type Cars" (sequence container) inside "Global Sequence Container"
        Dim LoadPetrolTypeCarsSequenceContainer As Sequence = DirectCast(GlobalSequenceContainer.Executables.Add("STOCK:SEQUENCE"), Sequence)
        LoadPetrolTypeCarsSequenceContainer.FailPackageOnFailure = True
        LoadPetrolTypeCarsSequenceContainer.FailParentOnFailure = True
        LoadPetrolTypeCarsSequenceContainer.Name = "Load Petrol Type Cars"
        LoadPetrolTypeCarsSequenceContainer.Description = "This is the inner sequence container to load Petrol Cars."
 
        ' To add "Load Diesel Type Cars" (sequence container) inside "Global Sequence Container"
        Dim LoadDieselTypeCarsSequenceContainer As Sequence = DirectCast(GlobalSequenceContainer.Executables.Add("STOCK:SEQUENCE"), Sequence)
        LoadDieselTypeCarsSequenceContainer.FailPackageOnFailure = True
        LoadDieselTypeCarsSequenceContainer.FailParentOnFailure = True
        LoadDieselTypeCarsSequenceContainer.Name = "Load Diesel Type Cars"
        LoadDieselTypeCarsSequenceContainer.Description = "This is the inner sequence container to load Diesel Cars."
 
        ' The above two containers will run parallel, so there is no need to put precedence constrainsts between them.
 
        '///////////////////////////////////////////////////////////////////////////
        ' //////// Third Level- Data flow tasks inside inner sequence containers //////
        ' ///////////////////////////////////////////////////////////////////////////
 
 
        'To add "Load Cars(Petrol)"(Data Flow Task) inside "Load Petrol Type Cars" (sequence container)
        Dim PetrolCarsDataFlowTask As TaskHost = DirectCast(LoadPetrolTypeCarsSequenceContainer.Executables.Add("DTS.Pipeline.1"), TaskHost)
        PetrolCarsDataFlowTask.Name = "Load Cars(Petrol)"
        PetrolCarsDataFlowTask.FailPackageOnFailure = True
        PetrolCarsDataFlowTask.FailParentOnFailure = True
        PetrolCarsDataFlowTask.DelayValidation = False
        PetrolCarsDataFlowTask.Description = "Data Flow Task to load Petrol Cars"
 
        '-----------Data Flow Inner component starts----------------
        Dim PetrolCarsMainPipeDataFlowTask As MainPipe = TryCast(PetrolCarsDataFlowTask.InnerObject, MainPipe)
        ' Source OLE DB connection manager to the package.
        Dim PetrolCarSconMgr As ConnectionManager = package.Connections("LocalHost.OLTP")
        ' Destination OLE DB connection manager to the package.
        Dim PetrolCarDconMgr As ConnectionManager = package.Connections("LocalHost.OLAP")
 
        ' Create and configure an OLE DB source component. 
        Dim PetrolCarsource As IDTSComponentMetaData90 = PetrolCarsMainPipeDataFlowTask.ComponentMetaDataCollection.[New]()
        PetrolCarsource.ComponentClassID = "DTSAdapter.OLEDBSource.1"
        ' Create the design-time instance of the source.
        Dim PetrolCarsrcDesignTime As CManagedComponentWrapper = PetrolCarsource.Instantiate()
        ' The ProvideComponentProperties method creates a default output.
        PetrolCarsrcDesignTime.ProvideComponentProperties()
        PetrolCarsource.Name = "Employee Dim from OLTP"
 
        ' Assign the connection manager.
        PetrolCarsource.RuntimeConnectionCollection(0).ConnectionManagerID = PetrolCarSconMgr.ID
        PetrolCarsource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(PetrolCarSconMgr)
        ' Set the custom properties of the source.
        PetrolCarsrcDesignTime.SetComponentProperty("AccessMode", 2)
        ' Mode 0 : SqlCommand
        PetrolCarsrcDesignTime.SetComponentProperty("SqlCommand", "SELECT CarCompany, CarBodyType, CarName, EngineType" & vbCr & vbLf & "FROM Car_Info" & vbCr & vbLf & "WHERE (EngineType = 'petrol')")
        ' Connect to the data source, and then update the metadata for the source.
        PetrolCarsrcDesignTime.AcquireConnections(Nothing)
        PetrolCarsrcDesignTime.ReinitializeMetaData()
        PetrolCarsrcDesignTime.ReleaseConnections()
 
        ' Add an Row Count to the data flow.
        Dim PetrolCarRowCountComponent As IDTSComponentMetaData90 = PetrolCarsMainPipeDataFlowTask.ComponentMetaDataCollection.[New]()
        PetrolCarRowCountComponent.Name = "Row Count"
        PetrolCarRowCountComponent.ComponentClassID = "DTSTransform.RowCount.1"
        'Public Token is: "{DE50D3C7-41AF-4804-9247-CF1DEB147971}";
        Dim PetrolCarrowCountDesignTime As CManagedComponentWrapper = PetrolCarRowCountComponent.Instantiate()
        PetrolCarrowCountDesignTime.ProvideComponentProperties()
        PetrolCarrowCountDesignTime.SetComponentProperty("VariableName", "RowCountVar")
        PetrolCarrowCountDesignTime.AcquireConnections(Nothing)
        PetrolCarrowCountDesignTime.ReinitializeMetaData()
        PetrolCarrowCountDesignTime.ReleaseConnections()
 
        ' Create the path from source to Row Count Transformation.
        Dim PetrolCarpathSource_RowCount As IDTSPath90 = PetrolCarsMainPipeDataFlowTask.PathCollection.[New]()
        PetrolCarpathSource_RowCount.AttachPathAndPropagateNotifications(PetrolCarsource.OutputCollection(0), PetrolCarRowCountComponent.InputCollection(0))
 
        ' Create and configure an OLE DB destination component.
        Dim PetrolCardestination As IDTSComponentMetaData90 = PetrolCarsMainPipeDataFlowTask.ComponentMetaDataCollection.[New]()
        PetrolCardestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1"
        ' Create the design-time instance of the destination.
        Dim PetrolCardestDesignTime As CManagedComponentWrapper = PetrolCardestination.Instantiate()
        ' The ProvideComponentProperties method creates a default input.
        PetrolCardestDesignTime.ProvideComponentProperties()
        PetrolCardestination.Name = "Employee Dim from OLAP"
        ' Assign the connection manager.
        PetrolCardestination.RuntimeConnectionCollection(0).ConnectionManagerID = PetrolCarDconMgr.ID
        PetrolCardestination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(PetrolCarDconMgr)
        ' Set the custom properties.
        PetrolCardestDesignTime.SetComponentProperty("AccessMode", 3)
        ' Mode 3 : OpenRowset Using FastLoad / Table - View fast load
        PetrolCardestDesignTime.SetComponentProperty("OpenRowset", "dbo.PetrolCars")
        PetrolCardestDesignTime.AcquireConnections(Nothing)
        PetrolCardestDesignTime.ReinitializeMetaData()
        PetrolCardestDesignTime.ReleaseConnections()
 
 
        ' Create the path from Rowcount to destination.
        Dim PetrolCarpathRowCount_Dest As IDTSPath90 = PetrolCarsMainPipeDataFlowTask.PathCollection.[New]()
        PetrolCarpathRowCount_Dest.AttachPathAndPropagateNotifications(PetrolCarRowCountComponent.OutputCollection(0), PetrolCardestination.InputCollection(0))
 
 
        ' Get the destination's default input and virtual input.
        Dim PetrolCarinput As IDTSInput90 = PetrolCardestination.InputCollection(0)
        Dim PetrolCarvInput As IDTSVirtualInput90 = PetrolCarinput.GetVirtualInput()
        ' Iterate through the virtual input column collection.
        For Each vColumn As IDTSVirtualInputColumn90 In PetrolCarvInput.VirtualInputColumnCollection
            ' Call the SetUsageType method of the destination
            ' to add each available virtual input column as an input column.
            PetrolCardestDesignTime.SetUsageType(PetrolCarinput.ID, PetrolCarvInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
        Next
 
        'map external metadata to the inputcolumn
        For Each inputColumn As IDTSInputColumn90 In PetrolCarinput.InputColumnCollection
            Dim exMetaColumn As IDTSExternalMetadataColumn90 = DirectCast(PetrolCarinput.ExternalMetadataColumnCollection(inputColumn.Name), IDTSExternalMetadataColumn90)
            inputColumn.ExternalMetadataColumnID = exMetaColumn.ID
        Next
 
        '-----------Data Flow Inner component ends-------------------
 
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'To add "Load Cars(Diesel)"(Data Flow Task) inside "Load Diesel Type Cars" (sequence container)
        Dim DieselCarsDataFlowTask As TaskHost = DirectCast(LoadDieselTypeCarsSequenceContainer.Executables.Add("DTS.Pipeline.1"), TaskHost)
        DieselCarsDataFlowTask.Name = "Load Cars(Diesel)"
        DieselCarsDataFlowTask.FailPackageOnFailure = True
        DieselCarsDataFlowTask.FailParentOnFailure = True
        DieselCarsDataFlowTask.DelayValidation = False
        DieselCarsDataFlowTask.Description = "Data Flow Task to load Diesel Cars"
 
        '-----------Data Flow Inner component starts----------------
        Dim DieselCarsMainPipeDataFlowTask As MainPipe = TryCast(DieselCarsDataFlowTask.InnerObject, MainPipe)
        ' Source OLE DB connection manager to the package.
        Dim DieselCarsconMgr As ConnectionManager = package.Connections("LocalHost.OLTP")
        ' Destination OLE DB connection manager to the package.
        Dim DieselCarDconMgr As ConnectionManager = package.Connections("LocalHost.OLAP")
 
        ' Create and configure an OLE DB source component. 
        Dim DieselCarsource As IDTSComponentMetaData90 = DieselCarsMainPipeDataFlowTask.ComponentMetaDataCollection.[New]()
        DieselCarsource.ComponentClassID = "DTSAdapter.OLEDBSource.1"
        ' Create the design-time instance of the source.
        Dim DieselCarsrcDesignTime As CManagedComponentWrapper = DieselCarsource.Instantiate()
        ' The ProvideComponentProperties method creates a default output.
        DieselCarsrcDesignTime.ProvideComponentProperties()
        DieselCarsource.Name = "Employee Dim from OLTP"
 
        ' Assign the connection manager.
        DieselCarsource.RuntimeConnectionCollection(0).ConnectionManagerID = DieselCarsconMgr.ID
        DieselCarsource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(DieselCarsconMgr)
        ' Set the custom properties of the source.
        DieselCarsrcDesignTime.SetComponentProperty("AccessMode", 2)
        ' Mode 0 : SqlCommand
        DieselCarsrcDesignTime.SetComponentProperty("SqlCommand", "SELECT CarCompany, CarBodyType, CarName, EngineType" & vbCr & vbLf & "FROM Car_Info" & vbCr & vbLf & "WHERE (EngineType = 'diesel')")
        ' Connect to the data source, and then update the metadata for the source.
        DieselCarsrcDesignTime.AcquireConnections(Nothing)
        DieselCarsrcDesignTime.ReinitializeMetaData()
        DieselCarsrcDesignTime.ReleaseConnections()
 
        ' Add an Row Count to the data flow.
        Dim DieselCarRowCountComponent As IDTSComponentMetaData90 = DieselCarsMainPipeDataFlowTask.ComponentMetaDataCollection.[New]()
        DieselCarRowCountComponent.Name = "Row Count"
        DieselCarRowCountComponent.ComponentClassID = "DTSTransform.RowCount.1"
        'Public Token is: "{DE50D3C7-41AF-4804-9247-CF1DEB147971}";
        Dim DieselCarrowCountDesignTime As CManagedComponentWrapper = DieselCarRowCountComponent.Instantiate()
        DieselCarrowCountDesignTime.ProvideComponentProperties()
        DieselCarrowCountDesignTime.SetComponentProperty("VariableName", "RowCountVar")
        DieselCarrowCountDesignTime.AcquireConnections(Nothing)
        DieselCarrowCountDesignTime.ReinitializeMetaData()
        DieselCarrowCountDesignTime.ReleaseConnections()
 
        ' Create the path from source to Row Count Transformation.
        Dim DieselCarpathSource_RowCount As IDTSPath90 = DieselCarsMainPipeDataFlowTask.PathCollection.[New]()
        DieselCarpathSource_RowCount.AttachPathAndPropagateNotifications(DieselCarsource.OutputCollection(0), DieselCarRowCountComponent.InputCollection(0))
 
        ' Create and configure an OLE DB destination component.
        Dim DieselCardestination As IDTSComponentMetaData90 = DieselCarsMainPipeDataFlowTask.ComponentMetaDataCollection.[New]()
        DieselCardestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1"
        ' Create the design-time instance of the destination.
        Dim DieselCardestDesignTime As CManagedComponentWrapper = DieselCardestination.Instantiate()
        ' The ProvideComponentProperties method creates a default input.
        DieselCardestDesignTime.ProvideComponentProperties()
        DieselCardestination.Name = "Employee Dim from OLAP"
        ' Assign the connection manager.
        DieselCardestination.RuntimeConnectionCollection(0).ConnectionManagerID = DieselCarDconMgr.ID
        DieselCardestination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(DieselCarDconMgr)
        ' Set the custom properties.
        DieselCardestDesignTime.SetComponentProperty("AccessMode", 3)
        ' Mode 3 : OpenRowset Using FastLoad / Table - View fast load
        DieselCardestDesignTime.SetComponentProperty("OpenRowset", "dbo.DieselCars")
        DieselCardestDesignTime.AcquireConnections(Nothing)
        DieselCardestDesignTime.ReinitializeMetaData()
        DieselCardestDesignTime.ReleaseConnections()
 
 
        ' Create the path from Rowcount to destination.
        Dim DieselCarpathRowCount_Dest As IDTSPath90 = DieselCarsMainPipeDataFlowTask.PathCollection.[New]()
        DieselCarpathRowCount_Dest.AttachPathAndPropagateNotifications(DieselCarRowCountComponent.OutputCollection(0), DieselCardestination.InputCollection(0))
 
 
        ' Get the destination's default input and virtual input.
        Dim DieselCarinput As IDTSInput90 = DieselCardestination.InputCollection(0)
        Dim DieselCarvInput As IDTSVirtualInput90 = DieselCarinput.GetVirtualInput()
        ' Iterate through the virtual input column collection.
        For Each vColumn As IDTSVirtualInputColumn90 In DieselCarvInput.VirtualInputColumnCollection
            ' Call the SetUsageType method of the destination
            ' to add each available virtual input column as an input column.
            DieselCardestDesignTime.SetUsageType(DieselCarinput.ID, DieselCarvInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
        Next
 
        'map external metadata to the inputcolumn
        For Each inputColumn As IDTSInputColumn90 In DieselCarinput.InputColumnCollection
            Dim exMetaColumn As IDTSExternalMetadataColumn90 = DirectCast(DieselCarinput.ExternalMetadataColumnCollection(inputColumn.Name), IDTSExternalMetadataColumn90)
            inputColumn.ExternalMetadataColumnID = exMetaColumn.ID
        Next
 
        '-----------Data Flow Inner component ends-------------------
 
 
 
 
        '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;
 
 
//The below code will create a SSIS package with Sequence Container for SQL Server 2008
 
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";
 
            /*////////////////////////////////////////////////////////
            ////////   First Level- Outer Scope   //////////////////
            ///////////////////////////////////////////////////////*/
 
            //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);
 
            //Global Sequence Container (Sequence Container)
            Sequence GlobalSequenceContainer = (Sequence)package.Executables.Add("STOCK:SEQUENCE");
            GlobalSequenceContainer.FailPackageOnFailure = true;
            GlobalSequenceContainer.FailParentOnFailure = true;
            GlobalSequenceContainer.Name = @"Global Sequence Container";
            GlobalSequenceContainer.Description = @"This is the outer sequence container.";
 
            //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);
 
 
            //To set the precedence for the above tasks and containers
            //Precedence from "Package Start Indicator" to "Global Sequence Container"
            PrecedenceConstraint PkgStrtInd_GloSeqCont = package.PrecedenceConstraints.Add(package.Executables[@"Package Start Indicator"], package.Executables[@"Global Sequence Container"]);
            PkgStrtInd_GloSeqCont.Name = @"PkgStrtInd_GloSeqCont";
 
            // Precedence from "Global Sequence Container" to "Package End Indicator"
            PrecedenceConstraint GloSeqCont_PkgEndInd = package.PrecedenceConstraints.Add(package.Executables[@"Global Sequence Container"], package.Executables[@"Package End Indicator"]);
            GloSeqCont_PkgEndInd.Name = @"GloSeqCont_PkgEndInd";
 
 
 
 
            /*////////////////////////////////////////////////////////
            ////////   Second Level- Inner Scope   //////////////////
            ///////////////////////////////////////////////////////*/
 
 
            // To add "Load Petrol Type Cars" (sequence container) inside "Global Sequence Container"
            Sequence LoadPetrolTypeCarsSequenceContainer = (Sequence)GlobalSequenceContainer.Executables.Add("STOCK:SEQUENCE");
            LoadPetrolTypeCarsSequenceContainer.FailPackageOnFailure = true;
            LoadPetrolTypeCarsSequenceContainer.FailParentOnFailure = true;
            LoadPetrolTypeCarsSequenceContainer.Name = @"Load Petrol Type Cars";
            LoadPetrolTypeCarsSequenceContainer.Description = @"This is the inner sequence container to load Petrol Cars.";
 
            // To add "Load Diesel Type Cars" (sequence container) inside "Global Sequence Container"
            Sequence LoadDieselTypeCarsSequenceContainer = (Sequence)GlobalSequenceContainer.Executables.Add("STOCK:SEQUENCE");
            LoadDieselTypeCarsSequenceContainer.FailPackageOnFailure = true;
            LoadDieselTypeCarsSequenceContainer.FailParentOnFailure = true;
            LoadDieselTypeCarsSequenceContainer.Name = @"Load Diesel Type Cars";
            LoadDieselTypeCarsSequenceContainer.Description = @"This is the inner sequence container to load Diesel Cars.";
 
            // The above two containers will run parallel, so there is no need to put precedence constrainsts between them.
 
            /*///////////////////////////////////////////////////////////////////////////
            //////// Third Level- Data flow tasks inside inner sequence containers //////
            ///////////////////////////////////////////////////////////////////////////*/
 
            //To add "Load Cars(Petrol)"(Data Flow Task) inside "Load Petrol Type Cars" (sequence container)
            TaskHost PetrolCarsDataFlowTask = (TaskHost)LoadPetrolTypeCarsSequenceContainer.Executables.Add("SSIS.Pipeline.2");
            PetrolCarsDataFlowTask.Name = @"Load Cars(Petrol)";
            PetrolCarsDataFlowTask.FailPackageOnFailure = true;
            PetrolCarsDataFlowTask.FailParentOnFailure = true;
            PetrolCarsDataFlowTask.DelayValidation = false;
            PetrolCarsDataFlowTask.Description = @"Data Flow Task to load Petrol Cars";
 
            //-----------Data Flow Inner component starts----------------
            MainPipe PetrolCarsMainPipeDataFlowTask = PetrolCarsDataFlowTask.InnerObject as MainPipe;
            // Source OLE DB connection manager to the package.
            ConnectionManager PetrolCarSconMgr = package.Connections["LocalHost.OLTP"];
            // Destination OLE DB connection manager to the package.
            ConnectionManager PetrolCarDconMgr = package.Connections["LocalHost.OLAP"];
 
            // Create and configure an OLE DB source component.  
            IDTSComponentMetaData100 PetrolCarsource = PetrolCarsMainPipeDataFlowTask.ComponentMetaDataCollection.New();
            PetrolCarsource.ComponentClassID = "DTSAdapter.OLEDBSource.2";
            // Create the design-time instance of the source.
            CManagedComponentWrapper PetrolCarsrcDesignTime = PetrolCarsource.Instantiate();
            // The ProvideComponentProperties method creates a default output.
            PetrolCarsrcDesignTime.ProvideComponentProperties();
            PetrolCarsource.Name = "Employee Dim from OLTP";
 
            // Assign the connection manager.
            PetrolCarsource.RuntimeConnectionCollection[0].ConnectionManagerID = PetrolCarSconMgr.ID;
            PetrolCarsource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(PetrolCarSconMgr);
            // Set the custom properties of the source.
            PetrolCarsrcDesignTime.SetComponentProperty("AccessMode", 2);  // Mode 0 : SqlCommand
            PetrolCarsrcDesignTime.SetComponentProperty("SqlCommand", @"SELECT     CarCompany, CarBodyType, CarName, EngineType
FROM         Car_Info
WHERE     (EngineType = 'petrol')");
            // Connect to the data source, and then update the metadata for the source.
            PetrolCarsrcDesignTime.AcquireConnections(null);
            PetrolCarsrcDesignTime.ReinitializeMetaData();
            PetrolCarsrcDesignTime.ReleaseConnections();
 
            // Add an Row Count to the data flow.
            IDTSComponentMetaData100 PetrolCarRowCountComponent = PetrolCarsMainPipeDataFlowTask.ComponentMetaDataCollection.New();
            PetrolCarRowCountComponent.Name = "Row Count";
            PetrolCarRowCountComponent.ComponentClassID = "DTSTransform.RowCount.2"; //Public Token is: "{DE50D3C7-41AF-4804-9247-CF1DEB147971}";
            CManagedComponentWrapper PetrolCarrowCountDesignTime = PetrolCarRowCountComponent.Instantiate();
            PetrolCarrowCountDesignTime.ProvideComponentProperties();
            PetrolCarrowCountDesignTime.SetComponentProperty("VariableName", "RowCountVar");
            PetrolCarrowCountDesignTime.AcquireConnections(null);
            PetrolCarrowCountDesignTime.ReinitializeMetaData();
            PetrolCarrowCountDesignTime.ReleaseConnections();
 
            // Create the path from source to Row Count Transformation.
            IDTSPath100 PetrolCarpathSource_RowCount = PetrolCarsMainPipeDataFlowTask.PathCollection.New();
            PetrolCarpathSource_RowCount.AttachPathAndPropagateNotifications(PetrolCarsource.OutputCollection[0], PetrolCarRowCountComponent.InputCollection[0]);
 
            // Create and configure an OLE DB destination component.
            IDTSComponentMetaData100 PetrolCardestination = PetrolCarsMainPipeDataFlowTask.ComponentMetaDataCollection.New();
            PetrolCardestination.ComponentClassID = "DTSAdapter.OLEDBDestination.2";
            // Create the design-time instance of the destination.
            CManagedComponentWrapper PetrolCardestDesignTime = PetrolCardestination.Instantiate();
            // The ProvideComponentProperties method creates a default input.
            PetrolCardestDesignTime.ProvideComponentProperties();
            PetrolCardestination.Name = "Employee Dim from OLAP";
            // Assign the connection manager.
            PetrolCardestination.RuntimeConnectionCollection[0].ConnectionManagerID = PetrolCarDconMgr.ID;
            PetrolCardestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(PetrolCarDconMgr);
            // Set the custom properties.
            PetrolCardestDesignTime.SetComponentProperty("AccessMode", 3); // Mode 3 : OpenRowset Using FastLoad  / Table - View fast load
            PetrolCardestDesignTime.SetComponentProperty("OpenRowset", "dbo.PetrolCars");
            PetrolCardestDesignTime.AcquireConnections(null);
            PetrolCardestDesignTime.ReinitializeMetaData();
            PetrolCardestDesignTime.ReleaseConnections();
 
 
            // Create the path from Rowcount to destination.
            IDTSPath100 PetrolCarpathRowCount_Dest = PetrolCarsMainPipeDataFlowTask.PathCollection.New();
            PetrolCarpathRowCount_Dest.AttachPathAndPropagateNotifications(PetrolCarRowCountComponent.OutputCollection[0], PetrolCardestination.InputCollection[0]);
 
 
            // Get the destination's default input and virtual input.
            IDTSInput100 PetrolCarinput = PetrolCardestination.InputCollection[0];
            IDTSVirtualInput100 PetrolCarvInput = PetrolCarinput.GetVirtualInput();
            // Iterate through the virtual input column collection.
            foreach (IDTSVirtualInputColumn100 vColumn in PetrolCarvInput.VirtualInputColumnCollection)
            {
                // Call the SetUsageType method of the destination
                //  to add each available virtual input column as an input column.
                PetrolCardestDesignTime.SetUsageType(
                   PetrolCarinput.ID, PetrolCarvInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
            }
 
            //map external metadata to the inputcolumn
            foreach (IDTSInputColumn100 inputColumn in PetrolCarinput.InputColumnCollection)
            {
                IDTSExternalMetadataColumn100 exMetaColumn = (IDTSExternalMetadataColumn100)PetrolCarinput.ExternalMetadataColumnCollection[inputColumn.Name];
                inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
            }
 
            //-----------Data Flow Inner component ends-------------------
 
            ///////////////////////////////////////////////////////////////////////////////////////////////
            //To add "Load Cars(Diesel)"(Data Flow Task) inside "Load Diesel Type Cars" (sequence container)
            TaskHost DieselCarsDataFlowTask = (TaskHost)LoadDieselTypeCarsSequenceContainer.Executables.Add("SSIS.Pipeline.2");
            DieselCarsDataFlowTask.Name = @"Load Cars(Diesel)";
            DieselCarsDataFlowTask.FailPackageOnFailure = true;
            DieselCarsDataFlowTask.FailParentOnFailure = true;
            DieselCarsDataFlowTask.DelayValidation = false;
            DieselCarsDataFlowTask.Description = @"Data Flow Task to load Diesel Cars";
 
            //-----------Data Flow Inner component starts----------------
            MainPipe DieselCarsMainPipeDataFlowTask = DieselCarsDataFlowTask.InnerObject as MainPipe;
            // Source OLE DB connection manager to the package.
            ConnectionManager DieselCarsconMgr = package.Connections["LocalHost.OLTP"];
            // Destination OLE DB connection manager to the package.
            ConnectionManager DieselCarDconMgr = package.Connections["LocalHost.OLAP"];
 
            // Create and configure an OLE DB source component.  
            IDTSComponentMetaData100 DieselCarsource = DieselCarsMainPipeDataFlowTask.ComponentMetaDataCollection.New();
            DieselCarsource.ComponentClassID = "DTSAdapter.OLEDBSource.2";
            // Create the design-time instance of the source.
            CManagedComponentWrapper DieselCarsrcDesignTime = DieselCarsource.Instantiate();
            // The ProvideComponentProperties method creates a default output.
            DieselCarsrcDesignTime.ProvideComponentProperties();
            DieselCarsource.Name = "Employee Dim from OLTP";
 
            // Assign the connection manager.
            DieselCarsource.RuntimeConnectionCollection[0].ConnectionManagerID = DieselCarsconMgr.ID;
            DieselCarsource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(DieselCarsconMgr); 
            // Set the custom properties of the source.
            DieselCarsrcDesignTime.SetComponentProperty("AccessMode", 2);  // Mode 0 : SqlCommand
            DieselCarsrcDesignTime.SetComponentProperty("SqlCommand", @"SELECT     CarCompany, CarBodyType, CarName, EngineType
FROM         Car_Info
WHERE     (EngineType = 'diesel')");
            // Connect to the data source, and then update the metadata for the source.
            DieselCarsrcDesignTime.AcquireConnections(null);
            DieselCarsrcDesignTime.ReinitializeMetaData();
            DieselCarsrcDesignTime.ReleaseConnections();
 
            // Add an Row Count to the data flow.
            IDTSComponentMetaData100 DieselCarRowCountComponent = DieselCarsMainPipeDataFlowTask.ComponentMetaDataCollection.New();
            DieselCarRowCountComponent.Name = "Row Count";
            DieselCarRowCountComponent.ComponentClassID = "DTSTransform.RowCount.2"; //Public Token is: "{DE50D3C7-41AF-4804-9247-CF1DEB147971}";
            CManagedComponentWrapper DieselCarrowCountDesignTime = DieselCarRowCountComponent.Instantiate();
            DieselCarrowCountDesignTime.ProvideComponentProperties();
            DieselCarrowCountDesignTime.SetComponentProperty("VariableName", "RowCountVar");
            DieselCarrowCountDesignTime.AcquireConnections(null);
            DieselCarrowCountDesignTime.ReinitializeMetaData();
            DieselCarrowCountDesignTime.ReleaseConnections();
 
            // Create the path from source to Row Count Transformation.
            IDTSPath100 DieselCarpathSource_RowCount = DieselCarsMainPipeDataFlowTask.PathCollection.New();
            DieselCarpathSource_RowCount.AttachPathAndPropagateNotifications(DieselCarsource.OutputCollection[0], DieselCarRowCountComponent.InputCollection[0]);
 
            // Create and configure an OLE DB destination component.
            IDTSComponentMetaData100 DieselCardestination = DieselCarsMainPipeDataFlowTask.ComponentMetaDataCollection.New();
            DieselCardestination.ComponentClassID = "DTSAdapter.OLEDBDestination.2";
            // Create the design-time instance of the destination.
            CManagedComponentWrapper DieselCardestDesignTime = DieselCardestination.Instantiate();
            // The ProvideComponentProperties method creates a default input.
            DieselCardestDesignTime.ProvideComponentProperties();
            DieselCardestination.Name = "Employee Dim from OLAP";
            // Assign the connection manager.
            DieselCardestination.RuntimeConnectionCollection[0].ConnectionManagerID = DieselCarDconMgr.ID;
            DieselCardestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(DieselCarDconMgr);
            // Set the custom properties.
            DieselCardestDesignTime.SetComponentProperty("AccessMode", 3); // Mode 3 : OpenRowset Using FastLoad  / Table - View fast load
            DieselCardestDesignTime.SetComponentProperty("OpenRowset", "dbo.DieselCars");
            DieselCardestDesignTime.AcquireConnections(null);
            DieselCardestDesignTime.ReinitializeMetaData();
            DieselCardestDesignTime.ReleaseConnections();
 
 
            // Create the path from Rowcount to destination.
            IDTSPath100 DieselCarpathRowCount_Dest = DieselCarsMainPipeDataFlowTask.PathCollection.New();
            DieselCarpathRowCount_Dest.AttachPathAndPropagateNotifications(DieselCarRowCountComponent.OutputCollection[0], DieselCardestination.InputCollection[0]);
 
 
            // Get the destination's default input and virtual input.
            IDTSInput100 DieselCarinput = DieselCardestination.InputCollection[0];
            IDTSVirtualInput100 DieselCarvInput = DieselCarinput.GetVirtualInput();
            // Iterate through the virtual input column collection.
            foreach (IDTSVirtualInputColumn100 vColumn in DieselCarvInput.VirtualInputColumnCollection)
            {
                // Call the SetUsageType method of the destination
                //  to add each available virtual input column as an input column.
                DieselCardestDesignTime.SetUsageType(
                   DieselCarinput.ID, DieselCarvInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
            }
 
            //map external metadata to the inputcolumn
            foreach (IDTSInputColumn100 inputColumn in DieselCarinput.InputColumnCollection)
            {
                IDTSExternalMetadataColumn100 exMetaColumn = (IDTSExternalMetadataColumn100)DieselCarinput.ExternalMetadataColumnCollection[inputColumn.Name];
                inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
            }
 
            //-----------Data Flow Inner component ends-------------------
 
 
            //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 with Sequence Container for SQL Server 2008
Module Module1
 
    Sub Main()
        '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"
 
        '////////////////////////////////////////////////////////
        ' //////// First Level- Outer Scope //////////////////
        ' ///////////////////////////////////////////////////////
 
 
        '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)
 
        'Global Sequence Container (Sequence Container)
        Dim GlobalSequenceContainer As Sequence = DirectCast(package.Executables.Add("STOCK:SEQUENCE"), Sequence)
        GlobalSequenceContainer.FailPackageOnFailure = True
        GlobalSequenceContainer.FailParentOnFailure = True
        GlobalSequenceContainer.Name = "Global Sequence Container"
        GlobalSequenceContainer.Description = "This is the outer sequence container."
 
        '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)
 
 
        'To set the precedence for the above tasks and containers
        'Precedence from "Package Start Indicator" to "Global Sequence Container"
        Dim PkgStrtInd_GloSeqCont As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Package Start Indicator"), package.Executables("Global Sequence Container"))
        PkgStrtInd_GloSeqCont.Name = "PkgStrtInd_GloSeqCont"
 
        ' Precedence from "Global Sequence Container" to "Package End Indicator"
        Dim GloSeqCont_PkgEndInd As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Global Sequence Container"), package.Executables("Package End Indicator"))
        GloSeqCont_PkgEndInd.Name = "GloSeqCont_PkgEndInd"
 
 
 
 
        '////////////////////////////////////////////////////////
        '//////// Second Level- Inner Scope ////////////////////
        ' ///////////////////////////////////////////////////////
 
 
 
        ' To add "Load Petrol Type Cars" (sequence container) inside "Global Sequence Container"
        Dim LoadPetrolTypeCarsSequenceContainer As Sequence = DirectCast(GlobalSequenceContainer.Executables.Add("STOCK:SEQUENCE"), Sequence)
        LoadPetrolTypeCarsSequenceContainer.FailPackageOnFailure = True
        LoadPetrolTypeCarsSequenceContainer.FailParentOnFailure = True
        LoadPetrolTypeCarsSequenceContainer.Name = "Load Petrol Type Cars"
        LoadPetrolTypeCarsSequenceContainer.Description = "This is the inner sequence container to load Petrol Cars."
 
        ' To add "Load Diesel Type Cars" (sequence container) inside "Global Sequence Container"
        Dim LoadDieselTypeCarsSequenceContainer As Sequence = DirectCast(GlobalSequenceContainer.Executables.Add("STOCK:SEQUENCE"), Sequence)
        LoadDieselTypeCarsSequenceContainer.FailPackageOnFailure = True
        LoadDieselTypeCarsSequenceContainer.FailParentOnFailure = True
        LoadDieselTypeCarsSequenceContainer.Name = "Load Diesel Type Cars"
        LoadDieselTypeCarsSequenceContainer.Description = "This is the inner sequence container to load Diesel Cars."
 
        ' The above two containers will run parallel, so there is no need to put precedence constrainsts between them.
 
        '///////////////////////////////////////////////////////////////////////////
        '//////// Third Level- Data flow tasks inside inner sequence containers ////
        '///////////////////////////////////////////////////////////////////////////
 
 
        'To add "Load Cars(Petrol)"(Data Flow Task) inside "Load Petrol Type Cars" (sequence container)
        Dim PetrolCarsDataFlowTask As TaskHost = DirectCast(LoadPetrolTypeCarsSequenceContainer.Executables.Add("SSIS.Pipeline.2"), TaskHost)
        PetrolCarsDataFlowTask.Name = "Load Cars(Petrol)"
        PetrolCarsDataFlowTask.FailPackageOnFailure = True
        PetrolCarsDataFlowTask.FailParentOnFailure = True
        PetrolCarsDataFlowTask.DelayValidation = False
        PetrolCarsDataFlowTask.Description = "Data Flow Task to load Petrol Cars"
 
        '-----------Data Flow Inner component starts----------------
        Dim PetrolCarsMainPipeDataFlowTask As MainPipe = TryCast(PetrolCarsDataFlowTask.InnerObject, MainPipe)
        ' Source OLE DB connection manager to the package.
        Dim PetrolCarSconMgr As ConnectionManager = package.Connections("LocalHost.OLTP")
        ' Destination OLE DB connection manager to the package.
        Dim PetrolCarDconMgr As ConnectionManager = package.Connections("LocalHost.OLAP")
 
        ' Create and configure an OLE DB source component. 
        Dim PetrolCarsource As IDTSComponentMetaData100 = PetrolCarsMainPipeDataFlowTask.ComponentMetaDataCollection.[New]()
        PetrolCarsource.ComponentClassID = "DTSAdapter.OLEDBSource.2"
        ' Create the design-time instance of the source.
        Dim PetrolCarsrcDesignTime As CManagedComponentWrapper = PetrolCarsource.Instantiate()
        ' The ProvideComponentProperties method creates a default output.
        PetrolCarsrcDesignTime.ProvideComponentProperties()
        PetrolCarsource.Name = "Employee Dim from OLTP"
 
        ' Assign the connection manager.
        PetrolCarsource.RuntimeConnectionCollection(0).ConnectionManagerID = PetrolCarSconMgr.ID
        PetrolCarsource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.GetExtendedInterface(PetrolCarSconMgr)
        ' Set the custom properties of the source.
        PetrolCarsrcDesignTime.SetComponentProperty("AccessMode", 2)
        ' Mode 0 : SqlCommand
        PetrolCarsrcDesignTime.SetComponentProperty("SqlCommand", "SELECT CarCompany, CarBodyType, CarName, EngineType" & vbCr & vbLf & "FROM Car_Info" & vbCr & vbLf & "WHERE (EngineType = 'petrol')")
        ' Connect to the data source, and then update the metadata for the source.
        PetrolCarsrcDesignTime.AcquireConnections(Nothing)
        PetrolCarsrcDesignTime.ReinitializeMetaData()
        PetrolCarsrcDesignTime.ReleaseConnections()
 
        ' Add an Row Count to the data flow.
        Dim PetrolCarRowCountComponent As IDTSComponentMetaData100 = PetrolCarsMainPipeDataFlowTask.ComponentMetaDataCollection.[New]()
        PetrolCarRowCountComponent.Name = "Row Count"
        PetrolCarRowCountComponent.ComponentClassID = "DTSTransform.RowCount.2"
        'Public Token is: "{DE50D3C7-41AF-4804-9247-CF1DEB147971}";
        Dim PetrolCarrowCountDesignTime As CManagedComponentWrapper = PetrolCarRowCountComponent.Instantiate()
        PetrolCarrowCountDesignTime.ProvideComponentProperties()
        PetrolCarrowCountDesignTime.SetComponentProperty("VariableName", "RowCountVar")
        PetrolCarrowCountDesignTime.AcquireConnections(Nothing)
        PetrolCarrowCountDesignTime.ReinitializeMetaData()
        PetrolCarrowCountDesignTime.ReleaseConnections()
 
        ' Create the path from source to Row Count Transformation.
        Dim PetrolCarpathSource_RowCount As IDTSPath100 = PetrolCarsMainPipeDataFlowTask.PathCollection.[New]()
        PetrolCarpathSource_RowCount.AttachPathAndPropagateNotifications(PetrolCarsource.OutputCollection(0), PetrolCarRowCountComponent.InputCollection(0))
 
        ' Create and configure an OLE DB destination component.
        Dim PetrolCardestination As IDTSComponentMetaData100 = PetrolCarsMainPipeDataFlowTask.ComponentMetaDataCollection.[New]()
        PetrolCardestination.ComponentClassID = "DTSAdapter.OLEDBDestination.2"
        ' Create the design-time instance of the destination.
        Dim PetrolCardestDesignTime As CManagedComponentWrapper = PetrolCardestination.Instantiate()
        ' The ProvideComponentProperties method creates a default input.
        PetrolCardestDesignTime.ProvideComponentProperties()
        PetrolCardestination.Name = "Employee Dim from OLAP"
        ' Assign the connection manager.
        PetrolCardestination.RuntimeConnectionCollection(0).ConnectionManagerID = PetrolCarDconMgr.ID
        PetrolCardestination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.GetExtendedInterface(PetrolCarDconMgr)
        ' Set the custom properties.
        PetrolCardestDesignTime.SetComponentProperty("AccessMode", 3)
        ' Mode 3 : OpenRowset Using FastLoad / Table - View fast load
        PetrolCardestDesignTime.SetComponentProperty("OpenRowset", "dbo.PetrolCars")
        PetrolCardestDesignTime.AcquireConnections(Nothing)
        PetrolCardestDesignTime.ReinitializeMetaData()
        PetrolCardestDesignTime.ReleaseConnections()
 
 
        ' Create the path from Rowcount to destination.
        Dim PetrolCarpathRowCount_Dest As IDTSPath100 = PetrolCarsMainPipeDataFlowTask.PathCollection.[New]()
        PetrolCarpathRowCount_Dest.AttachPathAndPropagateNotifications(PetrolCarRowCountComponent.OutputCollection(0), PetrolCardestination.InputCollection(0))
 
 
        ' Get the destination's default input and virtual input.
        Dim PetrolCarinput As IDTSInput100 = PetrolCardestination.InputCollection(0)
        Dim PetrolCarvInput As IDTSVirtualInput100 = PetrolCarinput.GetVirtualInput()
        ' Iterate through the virtual input column collection.
        For Each vColumn As IDTSVirtualInputColumn100 In PetrolCarvInput.VirtualInputColumnCollection
            ' Call the SetUsageType method of the destination
            ' to add each available virtual input column as an input column.
            PetrolCardestDesignTime.SetUsageType(PetrolCarinput.ID, PetrolCarvInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
        Next
 
        'map external metadata to the inputcolumn
        For Each inputColumn As IDTSInputColumn100 In PetrolCarinput.InputColumnCollection
            Dim exMetaColumn As IDTSExternalMetadataColumn100 = DirectCast(PetrolCarinput.ExternalMetadataColumnCollection(inputColumn.Name), IDTSExternalMetadataColumn100)
            inputColumn.ExternalMetadataColumnID = exMetaColumn.ID
        Next
 
        '-----------Data Flow Inner component ends-------------------
 
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'To add "Load Cars(Diesel)"(Data Flow Task) inside "Load Diesel Type Cars" (sequence container)
        Dim DieselCarsDataFlowTask As TaskHost = DirectCast(LoadDieselTypeCarsSequenceContainer.Executables.Add("SSIS.Pipeline.2"), TaskHost)
        DieselCarsDataFlowTask.Name = "Load Cars(Diesel)"
        DieselCarsDataFlowTask.FailPackageOnFailure = True
        DieselCarsDataFlowTask.FailParentOnFailure = True
        DieselCarsDataFlowTask.DelayValidation = False
        DieselCarsDataFlowTask.Description = "Data Flow Task to load Diesel Cars"
 
        '-----------Data Flow Inner component starts----------------
        Dim DieselCarsMainPipeDataFlowTask As MainPipe = TryCast(DieselCarsDataFlowTask.InnerObject, MainPipe)
        ' Source OLE DB connection manager to the package.
        Dim DieselCarsconMgr As ConnectionManager = package.Connections("LocalHost.OLTP")
        ' Destination OLE DB connection manager to the package.
        Dim DieselCarDconMgr As ConnectionManager = package.Connections("LocalHost.OLAP")
 
        ' Create and configure an OLE DB source component. 
        Dim DieselCarsource As IDTSComponentMetaData100 = DieselCarsMainPipeDataFlowTask.ComponentMetaDataCollection.[New]()
        DieselCarsource.ComponentClassID = "DTSAdapter.OLEDBSource.2"
        ' Create the design-time instance of the source.
        Dim DieselCarsrcDesignTime As CManagedComponentWrapper = DieselCarsource.Instantiate()
        ' The ProvideComponentProperties method creates a default output.
        DieselCarsrcDesignTime.ProvideComponentProperties()
        DieselCarsource.Name = "Employee Dim from OLTP"
 
        ' Assign the connection manager.
        DieselCarsource.RuntimeConnectionCollection(0).ConnectionManagerID = DieselCarsconMgr.ID
        DieselCarsource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.GetExtendedInterface(DieselCarsconMgr)
        ' Set the custom properties of the source.
        DieselCarsrcDesignTime.SetComponentProperty("AccessMode", 2)
        ' Mode 0 : SqlCommand
        DieselCarsrcDesignTime.SetComponentProperty("SqlCommand", "SELECT CarCompany, CarBodyType, CarName, EngineType" & vbCr & vbLf & "FROM Car_Info" & vbCr & vbLf & "WHERE (EngineType = 'diesel')")
        ' Connect to the data source, and then update the metadata for the source.
        DieselCarsrcDesignTime.AcquireConnections(Nothing)
        DieselCarsrcDesignTime.ReinitializeMetaData()
        DieselCarsrcDesignTime.ReleaseConnections()
 
        ' Add an Row Count to the data flow.
        Dim DieselCarRowCountComponent As IDTSComponentMetaData100 = DieselCarsMainPipeDataFlowTask.ComponentMetaDataCollection.[New]()
        DieselCarRowCountComponent.Name = "Row Count"
        DieselCarRowCountComponent.ComponentClassID = "DTSTransform.RowCount.2"
        'Public Token is: "{DE50D3C7-41AF-4804-9247-CF1DEB147971}";
        Dim DieselCarrowCountDesignTime As CManagedComponentWrapper = DieselCarRowCountComponent.Instantiate()
        DieselCarrowCountDesignTime.ProvideComponentProperties()
        DieselCarrowCountDesignTime.SetComponentProperty("VariableName", "RowCountVar")
        DieselCarrowCountDesignTime.AcquireConnections(Nothing)
        DieselCarrowCountDesignTime.ReinitializeMetaData()
        DieselCarrowCountDesignTime.ReleaseConnections()
 
        ' Create the path from source to Row Count Transformation.
        Dim DieselCarpathSource_RowCount As IDTSPath100 = DieselCarsMainPipeDataFlowTask.PathCollection.[New]()
        DieselCarpathSource_RowCount.AttachPathAndPropagateNotifications(DieselCarsource.OutputCollection(0), DieselCarRowCountComponent.InputCollection(0))
 
        ' Create and configure an OLE DB destination component.
        Dim DieselCardestination As IDTSComponentMetaData100 = DieselCarsMainPipeDataFlowTask.ComponentMetaDataCollection.[New]()
        DieselCardestination.ComponentClassID = "DTSAdapter.OLEDBDestination.2"
        ' Create the design-time instance of the destination.
        Dim DieselCardestDesignTime As CManagedComponentWrapper = DieselCardestination.Instantiate()
        ' The ProvideComponentProperties method creates a default input.
        DieselCardestDesignTime.ProvideComponentProperties()
        DieselCardestination.Name = "Employee Dim from OLAP"
        ' Assign the connection manager.
        DieselCardestination.RuntimeConnectionCollection(0).ConnectionManagerID = DieselCarDconMgr.ID
        DieselCardestination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.GetExtendedInterface(DieselCarDconMgr)
        ' Set the custom properties.
        DieselCardestDesignTime.SetComponentProperty("AccessMode", 3)
        ' Mode 3 : OpenRowset Using FastLoad / Table - View fast load
        DieselCardestDesignTime.SetComponentProperty("OpenRowset", "dbo.DieselCars")
        DieselCardestDesignTime.AcquireConnections(Nothing)
        DieselCardestDesignTime.ReinitializeMetaData()
        DieselCardestDesignTime.ReleaseConnections()
 
 
        ' Create the path from Rowcount to destination.
        Dim DieselCarpathRowCount_Dest As IDTSPath100 = DieselCarsMainPipeDataFlowTask.PathCollection.[New]()
        DieselCarpathRowCount_Dest.AttachPathAndPropagateNotifications(DieselCarRowCountComponent.OutputCollection(0), DieselCardestination.InputCollection(0))
 
 
        ' Get the destination's default input and virtual input.
        Dim DieselCarinput As IDTSInput100 = DieselCardestination.InputCollection(0)
        Dim DieselCarvInput As IDTSVirtualInput100 = DieselCarinput.GetVirtualInput()
        ' Iterate through the virtual input column collection.
        For Each vColumn As IDTSVirtualInputColumn100 In DieselCarvInput.VirtualInputColumnCollection
            ' Call the SetUsageType method of the destination
            ' to add each available virtual input column as an input column.
            DieselCardestDesignTime.SetUsageType(DieselCarinput.ID, DieselCarvInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
        Next
 
        'map external metadata to the inputcolumn
        For Each inputColumn As IDTSInputColumn100 In DieselCarinput.InputColumnCollection
            Dim exMetaColumn As IDTSExternalMetadataColumn100 = DirectCast(DieselCarinput.ExternalMetadataColumnCollection(inputColumn.Name), IDTSExternalMetadataColumn100)
            inputColumn.ExternalMetadataColumnID = exMetaColumn.ID
        Next
 
        '-----------Data Flow Inner component ends-------------------
 
 
        '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

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.

References:
MSDN Books Online

Categories: SSIS, SSIS programming

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

No comments yet.

No trackbacks yet.

Duplicate Remover SSIS Component Explorer

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 Sequence Container

All Posts

Recent Posts

Categories

Archives

Recent Comments