Programming Sequence Container
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:
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.
Download the free version from here.
References:
MSDN Books Online
Categories: SSIS, SSIS programming
Recent Comments