Programming SSIS Logging

Home > SSIS, SSIS programming > Programming SSIS Logging

Programming SSIS Logging

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

SQL Server provides us with a powerful Data Integration service called SSIS. But to make it more robust and effective, we need to monitor some of the vital measures so as to increase its performance, troubleshooting errors and to keep track of the data flow. The best way provided by SSIS to accomplish this is SSIS Logging. SSIS will log entries in the log file or table on the basis of the events that occur during the execution of the package.

Here in this section, we will tell you about different features of SSIS logging and the implementation of the same by programming Integration Services.

Here we will consider the same package used in “SSIS Programming Basic”
To start with SSIS Logging, click on “SSIS” in the menu bar or right click on empty area in Control flow and select “Logging…”

You will get a dialog box as below. The logging is disabled by default.

To enable it, enable the checkbox in the Containers pane and a Provider type for the Logging. Let’s consider the Provider type is Text file i.e. “SSIS Log Provider for Text Files” and click on Add… button.

Provide a File Connection manager for Logging. Let the connection manager name is “SSIS Log.txt” (same as the file name but you can give different name for this)

Set the properties of the log provider as given below in the figure. Select the log provider.

Let’s consider that we are going to implement logging at the package level. We need information regarding “OnError” and “OnWarning” events.

List of events that are supported by SSIS are:

OnError

OnExecStatusChanged

OnInformation

OnPipelinePostEndOfRowset

OnPipelinePostPrimeOutput

OnPipelinePreEndOfRowset

OnPipelinePrePrimeOutput

OnPipelineRowsSent

OnPostExecute

OnPostValidate

OnPreExecute

OnPreValidate

OnProgress

OnQueryCancel

OnTaskFailed

OnVariableValueChanged

OnWarning

Diagnostic

[Note: The description for each of the events is given in "Configure SSIS Logs" dialog box.]

Click on Details tab to select the events that we need to track. And then click on Advanced >> button to get more options for a particular event.

Below is the list of additional information that we can log for a particular event:

Value	Description
Computer	The name of the computer on which the logged event occurred.
Operator	The user name of the person who started the package.
SourceName	The name of the package, container, or task in which the logged event occurred.
SourceID	The global unique identifier (GUID) of the package, container, or task in which the logged event occurred.
ExecutionID	The global unique identifier of the package execution instance.
MessageText	A message associated with the log entry.
DataBytes	Reserved for future use.

Let’s consider we are interested to keep Computer, SourceName and MessageText as logging information for our package as shown below.

Click OK, save the package and execute it.

You can see the logs generated in the file “SSIS Log.txt“.

Let’s do the same logging mechanism by SSIS programming.

Steps to follow for implementing logging mechanism in SSIS package:

Step1: Enable the logging mode of the package

package.LoggingMode = DTSLoggingMode.Enabled;

Step2: Create Log file connection manager, for our case it is a flat file named “SSIS Log.txt”

Step3: Add a package log provider. In SSIS 2005 the CreationName is “DTS.LogProviderTextFile.1″

Step4: Assign connection manager to the logging provider.

Step5: Select the events that has to be considered for logging, i.e. “OnError”, OnWarning”

Step6: Select the options for each events that has to be taken for gathering information while the package is running, i.e. Computer, SourceName and MessageText. This can be achieved by using the structure DTSEventColumnFilter.

Step7: Set column filter to its respective events.

Step8: Add the rest of the components of the package.

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

Note: SQL Lion team had developed one application name “Expert Logger“ to set the SSIS logging mechanism batch wise i.e. implementation of logging to any number of packages at a time. Download the free version from here.

The coding can be done in C# .net or VB .net. 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 Logging 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;
 
            //Logging implementation starts
            package.LoggingMode = DTSLoggingMode.Enabled;
            //For package logging connection manager (flat file)
            ConnectionManager LogConMgr = package.Connections.Add("FILE");
            LogConMgr.ConnectionString = @"I:Site workSSIS Log.txt";
            LogConMgr.Name = "SSIS Log.txt";
 
            LogProvider packagelogprovider = package.LogProviders.Add("DTS.LogProviderTextFile.1");
            packagelogprovider.ConfigString = LogConMgr.Name;
            packagelogprovider.Name = "Text File Log Provider";
            packagelogprovider.Description = "Writes log entries for events to a CSV file";
 
            package.LoggingOptions.SelectedLogProviders.Add(packagelogprovider);
            LoggingOptions packageLogging = package.LoggingOptions;
            packageLogging.EventFilterKind = DTSEventFilterKind.Inclusion;
            packageLogging.EventFilter.Initialize();
            packageLogging.EventFilter = new string[] { 
                    "OnError",
                    "OnWarning" };
            //OnError Event 
            DTSEventColumnFilter OnErrorColumnFilter = new DTSEventColumnFilter();
            OnErrorColumnFilter.Computer = true; 
            OnErrorColumnFilter.SourceName = true; 
            OnErrorColumnFilter.MessageText = true; 
            packageLogging.SetColumnFilter("OnError", OnErrorColumnFilter);
 
            //OnWarning Event   
            DTSEventColumnFilter OnWarningColumnFilter = new DTSEventColumnFilter();
            OnWarningColumnFilter.Computer = true;
            OnWarningColumnFilter.SourceName = true;
            OnWarningColumnFilter.MessageText = true; 
            packageLogging.SetColumnFilter("OnWarning", OnWarningColumnFilter);
 
            //Logging implementation ends
 
 
            //To add Connection Manager to the package
            //For source database (OLTP)
            ConnectionManager OLTP = package.Connections.Add("OLEDB");
            OLTP.ConnectionString = "Data Source=.;Initial Catalog=OLTP;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";
            OLTP.Name = "LocalHost.OLTP";
            //For destination database (OLAP)
            ConnectionManager OLAP = package.Connections.Add("OLEDB");
            OLAP.ConnectionString = "Data Source=.;Initial Catalog=OLAP;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";
            OLAP.Name = "LocalHost.OLAP";
 
            //To add package variables
            Variable TransactionID = package.Variables.Add("TransactionID", false, "User", 0);
            TransactionID.Name = @"TransactionID";
 
            Variable RowCountVar = package.Variables.Add("RowCountVar", false, "User", 0);
            RowCountVar.Name = @"RowCountVar";
 
            //To add Package Start Indicator (Execute Sql task )
            TaskHost PackageStartIndicatorTask = (TaskHost)package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
            PackageStartIndicatorTask.Name = @"Package Start Indicator";
            PackageStartIndicatorTask.Description = @"Execute SQL Task Description";
            XmlDocument PackageStartIndicatorTaskdoc = new XmlDocument();
            PackageStartIndicatorTaskdoc.LoadXml(@"<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.InitTransaction"" SQLTask:ResultType=""ResultSetType_SingleRow"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ResultBinding SQLTask:ResultName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" /></SQLTask:SqlTaskData>");
            ((IDTSComponentPersist)PackageStartIndicatorTask.InnerObject).LoadFromXML(PackageStartIndicatorTaskdoc.DocumentElement, null);
 
            //To add Load Employee Dim to the package [Data Flow Task]
            TaskHost dataFlowTaskHost = (TaskHost)package.Executables.Add("DTS.Pipeline.1");
            dataFlowTaskHost.Name = @"Load Employee Dim";
            dataFlowTaskHost.FailPackageOnFailure = true;
            dataFlowTaskHost.FailParentOnFailure = true;
            dataFlowTaskHost.DelayValidation = false;
            dataFlowTaskHost.Description = @"Data Flow Task";
 
            //-----------Data Flow Inner component starts----------------
            MainPipe dataFlowTask = dataFlowTaskHost.InnerObject as MainPipe;
            // Source OLE DB connection manager to the package.
            ConnectionManager SconMgr = package.Connections["LocalHost.OLTP"];
            // Destination OLE DB connection manager to the package.
            ConnectionManager DconMgr = package.Connections["LocalHost.OLAP"];
 
            // Create and configure an OLE DB source component.  
            IDTSComponentMetaData90 source = dataFlowTask.ComponentMetaDataCollection.New();
            source.ComponentClassID = "DTSAdapter.OLEDBSource.1";
 
            // Create the design-time instance of the source.
            CManagedComponentWrapper srcDesignTime = source.Instantiate();
            // The ProvideComponentProperties method creates a default output.
            srcDesignTime.ProvideComponentProperties();
            source.Name = "Employee Dim from OLTP";
 
            // Assign the connection manager.
            source.RuntimeConnectionCollection[0].ConnectionManagerID = SconMgr.ID;
            source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(SconMgr);
            // Set the custom properties of the source.
            srcDesignTime.SetComponentProperty("AccessMode", 0); // Mode 0 : OpenRowset / Table - View
            srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Employee_Dim]");
            // Connect to the data source, and then update the metadata for the source.
            srcDesignTime.AcquireConnections(null);
            srcDesignTime.ReinitializeMetaData();
            srcDesignTime.ReleaseConnections();
 
 
 
            // Add an Row Count to the data flow.
            IDTSComponentMetaData90 RowCountComponent = dataFlowTask.ComponentMetaDataCollection.New();
            RowCountComponent.Name = "Row Count";
            RowCountComponent.ComponentClassID = "DTSTransform.RowCount.1"; //Public Token is: "{DE50D3C7-41AF-4804-9247-CF1DEB147971}";
            CManagedComponentWrapper rowCountDesignTime = RowCountComponent.Instantiate();
            rowCountDesignTime.ProvideComponentProperties();
            rowCountDesignTime.SetComponentProperty("VariableName", "RowCountVar");
            rowCountDesignTime.AcquireConnections(null);
            rowCountDesignTime.ReinitializeMetaData();
            rowCountDesignTime.ReleaseConnections();
 
            // Create the path from source to Row Count Transformation.
            IDTSPath90 pathSource_RowCount = dataFlowTask.PathCollection.New();
            pathSource_RowCount.AttachPathAndPropagateNotifications(source.OutputCollection[0], RowCountComponent.InputCollection[0]);
 
            // Create and configure an OLE DB destination component.
            IDTSComponentMetaData90 destination = dataFlowTask.ComponentMetaDataCollection.New();
            destination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";
            // Create the design-time instance of the destination.
            CManagedComponentWrapper destDesignTime = destination.Instantiate();
            // The ProvideComponentProperties method creates a default input.
            destDesignTime.ProvideComponentProperties();
            destination.Name = "Employee Dim from OLAP";
            // Assign the connection manager.
            destination.RuntimeConnectionCollection[0].ConnectionManagerID = DconMgr.ID;
            destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(DconMgr);
            // Set the custom properties.
            destDesignTime.SetComponentProperty("AccessMode", 3); // Mode 3 : OpenRowset Using FastLoad  / Table - View fast load
            destDesignTime.SetComponentProperty("OpenRowset", "[EmployeeDim]");
            destDesignTime.AcquireConnections(null);
            destDesignTime.ReinitializeMetaData();
            destDesignTime.ReleaseConnections();
 
 
            // Create the path from Rowcount to destination.
            IDTSPath90 pathRowCount_Dest = dataFlowTask.PathCollection.New();
            pathRowCount_Dest.AttachPathAndPropagateNotifications(RowCountComponent.OutputCollection[0], destination.InputCollection[0]);
 
 
            // Get the destination's default input and virtual input.
            IDTSInput90 input = destination.InputCollection[0];
            IDTSVirtualInput90 vInput = input.GetVirtualInput();
            // Iterate through the virtual input column collection.
            foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
            {
                // Call the SetUsageType method of the destination
                //  to add each available virtual input column as an input column.
                destDesignTime.SetUsageType(
                   input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
            }
 
            //map external metadata to the inputcolumn
            foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)
            {
                IDTSExternalMetadataColumn90 exMetaColumn = (IDTSExternalMetadataColumn90)input.ExternalMetadataColumnCollection[inputColumn.Name];
                inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
            }
 
            //-----------Data Flow Inner component ends-------------------
 
            // Precedence constraints from "Package Start Indicator" to "Load Employee Dim"
            PrecedenceConstraint PackageStartIndicatorTask_dataFlowTaskHost = package.PrecedenceConstraints.Add(package.Executables[@"Package Start Indicator"], package.Executables[@"Load Employee Dim"]);
            PackageStartIndicatorTask_dataFlowTaskHost.Name = @"Package Start Indicator" + @"Load Employee Dim";
 
 
            //To add Package End Indicator (Execute Sql task )
            TaskHost PackageEndIndicatorTask = (TaskHost)package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
            PackageEndIndicatorTask.Name = @"Package End Indicator";
            PackageEndIndicatorTask.Description = @"Execute SQL Task Description";
            XmlDocument PackageEndIndicatorTaskdoc = new XmlDocument();
            PackageEndIndicatorTaskdoc.LoadXml(@"<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.EndTransaction ?, ?"" SQLTask:ResultType=""ResultSetType_None"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ParameterBinding SQLTask:ParameterName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /><SQLTask:ParameterBinding SQLTask:ParameterName=""1"" SQLTask:DtsVariableName=""User::RowCountVar"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /></SQLTask:SqlTaskData>");
            ((IDTSComponentPersist)PackageEndIndicatorTask.InnerObject).LoadFromXML(PackageEndIndicatorTaskdoc.DocumentElement, null);
 
            // Precedence constraints from "Load Employee Dim" to "Package End Indicator"
            PrecedenceConstraint dataFlowTaskHost_PackageEndIndicatorTask = package.PrecedenceConstraints.Add(package.Executables[@"Load Employee Dim"], package.Executables[@"Package End Indicator"]);
            dataFlowTaskHost_PackageEndIndicatorTask.Name = @"Load Employee Dim" + @"Package End Indicator";
 
            //Saving the package
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
            app.SaveToXml(@"I:Site workSamplePackageSamplePackageSamplePackage.dtsx", package, null);
        }
    }
}

VB .Net Expand

[ Copy to Clipboard ] | [ View Source ]

Imports System
Imports System.Collections.Generic
Imports System.Text
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports System.Data.SqlClient
Imports System.Xml
 
 
'The below code will create a SSIS package with Logging 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
 
        'Logging implementation starts
        package.LoggingMode = DTSLoggingMode.Enabled
        'For package logging connection manager (flat file)
        Dim LogConMgr As ConnectionManager = package.Connections.Add("FILE")
        LogConMgr.ConnectionString = "I:Site workSSIS Log.txt"
        LogConMgr.Name = "SSIS Log.txt"
 
        Dim packagelogprovider As LogProvider = package.LogProviders.Add("DTS.LogProviderTextFile.1")
        packagelogprovider.ConfigString = LogConMgr.Name
        packagelogprovider.Name = "Text File Log Provider"
        packagelogprovider.Description = "Writes log entries for events to a CSV file"
 
        package.LoggingOptions.SelectedLogProviders.Add(packagelogprovider)
        Dim packageLogging As LoggingOptions = package.LoggingOptions
        packageLogging.EventFilterKind = DTSEventFilterKind.Inclusion
        packageLogging.EventFilter.Initialize()
        packageLogging.EventFilter = New String() {"OnError", "OnWarning"}
        'OnError Event 
        Dim OnErrorColumnFilter As New DTSEventColumnFilter()
        OnErrorColumnFilter.Computer = True
        OnErrorColumnFilter.SourceName = True
        OnErrorColumnFilter.MessageText = True
        packageLogging.SetColumnFilter("OnError", OnErrorColumnFilter)
 
        'OnWarning Event 
        Dim OnWarningColumnFilter As New DTSEventColumnFilter()
        OnWarningColumnFilter.Computer = True
        OnWarningColumnFilter.SourceName = True
        OnWarningColumnFilter.MessageText = True
        packageLogging.SetColumnFilter("OnWarning", OnWarningColumnFilter)
 
        'Logging implementation ends
 
        'To add Connection Manager to the package
        'For source database (OLTP)
        Dim OLTP As ConnectionManager = package.Connections.Add("OLEDB")
        OLTP.ConnectionString = "Data Source=.;Initial Catalog=OLTP;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"
        OLTP.Name = "LocalHost.OLTP"
        'For destination database (OLAP)
        Dim OLAP As ConnectionManager = package.Connections.Add("OLEDB")
        OLAP.ConnectionString = "Data Source=.;Initial Catalog=OLAP;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"
        OLAP.Name = "LocalHost.OLAP"
 
        'To add package variables
        Dim TransactionID As Variable = package.Variables.Add("TransactionID", False, "User", 0)
        TransactionID.Name = "TransactionID"
 
        Dim RowCountVar As Variable = package.Variables.Add("RowCountVar", False, "User", 0)
        RowCountVar.Name = "RowCountVar"
 
        'To add Package Start Indicator (Execute Sql task )
        Dim PackageStartIndicatorTask As TaskHost = DirectCast(package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"), TaskHost)
        PackageStartIndicatorTask.Name = "Package Start Indicator"
        PackageStartIndicatorTask.Description = "Execute SQL Task Description"
        Dim PackageStartIndicatorTaskdoc As New XmlDocument()
        PackageStartIndicatorTaskdoc.LoadXml("<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.InitTransaction"" SQLTask:ResultType=""ResultSetType_SingleRow"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ResultBinding SQLTask:ResultName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" /></SQLTask:SqlTaskData>")
        DirectCast(PackageStartIndicatorTask.InnerObject, IDTSComponentPersist).LoadFromXML(PackageStartIndicatorTaskdoc.DocumentElement, Nothing)
 
        'To add Load Employee Dim to the package [Data Flow Task]
        Dim dataFlowTaskHost As TaskHost = DirectCast(package.Executables.Add("DTS.Pipeline.1"), TaskHost)
        dataFlowTaskHost.Name = "Load Employee Dim"
        dataFlowTaskHost.FailPackageOnFailure = True
        dataFlowTaskHost.FailParentOnFailure = True
        dataFlowTaskHost.DelayValidation = False
        dataFlowTaskHost.Description = "Data Flow Task"
 
        '-----------Data Flow Inner component starts----------------
        Dim dataFlowTask As MainPipe = TryCast(dataFlowTaskHost.InnerObject, MainPipe)
        ' Source OLE DB connection manager to the package.
        Dim SconMgr As ConnectionManager = package.Connections("LocalHost.OLTP")
        ' Destination OLE DB connection manager to the package.
        Dim DconMgr As ConnectionManager = package.Connections("LocalHost.OLAP")
 
        ' Create and configure an OLE DB source component. 
        Dim source As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.[New]()
        source.ComponentClassID = "DTSAdapter.OLEDBSource.1"
 
        ' Create the design-time instance of the source.
        Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate()
        ' The ProvideComponentProperties method creates a default output.
        srcDesignTime.ProvideComponentProperties()
        source.Name = "Employee Dim from OLTP"
 
        ' Assign the connection manager.
        source.RuntimeConnectionCollection(0).ConnectionManagerID = SconMgr.ID
        source.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(SconMgr)
        ' Set the custom properties of the source.
        srcDesignTime.SetComponentProperty("AccessMode", 0)
        ' Mode 0 : OpenRowset / Table - View
        srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Employee_Dim]")
        ' Connect to the data source, and then update the metadata for the source.
        srcDesignTime.AcquireConnections(Nothing)
        srcDesignTime.ReinitializeMetaData()
        srcDesignTime.ReleaseConnections()
 
 
 
        ' Add an Row Count to the data flow.
        Dim RowCountComponent As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.[New]()
        RowCountComponent.Name = "Row Count"
        RowCountComponent.ComponentClassID = "DTSTransform.RowCount.1"
        'Public Token is: "{DE50D3C7-41AF-4804-9247-CF1DEB147971}";
        Dim rowCountDesignTime As CManagedComponentWrapper = RowCountComponent.Instantiate()
        rowCountDesignTime.ProvideComponentProperties()
        rowCountDesignTime.SetComponentProperty("VariableName", "RowCountVar")
        rowCountDesignTime.AcquireConnections(Nothing)
        rowCountDesignTime.ReinitializeMetaData()
        rowCountDesignTime.ReleaseConnections()
 
        ' Create the path from source to Row Count Transformation.
        Dim pathSource_RowCount As IDTSPath90 = dataFlowTask.PathCollection.[New]()
        pathSource_RowCount.AttachPathAndPropagateNotifications(source.OutputCollection(0), RowCountComponent.InputCollection(0))
 
        ' Create and configure an OLE DB destination component.
        Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.[New]()
        destination.ComponentClassID = "DTSAdapter.OLEDBDestination.1"
        ' Create the design-time instance of the destination.
        Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate()
        ' The ProvideComponentProperties method creates a default input.
        destDesignTime.ProvideComponentProperties()
        destination.Name = "Employee Dim from OLAP"
        ' Assign the connection manager.
        destination.RuntimeConnectionCollection(0).ConnectionManagerID = DconMgr.ID
        destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(DconMgr)
        ' Set the custom properties.
        destDesignTime.SetComponentProperty("AccessMode", 3)
        ' Mode 3 : OpenRowset Using FastLoad / Table - View fast load
        destDesignTime.SetComponentProperty("OpenRowset", "[EmployeeDim]")
        destDesignTime.AcquireConnections(Nothing)
        destDesignTime.ReinitializeMetaData()
        destDesignTime.ReleaseConnections()
 
 
        ' Create the path from Rowcount to destination.
        Dim pathRowCount_Dest As IDTSPath90 = dataFlowTask.PathCollection.[New]()
        pathRowCount_Dest.AttachPathAndPropagateNotifications(RowCountComponent.OutputCollection(0), destination.InputCollection(0))
 
 
        ' Get the destination's default input and virtual input.
        Dim input As IDTSInput90 = destination.InputCollection(0)
        Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()
        ' Iterate through the virtual input column collection.
        For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
            ' Call the SetUsageType method of the destination
            ' to add each available virtual input column as an input column.
            destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
        Next
 
        'map external metadata to the inputcolumn
        For Each inputColumn As IDTSInputColumn90 In input.InputColumnCollection
            Dim exMetaColumn As IDTSExternalMetadataColumn90 = DirectCast(input.ExternalMetadataColumnCollection(inputColumn.Name), IDTSExternalMetadataColumn90)
            inputColumn.ExternalMetadataColumnID = exMetaColumn.ID
        Next
 
        '-----------Data Flow Inner component ends-------------------
 
        ' Precedence constraints from "Package Start Indicator" to "Load Employee Dim"
        Dim PackageStartIndicatorTask_dataFlowTaskHost As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Package Start Indicator"), package.Executables("Load Employee Dim"))
        PackageStartIndicatorTask_dataFlowTaskHost.Name = "Package Start Indicator" & "Load Employee Dim"
 
 
        'To add Package End Indicator (Execute Sql task )
        Dim PackageEndIndicatorTask As TaskHost = DirectCast(package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"), TaskHost)
        PackageEndIndicatorTask.Name = "Package End Indicator"
        PackageEndIndicatorTask.Description = "Execute SQL Task Description"
        Dim PackageEndIndicatorTaskdoc As New XmlDocument()
        PackageEndIndicatorTaskdoc.LoadXml("<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.EndTransaction ?, ?"" SQLTask:ResultType=""ResultSetType_None"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ParameterBinding SQLTask:ParameterName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /><SQLTask:ParameterBinding SQLTask:ParameterName=""1"" SQLTask:DtsVariableName=""User::RowCountVar"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /></SQLTask:SqlTaskData>")
        DirectCast(PackageEndIndicatorTask.InnerObject, IDTSComponentPersist).LoadFromXML(PackageEndIndicatorTaskdoc.DocumentElement, Nothing)
 
        ' Precedence constraints from "Load Employee Dim" to "Package End Indicator"
        Dim dataFlowTaskHost_PackageEndIndicatorTask As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Load Employee Dim"), package.Executables("Package End Indicator"))
        dataFlowTaskHost_PackageEndIndicatorTask.Name = "Load Employee Dim" & "Package End Indicator"
 
        'Saving the package
        Dim app As New Microsoft.SqlServer.Dts.Runtime.Application()
        app.SaveToXml("I:Site workSamplePackageSamplePackageSamplePackage.dtsx", package, Nothing)
    End Sub
End Module

Programming Integration Service in SQL Server 2008

C# .Net Expand

[ Copy to Clipboard ] | [ View Source ]

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Data.SqlClient;
using System.Xml;
 
namespace SamplePackageCSharp2008
{
    class Program
    {
        static void Main(string[] args)
        {
            //To Create a package named [Sample Package]
            Package package = new Package();
            package.Name = "Sample Package";
            package.PackageType = DTSPackageType.DTSDesigner100;
            package.VersionBuild = 1;
 
            //Logging implementation starts
            package.LoggingMode = DTSLoggingMode.Enabled;
            //For package logging connection manager (flat file)
            ConnectionManager LogConMgr = package.Connections.Add("FILE");
            LogConMgr.ConnectionString = @"I:Site workSSIS Log.txt";
            LogConMgr.Name = "SSIS Log.txt";
 
            LogProvider packagelogprovider = package.LogProviders.Add("DTS.LogProviderTextFile.2");
            packagelogprovider.ConfigString = LogConMgr.Name;
            packagelogprovider.Name = "Text File Log Provider";
            packagelogprovider.Description = "Writes log entries for events to a CSV file";
 
            package.LoggingOptions.SelectedLogProviders.Add(packagelogprovider);
            LoggingOptions packageLogging = package.LoggingOptions;
            packageLogging.EventFilterKind = DTSEventFilterKind.Inclusion;
            packageLogging.EventFilter.Initialize();
            packageLogging.EventFilter = new string[] { 
                    "OnError",
                    "OnWarning" };
            //OnError Event 
            DTSEventColumnFilter OnErrorColumnFilter = new DTSEventColumnFilter();
            OnErrorColumnFilter.Computer = true;
            OnErrorColumnFilter.SourceName = true;
            OnErrorColumnFilter.MessageText = true;
            packageLogging.SetColumnFilter("OnError", OnErrorColumnFilter);
 
            //OnWarning Event   
            DTSEventColumnFilter OnWarningColumnFilter = new DTSEventColumnFilter();
            OnWarningColumnFilter.Computer = true;
            OnWarningColumnFilter.SourceName = true;
            OnWarningColumnFilter.MessageText = true;
            packageLogging.SetColumnFilter("OnWarning", OnWarningColumnFilter);
 
            //Logging implementation ends
 
            //To add Connection Manager to the package
            //For source database (OLTP)
            ConnectionManager OLTP = package.Connections.Add("OLEDB");
            OLTP.ConnectionString = "Data Source=.;Initial Catalog=OLTP;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
            OLTP.Name = "LocalHost.OLTP";
            //For destination database (OLAP)
            ConnectionManager OLAP = package.Connections.Add("OLEDB");
            OLAP.ConnectionString = "Data Source=.;Initial Catalog=OLAP;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
            OLAP.Name = "LocalHost.OLAP";
 
            //To add package variables
            Variable TransactionID = package.Variables.Add("TransactionID", false, "User", 0);
            TransactionID.Name = @"TransactionID";
 
            Variable RowCountVar = package.Variables.Add("RowCountVar", false, "User", 0);
            RowCountVar.Name = @"RowCountVar";
 
            //To add Package Start Indicator (Execute Sql task )
            TaskHost PackageStartIndicatorTask = (TaskHost)package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
            PackageStartIndicatorTask.Name = @"Package Start Indicator";
            PackageStartIndicatorTask.Description = @"Execute SQL Task Description";
            XmlDocument PackageStartIndicatorTaskdoc = new XmlDocument();
            PackageStartIndicatorTaskdoc.LoadXml(@"<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.InitTransaction"" SQLTask:ResultType=""ResultSetType_SingleRow"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ResultBinding SQLTask:ResultName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" /></SQLTask:SqlTaskData>");
            ((IDTSComponentPersist)PackageStartIndicatorTask.InnerObject).LoadFromXML(PackageStartIndicatorTaskdoc.DocumentElement, null);
 
            //To add Load Employee Dim to the package [Data Flow Task]
            TaskHost dataFlowTaskHost = (TaskHost)package.Executables.Add("SSIS.Pipeline.2");
            dataFlowTaskHost.Name = @"Load Employee Dim";
            dataFlowTaskHost.FailPackageOnFailure = true;
            dataFlowTaskHost.FailParentOnFailure = true;
            dataFlowTaskHost.DelayValidation = false;
            dataFlowTaskHost.Description = @"Data Flow Task";
 
            //-----------Data Flow Inner component starts----------------
            MainPipe dataFlowTask = dataFlowTaskHost.InnerObject as MainPipe;
            // Source OLE DB connection manager to the package.
            ConnectionManager SconMgr = package.Connections["LocalHost.OLTP"];
            // Destination OLE DB connection manager to the package.
            ConnectionManager DconMgr = package.Connections["LocalHost.OLAP"];
 
            // Create and configure an OLE DB source component.  
            IDTSComponentMetaData100 source = dataFlowTask.ComponentMetaDataCollection.New();
            source.ComponentClassID = "DTSAdapter.OLEDBSource.2";
 
            // Create the design-time instance of the source.
            CManagedComponentWrapper srcDesignTime = source.Instantiate();
            // The ProvideComponentProperties method creates a default output.
            srcDesignTime.ProvideComponentProperties();
            source.Name = "Employee Dim from OLTP";
 
            // Assign the connection manager.
            source.RuntimeConnectionCollection[0].ConnectionManagerID = SconMgr.ID;
            source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(SconMgr);
            // Set the custom properties of the source.
            srcDesignTime.SetComponentProperty("AccessMode", 0); // Mode 0 : OpenRowset / Table - View
            srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Employee_Dim]");
            // Connect to the data source, and then update the metadata for the source.
            srcDesignTime.AcquireConnections(null);
            srcDesignTime.ReinitializeMetaData();
            srcDesignTime.ReleaseConnections();
 
 
 
            // Add an Row Count to the data flow.
            IDTSComponentMetaData100 RowCountComponent = dataFlowTask.ComponentMetaDataCollection.New();
            RowCountComponent.Name = "Row Count";
            RowCountComponent.ComponentClassID = "DTSTransform.RowCount.2"; 
            CManagedComponentWrapper rowCountDesignTime = RowCountComponent.Instantiate();
            rowCountDesignTime.ProvideComponentProperties();
            rowCountDesignTime.SetComponentProperty("VariableName", "RowCountVar");
            rowCountDesignTime.AcquireConnections(null);
            rowCountDesignTime.ReinitializeMetaData();
            rowCountDesignTime.ReleaseConnections();
 
            // Create the path from source to Row Count Transformation.
            IDTSPath100 pathSource_RowCount = dataFlowTask.PathCollection.New();
            pathSource_RowCount.AttachPathAndPropagateNotifications(source.OutputCollection[0], RowCountComponent.InputCollection[0]);
 
            // Create and configure an OLE DB destination component.
            IDTSComponentMetaData100 destination = dataFlowTask.ComponentMetaDataCollection.New();
            destination.ComponentClassID = "DTSAdapter.OLEDBDestination.2";
            // Create the design-time instance of the destination.
            CManagedComponentWrapper destDesignTime = destination.Instantiate();
            // The ProvideComponentProperties method creates a default input.
            destDesignTime.ProvideComponentProperties();
            destination.Name = "Employee Dim from OLAP";
            // Assign the connection manager.
            destination.RuntimeConnectionCollection[0].ConnectionManagerID = DconMgr.ID;
            destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(DconMgr);
            // Set the custom properties.
            destDesignTime.SetComponentProperty("AccessMode", 3); // Mode 3 : OpenRowset Using FastLoad  / Table - View fast load
            destDesignTime.SetComponentProperty("OpenRowset", "[EmployeeDim]");
            destDesignTime.AcquireConnections(null);
            destDesignTime.ReinitializeMetaData();
            destDesignTime.ReleaseConnections();
 
 
            // Create the path from Rowcount to destination.
            IDTSPath100 pathRowCount_Dest = dataFlowTask.PathCollection.New();
            pathRowCount_Dest.AttachPathAndPropagateNotifications(RowCountComponent.OutputCollection[0], destination.InputCollection[0]);
 
 
            // Get the destination's default input and virtual input.
            IDTSInput100 input = destination.InputCollection[0];
            IDTSVirtualInput100 vInput = input.GetVirtualInput();
            // Iterate through the virtual input column collection.
            foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
            {
                // Call the SetUsageType method of the destination
                //  to add each available virtual input column as an input column.
                destDesignTime.SetUsageType(
                   input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
            }
 
            //map external metadata to the inputcolumn
            //map external metadata to the inputcolumn
            foreach (IDTSInputColumn100 inputColumn in input.InputColumnCollection)
            {
                IDTSExternalMetadataColumn100 exMetaColumn = (IDTSExternalMetadataColumn100)input.ExternalMetadataColumnCollection[inputColumn.Name];
                inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
            }
 
            //-----------Data Flow Inner component ends-------------------
 
            // Precedence constraints from "Package Start Indicator" to "Load Employee Dim"
            PrecedenceConstraint PackageStartIndicatorTask_dataFlowTaskHost = package.PrecedenceConstraints.Add(package.Executables[@"Package Start Indicator"], package.Executables[@"Load Employee Dim"]);
            PackageStartIndicatorTask_dataFlowTaskHost.Name = @"Package Start Indicator" + @"Load Employee Dim";
 
 
            //To add Package End Indicator (Execute Sql task )
            TaskHost PackageEndIndicatorTask = (TaskHost)package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
            PackageEndIndicatorTask.Name = @"Package End Indicator";
            PackageEndIndicatorTask.Description = @"Execute SQL Task Description";
            XmlDocument PackageEndIndicatorTaskdoc = new XmlDocument();
            PackageEndIndicatorTaskdoc.LoadXml(@"<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.EndTransaction ?, ?"" SQLTask:ResultType=""ResultSetType_None"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ParameterBinding SQLTask:ParameterName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /><SQLTask:ParameterBinding SQLTask:ParameterName=""1"" SQLTask:DtsVariableName=""User::RowCountVar"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /></SQLTask:SqlTaskData>");
            ((IDTSComponentPersist)PackageEndIndicatorTask.InnerObject).LoadFromXML(PackageEndIndicatorTaskdoc.DocumentElement, null);
 
            // Precedence constraints from "Load Employee Dim" to "Package End Indicator"
            PrecedenceConstraint dataFlowTaskHost_PackageEndIndicatorTask = package.PrecedenceConstraints.Add(package.Executables[@"Load Employee Dim"], package.Executables[@"Package End Indicator"]);
            dataFlowTaskHost_PackageEndIndicatorTask.Name = @"Load Employee Dim" + @"Package End Indicator";
 
            //Saving the package
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
            app.SaveToXml(@"I:Site workSamplePackage 2008SamplePackageSamplePackage.dtsx", package, null);
        }
    }
}

VB .Net Expand

[ Copy to Clipboard ] | [ View Source ]

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports System.Data.SqlClient
Imports System.Xml
 
'The below code will create a SSIS package with Logging 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
 
        'Logging implementation starts
        package.LoggingMode = DTSLoggingMode.Enabled
        'For package logging connection manager (flat file)
        Dim LogConMgr As ConnectionManager = package.Connections.Add("FILE")
        LogConMgr.ConnectionString = "I:Site workSSIS Log.txt"
        LogConMgr.Name = "SSIS Log.txt"
 
        Dim packagelogprovider As LogProvider = package.LogProviders.Add("DTS.LogProviderTextFile.2")
        packagelogprovider.ConfigString = LogConMgr.Name
        packagelogprovider.Name = "Text File Log Provider"
        packagelogprovider.Description = "Writes log entries for events to a CSV file"
 
        package.LoggingOptions.SelectedLogProviders.Add(packagelogprovider)
        Dim packageLogging As LoggingOptions = package.LoggingOptions
        packageLogging.EventFilterKind = DTSEventFilterKind.Inclusion
        packageLogging.EventFilter.Initialize()
        packageLogging.EventFilter = New String() {"OnError", "OnWarning"}
        'OnError Event 
        Dim OnErrorColumnFilter As New DTSEventColumnFilter()
        OnErrorColumnFilter.Computer = True
        OnErrorColumnFilter.SourceName = True
        OnErrorColumnFilter.MessageText = True
        packageLogging.SetColumnFilter("OnError", OnErrorColumnFilter)
 
        'OnWarning Event 
        Dim OnWarningColumnFilter As New DTSEventColumnFilter()
        OnWarningColumnFilter.Computer = True
        OnWarningColumnFilter.SourceName = True
        OnWarningColumnFilter.MessageText = True
        packageLogging.SetColumnFilter("OnWarning", OnWarningColumnFilter)
 
        'Logging implementation ends
 
        'To add Connection Manager to the package
        'For source database (OLTP)
        Dim OLTP As ConnectionManager = package.Connections.Add("OLEDB")
        OLTP.ConnectionString = "Data Source=.;Initial Catalog=OLTP;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
        OLTP.Name = "LocalHost.OLTP"
        'For destination database (OLAP)
        Dim OLAP As ConnectionManager = package.Connections.Add("OLEDB")
        OLAP.ConnectionString = "Data Source=.;Initial Catalog=OLAP;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
        OLAP.Name = "LocalHost.OLAP"
 
        'To add package variables
        Dim TransactionID As Variable = package.Variables.Add("TransactionID", False, "User", 0)
        TransactionID.Name = "TransactionID"
 
        Dim RowCountVar As Variable = package.Variables.Add("RowCountVar", False, "User", 0)
        RowCountVar.Name = "RowCountVar"
 
        'To add Package Start Indicator (Execute Sql task )
        Dim PackageStartIndicatorTask As TaskHost = DirectCast(package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"), TaskHost)
        PackageStartIndicatorTask.Name = "Package Start Indicator"
        PackageStartIndicatorTask.Description = "Execute SQL Task Description"
        Dim PackageStartIndicatorTaskdoc As New XmlDocument()
        PackageStartIndicatorTaskdoc.LoadXml("<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.InitTransaction"" SQLTask:ResultType=""ResultSetType_SingleRow"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ResultBinding SQLTask:ResultName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" /></SQLTask:SqlTaskData>")
        DirectCast(PackageStartIndicatorTask.InnerObject, IDTSComponentPersist).LoadFromXML(PackageStartIndicatorTaskdoc.DocumentElement, Nothing)
 
        'To add Load Employee Dim to the package [Data Flow Task]
        Dim dataFlowTaskHost As TaskHost = DirectCast(package.Executables.Add("SSIS.Pipeline.2"), TaskHost)
        dataFlowTaskHost.Name = "Load Employee Dim"
        dataFlowTaskHost.FailPackageOnFailure = True
        dataFlowTaskHost.FailParentOnFailure = True
        dataFlowTaskHost.DelayValidation = False
        dataFlowTaskHost.Description = "Data Flow Task"
 
        '-----------Data Flow Inner component starts----------------
        Dim dataFlowTask As MainPipe = TryCast(dataFlowTaskHost.InnerObject, MainPipe)
        ' Source OLE DB connection manager to the package.
        Dim SconMgr As ConnectionManager = package.Connections("LocalHost.OLTP")
        ' Destination OLE DB connection manager to the package.
        Dim DconMgr As ConnectionManager = package.Connections("LocalHost.OLAP")
 
        ' Create and configure an OLE DB source component. 
        Dim source As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection.[New]()
        source.ComponentClassID = "DTSAdapter.OLEDBSource.2"
 
        ' Create the design-time instance of the source.
        Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate()
        ' The ProvideComponentProperties method creates a default output.
        srcDesignTime.ProvideComponentProperties()
        source.Name = "Employee Dim from OLTP"
 
        ' Assign the connection manager.
        source.RuntimeConnectionCollection(0).ConnectionManagerID = SconMgr.ID
        source.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.GetExtendedInterface(SconMgr)
        ' Set the custom properties of the source.
        srcDesignTime.SetComponentProperty("AccessMode", 0)
        ' Mode 0 : OpenRowset / Table - View
        srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Employee_Dim]")
        ' Connect to the data source, and then update the metadata for the source.
        srcDesignTime.AcquireConnections(Nothing)
        srcDesignTime.ReinitializeMetaData()
        srcDesignTime.ReleaseConnections()
 
 
 
        ' Add an Row Count to the data flow.
        Dim RowCountComponent As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection.[New]()
        RowCountComponent.Name = "Row Count"
        RowCountComponent.ComponentClassID = "DTSTransform.RowCount.2"
        Dim rowCountDesignTime As CManagedComponentWrapper = RowCountComponent.Instantiate()
        rowCountDesignTime.ProvideComponentProperties()
        rowCountDesignTime.SetComponentProperty("VariableName", "RowCountVar")
        rowCountDesignTime.AcquireConnections(Nothing)
        rowCountDesignTime.ReinitializeMetaData()
        rowCountDesignTime.ReleaseConnections()
 
        ' Create the path from source to Row Count Transformation.
        Dim pathSource_RowCount As IDTSPath100 = dataFlowTask.PathCollection.[New]()
        pathSource_RowCount.AttachPathAndPropagateNotifications(source.OutputCollection(0), RowCountComponent.InputCollection(0))
 
        ' Create and configure an OLE DB destination component.
        Dim destination As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection.[New]()
        destination.ComponentClassID = "DTSAdapter.OLEDBDestination.2"
        ' Create the design-time instance of the destination.
        Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate()
        ' The ProvideComponentProperties method creates a default input.
        destDesignTime.ProvideComponentProperties()
        destination.Name = "Employee Dim from OLAP"
        ' Assign the connection manager.
        destination.RuntimeConnectionCollection(0).ConnectionManagerID = DconMgr.ID
        destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.GetExtendedInterface(DconMgr)
        ' Set the custom properties.
        destDesignTime.SetComponentProperty("AccessMode", 3)
        ' Mode 3 : OpenRowset Using FastLoad / Table - View fast load
        destDesignTime.SetComponentProperty("OpenRowset", "[EmployeeDim]")
        destDesignTime.AcquireConnections(Nothing)
        destDesignTime.ReinitializeMetaData()
        destDesignTime.ReleaseConnections()
 
 
        ' Create the path from Rowcount to destination.
        Dim pathRowCount_Dest As IDTSPath100 = dataFlowTask.PathCollection.[New]()
        pathRowCount_Dest.AttachPathAndPropagateNotifications(RowCountComponent.OutputCollection(0), destination.InputCollection(0))
 
 
        ' Get the destination's default input and virtual input.
        Dim input As IDTSInput100 = destination.InputCollection(0)
        Dim vInput As IDTSVirtualInput100 = input.GetVirtualInput()
        ' Iterate through the virtual input column collection.
        For Each vColumn As IDTSVirtualInputColumn100 In vInput.VirtualInputColumnCollection
            ' Call the SetUsageType method of the destination
            ' to add each available virtual input column as an input column.
            destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
        Next
 
        'map external metadata to the inputcolumn
        'map external metadata to the inputcolumn
        For Each inputColumn As IDTSInputColumn100 In input.InputColumnCollection
            Dim exMetaColumn As IDTSExternalMetadataColumn100 = DirectCast(input.ExternalMetadataColumnCollection(inputColumn.Name), IDTSExternalMetadataColumn100)
            inputColumn.ExternalMetadataColumnID = exMetaColumn.ID
        Next
 
        '-----------Data Flow Inner component ends-------------------
 
        ' Precedence constraints from "Package Start Indicator" to "Load Employee Dim"
        Dim PackageStartIndicatorTask_dataFlowTaskHost As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Package Start Indicator"), package.Executables("Load Employee Dim"))
        PackageStartIndicatorTask_dataFlowTaskHost.Name = "Package Start Indicator" & "Load Employee Dim"
 
 
        'To add Package End Indicator (Execute Sql task )
        Dim PackageEndIndicatorTask As TaskHost = DirectCast(package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"), TaskHost)
        PackageEndIndicatorTask.Name = "Package End Indicator"
        PackageEndIndicatorTask.Description = "Execute SQL Task Description"
        Dim PackageEndIndicatorTaskdoc As New XmlDocument()
        PackageEndIndicatorTaskdoc.LoadXml("<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.EndTransaction ?, ?"" SQLTask:ResultType=""ResultSetType_None"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ParameterBinding SQLTask:ParameterName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /><SQLTask:ParameterBinding SQLTask:ParameterName=""1"" SQLTask:DtsVariableName=""User::RowCountVar"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /></SQLTask:SqlTaskData>")
        DirectCast(PackageEndIndicatorTask.InnerObject, IDTSComponentPersist).LoadFromXML(PackageEndIndicatorTaskdoc.DocumentElement, Nothing)
 
        ' Precedence constraints from "Load Employee Dim" to "Package End Indicator"
        Dim dataFlowTaskHost_PackageEndIndicatorTask As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Load Employee Dim"), package.Executables("Package End Indicator"))
        dataFlowTaskHost_PackageEndIndicatorTask.Name = "Load Employee Dim" & "Package End Indicator"
 
        'Saving the package
        Dim app As New Microsoft.SqlServer.Dts.Runtime.Application()
        app.SaveToXml("I:Site workSamplePackage 2008SamplePackageSamplePackage.dtsx", package, Nothing)
    End Sub
End Module

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

Reference:
MSDN Books Online

Categories: SSIS, SSIS programming

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

http://tinyurl.com/cmpgsarah31593

January 26th, 2013 at 13:43 | #1

Reply | Quote

“Programming SSIS Logging | SQL Lion” seriously got myself
simply addicted with ur website! I reallywill probably be back
again far more frequently. Thanks ,Heriberto
Stephen

February 1st, 2013 at 12:48 | #2

Reply | Quote

In looking for the proper collar for the pets, things that
you need to consider are safety, the training process as well as
your convenience. Be patient with your dog.
Dog obedience training is a bit more than trained responses.

No trackbacks yet.

Delete Duplicate Records GROUP BY Clause (SQL Server 2005 Vs 2008)

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 SSIS Logging

All Posts

Recent Posts

Categories

Archives

Recent Comments