Programming Foreach Loop Container – Enumerating Excel Sheets

Home > SSIS, SSIS programming > Programming Foreach Loop Container – Enumerating Excel Sheets

Programming Foreach Loop Container – Enumerating Excel Sheets

June 28th, 2009 Arun Mallick Leave a comment Go to comments

In my previous article named “Programming Foreach Loop Container – Enumerating Excel Files”, we discussed about enumerating Excel files in a folder and load data from each excel file to the destination by using Foreach File enumerator.

But there may be cases where we may have data in the form of sheets in a single excel file. For example, the total sales of study materials over different countries.

To load all the sheets from a single excel file in Foreach Loop Container, we have to use “Foreach ADO.NET Schema Rowset Enumerator”. This special enumerator is used to enumerate through schema information about a data source i.e. the schema information supported by the OLEDB provider. For example, to get list of tables, schemas, column information, constraints, etc from a database.

Excel Sheets example

Below you will see step by step operations to configure the package for enumerating sheets from a excel file.

Step 1: Create a package with the basic components and tasks. Refer my earlier article for details. Programming Foreach Loop Container – Enumerating Excel Files

Step 2: Create a variable named “CurrentExcelSheet” of String type with default value as “India$” (one of the sheet name) to avoid any validation error.

Step 3: Create Connection manager for source (Excel Connection manager) and for the destination (OLEDB connection manager)

Connection Managers

Step 4: Create ADO .Net connection manager to access the excel file in the Foreach ADO.NET Schema Rowset Enumerator.

Right click on the connection manager area and select “New ADO .NET Connection”

ADO .net connection

In the connection manager dialog box, select “Microsoft Jet4.0 OLE DB Provider” so that we can access Excel file by this OLE DB driver.

Microsoft Jet4.0 OLE DB Provider

Then in the “Database file name” section select the Excel file that contains information about all the sales. (If necessary select “All Files *.*” in the file open dialog box.)

Database file name

Leave the User name and password fields as it is. Click on “All” pane at left side and set the value for “Extended Properties” to “Excel 8.0”.

Extended Properties

Then test the connection and press OK.

Step 5: Create a “Foreach Loop Container” and set the “Enumerator” in Collection pane to “Foreach ADO.NET Schema Rowset Enumerator”. Here two more properties you have to set. They are:

a) Connection: Select an ADO.NET connection manager in the list. Here in our case, the connection manager will be “Ado .net for Excel” that we created above.

b) Schema: Select the schema to enumerate. For our case we set “Tables” as we need to enumerate all the sheets of the given excel file.

Keep the “Set Restrictions” settings as it is. For Each Loop Editor

Step 6: Then we have to set the Variable Mappings pane. Here we will set the “User::CurrentExcelSheet” variable to index 2. Here index 2 is specifically used because the table information of the excel sheet is at the 3rd column of the resultset generated by the “Foreach ADO.NET Schema Rowset Enumerator” and the columns of the resultset have 0 based indexing.

Step 7: Create the data flow task name “Sales” where we have one source named “Excel Source (Sales)”, but the table information for the source will be fetched from the variable “User::CurrentExcelSheet” so that at each iteration, we will get the new sheet information from the excel file.

Data Flow Task

For this we have to set the “Data access mode:” to “Table name or view name variable” and the variable name to “User::CurrentExcelSheet”.

Excel Source Editor

Step 8: Create the destination component and all other components as per the requirement and run the package. For more information about the other components, refer my article named “SSIS Programming Basic”.

Control Flow

So we successfully executed the package for extracting data from different sheets of a excel file.

Let’s have a look on the procedures to create the above logic by using Integration Service Programming in SSIS. The code will go in the same way as my previous article “Programming Foreach Loop Container – Enumerating Excel Files” except some modifications in the areas of Enumerator where we are using “Foreach ADO.NET Schema Rowset Enumerator“

In the Excel source area the data is extracted from a variable instead of Open Rowset. The code is self descriptive and easy to understand. The code is given in both C# .net and VB .net language for both SQL Server 2005 and SQL Server 2008.

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 ForEach Loop Container (Enumerating through Excel Sheets) for SQL Server 2005
 
namespace CreatePackageSample
{
    class Program
    {
        static void Main(string[] args)
        {
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
 
            //To Create a package named [Sample Package]
            Package package = new Package();
            package.Name = "Sample Package";
            package.PackageType = DTSPackageType.DTSDesigner90;
            package.VersionBuild = 1;
 
            //To add package variables
            Variable RowCountVar = package.Variables.Add("RowCountVar", false, "User", 0);
            RowCountVar.Name = @"RowCountVar";
 
            Variable TransactionID = package.Variables.Add("TransactionID", false, "User", 0);
            TransactionID.Name = @"TransactionID";
 
            Variable CurrentExcelSheet = package.Variables.Add("CurrentExcelSheet", false, "User", @"India$");
            CurrentExcelSheet.Name = @"CurrentExcelSheet";
            //DataType will be automatically set to String
 
            //For source database (Excel)
            ConnectionManager ExcelSource = package.Connections.Add("EXCEL");
            ExcelSource.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\Site work\SampleExcelFiles\Sales.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";";
            ExcelSource.Name = "Excel Connection Manager";
 
            //For Ado .net connection manager to access Excel sheets
            ConnectionManager AdoNetExcel = package.Connections.Add("ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089");
            AdoNetExcel.ConnectionString = @"Data Source=I:\Site work\SampleExcelFiles\Sales.xls;Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;";
            AdoNetExcel.Name = @"Ado .net for Excel";
 
            //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 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 "Excel Sheet Iterator" (ForEach Loop container)
            ForEachLoop ForEachLoopContainer = (ForEachLoop)package.Executables.Add("STOCK:FOREACHLOOP");
            ForEachLoopContainer.Name = @"Excel Sheet Iterator";
            ForEachLoopContainer.Description = @"Foreach Loop Container";
 
 
            //To Set Foreach loop container collection information
            ForEachEnumeratorInfo SheetEnumeratorInfo = app.ForEachEnumeratorInfos["Foreach ADO.NET Schema Rowset Enumerator"];
            ForEachEnumeratorHost SheetEnumeratorHost = SheetEnumeratorInfo.CreateNew();
            SheetEnumeratorHost.CollectionEnumerator = true;
            SheetEnumeratorHost.Properties["Connection"].SetValue(SheetEnumeratorHost, @"Ado .net for Excel");
            //GUID for Tables is  "c8b52229-5cf3-11ce-ade5-00aa0044773d"
            SheetEnumeratorHost.Properties["SchemaRowset"].SetValue(SheetEnumeratorHost, new Guid("c8b52229-5cf3-11ce-ade5-00aa0044773d"));
 
            ForEachLoopContainer.ForEachEnumerator = SheetEnumeratorHost;
 
            //To Set Foreach loop container variable mappings
            ForEachVariableMapping CurrenSheetVariableMapping = ForEachLoopContainer.VariableMappings.Add();
            CurrenSheetVariableMapping.VariableName = @"User::CurrentExcelSheet";
            CurrenSheetVariableMapping.ValueIndex = 2;
 
            //To add Sales inside Foreach loop container (Data Flow Task)
            TaskHost dataFlowTaskHost = (TaskHost)ForEachLoopContainer.Executables.Add("DTS.Pipeline.1");
            dataFlowTaskHost.Name = @"Sales";
            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["Excel Connection Manager"];
            // Destination OLE DB connection manager to the package.
            ConnectionManager DconMgr = package.Connections["LocalHost.OLAP"];
 
            // Create and configure an Excel source component.  
            IDTSComponentMetaData90 source = dataFlowTask.ComponentMetaDataCollection.New();
            source.ComponentClassID = "DTSAdapter.ExcelSource.1";
 
            // Create the design-time instance of the source.
            CManagedComponentWrapper srcDesignTime = source.Instantiate();
            // The ProvideComponentProperties method creates a default output.
            srcDesignTime.ProvideComponentProperties();
            source.Name = "Excel Source (Sales)";
 
            // 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", 1); // Mode 1 : OpenRowset From Variable
            srcDesignTime.SetComponentProperty("OpenRowsetVariable", "User::CurrentExcelSheet");
            // 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 = "Sales 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", "[dbo].[Sales]");
            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 "Excel Sheet Iterator"
            PrecedenceConstraint PackageStartIndicatorTask_ForEachLoop = package.PrecedenceConstraints.Add(package.Executables[@"Package Start Indicator"], package.Executables[@"Excel Sheet Iterator"]);
            PackageStartIndicatorTask_ForEachLoop.Name = @"Package Start Indicator" + @"Excel Sheet Iterator";
 
 
            //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 "Excel Sheet Iterator" to "Package End Indicator"
            PrecedenceConstraint ForEachLoop_PackageEndIndicatorTask = package.PrecedenceConstraints.Add(package.Executables[@"Excel Sheet Iterator"], package.Executables[@"Package End Indicator"]);
            ForEachLoop_PackageEndIndicatorTask.Name = @"Excel Sheet Iterator" + @"Package End Indicator";
 
 
            //Saving the package
            app.SaveToXml(@"I:\Site work\SamplePackage\SamplePackage\SamplePackage.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 ForEach Loop Container (Enumerating through Excel Sheets) for SQL Server 2005
Module Module1
 
    Sub Main(ByVal args As String())
        Dim app As New Microsoft.SqlServer.Dts.Runtime.Application()
 
        '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 package variables
        Dim RowCountVar As Variable = package.Variables.Add("RowCountVar", False, "User", 0)
        RowCountVar.Name = "RowCountVar"
 
        Dim TransactionID As Variable = package.Variables.Add("TransactionID", False, "User", 0)
        TransactionID.Name = "TransactionID"
 
        Dim CurrentExcelSheet As Variable = package.Variables.Add("CurrentExcelSheet", False, "User", "India$")
        CurrentExcelSheet.Name = "CurrentExcelSheet"
        'DataType will be automatically set to String
 
        'For source database (Excel)
        Dim ExcelSource As ConnectionManager = package.Connections.Add("EXCEL")
        ExcelSource.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\Site work\SampleExcelFiles\Sales.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";"
        ExcelSource.Name = "Excel Connection Manager"
 
        'For Ado .net connection manager to access Excel sheets
        Dim AdoNetExcel As ConnectionManager = package.Connections.Add("ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
        AdoNetExcel.ConnectionString = "Data Source=I:\Site work\SampleExcelFiles\Sales.xls;Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;"
        AdoNetExcel.Name = "Ado .net for Excel"
 
        '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 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 "Excel Sheet Iterator" (ForEach Loop container)
        Dim ForEachLoopContainer As ForEachLoop = DirectCast(package.Executables.Add("STOCK:FOREACHLOOP"), ForEachLoop)
        ForEachLoopContainer.Name = "Excel Sheet Iterator"
        ForEachLoopContainer.Description = "Foreach Loop Container"
 
 
        'To Set Foreach loop container collection information
        Dim SheetEnumeratorInfo As ForEachEnumeratorInfo = app.ForEachEnumeratorInfos("Foreach ADO.NET Schema Rowset Enumerator")
        Dim SheetEnumeratorHost As ForEachEnumeratorHost = SheetEnumeratorInfo.CreateNew()
        SheetEnumeratorHost.CollectionEnumerator = True
        SheetEnumeratorHost.Properties("Connection").SetValue(SheetEnumeratorHost, "Ado .net for Excel")
        'GUID for Tables is "c8b52229-5cf3-11ce-ade5-00aa0044773d"
        SheetEnumeratorHost.Properties("SchemaRowset").SetValue(SheetEnumeratorHost, New Guid("c8b52229-5cf3-11ce-ade5-00aa0044773d"))
 
        ForEachLoopContainer.ForEachEnumerator = SheetEnumeratorHost
 
        'To Set Foreach loop container variable mappings
        Dim CurrenSheetVariableMapping As ForEachVariableMapping = ForEachLoopContainer.VariableMappings.Add()
        CurrenSheetVariableMapping.VariableName = "User::CurrentExcelSheet"
        CurrenSheetVariableMapping.ValueIndex = 2
 
        'To add Sales inside Foreach loop container (Data Flow Task)
        Dim dataFlowTaskHost As TaskHost = DirectCast(ForEachLoopContainer.Executables.Add("DTS.Pipeline.1"), TaskHost)
        dataFlowTaskHost.Name = "Sales"
        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("Excel Connection Manager")
        ' Destination OLE DB connection manager to the package.
        Dim DconMgr As ConnectionManager = package.Connections("LocalHost.OLAP")
 
        ' Create and configure an Excel source component. 
        Dim source As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.[New]()
        source.ComponentClassID = "DTSAdapter.ExcelSource.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 = "Excel Source (Sales)"
 
        ' 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", 1)
        ' Mode 1 : OpenRowset From Variable
        srcDesignTime.SetComponentProperty("OpenRowsetVariable", "User::CurrentExcelSheet")
        ' 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 = "Sales 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", "[dbo].[Sales]")
        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 "Excel Sheet Iterator"
        Dim PackageStartIndicatorTask_ForEachLoop As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Package Start Indicator"), package.Executables("Excel Sheet Iterator"))
        PackageStartIndicatorTask_ForEachLoop.Name = "Package Start Indicator" & "Excel Sheet Iterator"
 
 
        '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 "Excel Sheet Iterator" to "Package End Indicator"
        Dim ForEachLoop_PackageEndIndicatorTask As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Excel Sheet Iterator"), package.Executables("Package End Indicator"))
        ForEachLoop_PackageEndIndicatorTask.Name = "Excel Sheet Iterator" & "Package End Indicator"
 
 
        'Saving the package
        app.SaveToXml("I:\Site work\SamplePackage\SamplePackage\SamplePackage.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 ForEach Loop Container (Enumerating through Excel Sheets) for SQL Server 2008
 
namespace SamplePackageCSharp2008
{
    class Program
    {
        static void Main(string[] args)
        {
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
 
            //To Create a package named [Sample Package]
            Package package = new Package();
            package.Name = "Sample Package";
            package.PackageType = DTSPackageType.DTSDesigner100;
            package.VersionBuild = 1;
 
            //To add package variables
            Variable RowCountVar = package.Variables.Add("RowCountVar", false, "User", 0);
            RowCountVar.Name = @"RowCountVar";
 
            Variable TransactionID = package.Variables.Add("TransactionID", false, "User", 0);
            TransactionID.Name = @"TransactionID";
 
            Variable CurrentExcelSheet = package.Variables.Add("CurrentExcelSheet", false, "User", @"India$");
            CurrentExcelSheet.Name = @"CurrentExcelSheet";
            //DataType will be automatically set to String
 
            //For source database (Excel)
            ConnectionManager ExcelSource = package.Connections.Add("EXCEL");
            ExcelSource.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\Site work\SampleExcelFiles\Sales.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";";
            ExcelSource.Name = "Excel Connection Manager";
 
            //For Ado .net connection manager to access Excel sheets
            ConnectionManager AdoNetExcel = package.Connections.Add("ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089");
            AdoNetExcel.ConnectionString = @"Data Source=I:\Site work\SampleExcelFiles\Sales.xls;Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;";
            AdoNetExcel.Name = @"Ado .net for Excel";
 
            //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 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 "Excel Sheet Iterator" (ForEach Loop container)
            ForEachLoop ForEachLoopContainer = (ForEachLoop)package.Executables.Add("STOCK:FOREACHLOOP");
            ForEachLoopContainer.Name = @"Excel Sheet Iterator";
            ForEachLoopContainer.Description = @"Foreach Loop Container";
 
 
            //To Set Foreach loop container collection information
            ForEachEnumeratorInfo SheetEnumeratorInfo = app.ForEachEnumeratorInfos["Foreach ADO.NET Schema Rowset Enumerator"];
            ForEachEnumeratorHost SheetEnumeratorHost = SheetEnumeratorInfo.CreateNew();
            SheetEnumeratorHost.CollectionEnumerator = true;
            SheetEnumeratorHost.Properties["Connection"].SetValue(SheetEnumeratorHost, @"Ado .net for Excel");
            //GUID for Tables is  "c8b52229-5cf3-11ce-ade5-00aa0044773d"
            SheetEnumeratorHost.Properties["SchemaRowset"].SetValue(SheetEnumeratorHost, new Guid("c8b52229-5cf3-11ce-ade5-00aa0044773d"));
 
            ForEachLoopContainer.ForEachEnumerator = SheetEnumeratorHost;
 
            //To Set Foreach loop container variable mappings
            ForEachVariableMapping CurrenSheetVariableMapping = ForEachLoopContainer.VariableMappings.Add();
            CurrenSheetVariableMapping.VariableName = @"User::CurrentExcelSheet";
            CurrenSheetVariableMapping.ValueIndex = 2;
 
            //To add Sales inside Foreach loop container (Data Flow Task)
            TaskHost dataFlowTaskHost = (TaskHost)ForEachLoopContainer.Executables.Add("SSIS.Pipeline.2");
            dataFlowTaskHost.Name = @"Sales";
            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["Excel Connection Manager"];
            // Destination OLE DB connection manager to the package.
            ConnectionManager DconMgr = package.Connections["LocalHost.OLAP"];
 
            // Create and configure an Excel source component.  
            IDTSComponentMetaData100 source = dataFlowTask.ComponentMetaDataCollection.New();
            source.ComponentClassID = "DTSAdapter.ExcelSource.2";
 
            // Create the design-time instance of the source.
            CManagedComponentWrapper srcDesignTime = source.Instantiate();
            // The ProvideComponentProperties method creates a default output.
            srcDesignTime.ProvideComponentProperties();
            source.Name = "Excel Source (Sales)";
 
            // 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", 1); // Mode 1 : OpenRowset From Variable
            srcDesignTime.SetComponentProperty("OpenRowsetVariable", "User::CurrentExcelSheet");
            // 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"; //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.
            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 = "Sales 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", "[dbo].[Sales]");
            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
            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 "Excel Sheet Iterator"
            PrecedenceConstraint PackageStartIndicatorTask_ForEachLoop = package.PrecedenceConstraints.Add(package.Executables[@"Package Start Indicator"], package.Executables[@"Excel Sheet Iterator"]);
            PackageStartIndicatorTask_ForEachLoop.Name = @"Package Start Indicator" + @"Excel Sheet Iterator";
 
 
            //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 "Excel Sheet Iterator" to "Package End Indicator"
            PrecedenceConstraint ForEachLoop_PackageEndIndicatorTask = package.PrecedenceConstraints.Add(package.Executables[@"Excel Sheet Iterator"], package.Executables[@"Package End Indicator"]);
            ForEachLoop_PackageEndIndicatorTask.Name = @"Excel Sheet Iterator" + @"Package End Indicator";
 
 
            //Saving the package
            app.SaveToXml(@"I:\Site work\SamplePackage 2008\SamplePackage\SamplePackage.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 ForEach Loop Container (Enumerating through Excel Sheets) for SQL Server 2008
Module Module1
 
    Sub Main()
 
        Dim app As New Microsoft.SqlServer.Dts.Runtime.Application()
 
        '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 package variables
        Dim RowCountVar As Variable = package.Variables.Add("RowCountVar", False, "User", 0)
        RowCountVar.Name = "RowCountVar"
 
        Dim TransactionID As Variable = package.Variables.Add("TransactionID", False, "User", 0)
        TransactionID.Name = "TransactionID"
 
        Dim CurrentExcelSheet As Variable = package.Variables.Add("CurrentExcelSheet", False, "User", "India$")
        CurrentExcelSheet.Name = "CurrentExcelSheet"
        'DataType will be automatically set to String
 
        'For source database (Excel)
        Dim ExcelSource As ConnectionManager = package.Connections.Add("EXCEL")
        ExcelSource.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\Site work\SampleExcelFiles\Sales.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";"
        ExcelSource.Name = "Excel Connection Manager"
 
        'For Ado .net connection manager to access Excel sheets
        Dim AdoNetExcel As ConnectionManager = package.Connections.Add("ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
        AdoNetExcel.ConnectionString = "Data Source=I:\Site work\SampleExcelFiles\Sales.xls;Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;"
        AdoNetExcel.Name = "Ado .net for Excel"
 
        '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 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 "Excel Sheet Iterator" (ForEach Loop container)
        Dim ForEachLoopContainer As ForEachLoop = DirectCast(package.Executables.Add("STOCK:FOREACHLOOP"), ForEachLoop)
        ForEachLoopContainer.Name = "Excel Sheet Iterator"
        ForEachLoopContainer.Description = "Foreach Loop Container"
 
 
        'To Set Foreach loop container collection information
        Dim SheetEnumeratorInfo As ForEachEnumeratorInfo = app.ForEachEnumeratorInfos("Foreach ADO.NET Schema Rowset Enumerator")
        Dim SheetEnumeratorHost As ForEachEnumeratorHost = SheetEnumeratorInfo.CreateNew()
        SheetEnumeratorHost.CollectionEnumerator = True
        SheetEnumeratorHost.Properties("Connection").SetValue(SheetEnumeratorHost, "Ado .net for Excel")
        'GUID for Tables is "c8b52229-5cf3-11ce-ade5-00aa0044773d"
        SheetEnumeratorHost.Properties("SchemaRowset").SetValue(SheetEnumeratorHost, New Guid("c8b52229-5cf3-11ce-ade5-00aa0044773d"))
 
        ForEachLoopContainer.ForEachEnumerator = SheetEnumeratorHost
 
        'To Set Foreach loop container variable mappings
        Dim CurrenSheetVariableMapping As ForEachVariableMapping = ForEachLoopContainer.VariableMappings.Add()
        CurrenSheetVariableMapping.VariableName = "User::CurrentExcelSheet"
        CurrenSheetVariableMapping.ValueIndex = 2
 
        'To add Sales inside Foreach loop container (Data Flow Task)
        Dim dataFlowTaskHost As TaskHost = DirectCast(ForEachLoopContainer.Executables.Add("SSIS.Pipeline.2"), TaskHost)
        dataFlowTaskHost.Name = "Sales"
        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("Excel Connection Manager")
        ' Destination OLE DB connection manager to the package.
        Dim DconMgr As ConnectionManager = package.Connections("LocalHost.OLAP")
 
        ' Create and configure an Excel source component. 
        Dim source As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection.[New]()
        source.ComponentClassID = "DTSAdapter.ExcelSource.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 = "Excel Source (Sales)"
 
        ' 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", 1)
        ' Mode 1 : OpenRowset From Variable
        srcDesignTime.SetComponentProperty("OpenRowsetVariable", "User::CurrentExcelSheet")
        ' 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"
        '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 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 = "Sales 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", "[dbo].[Sales]")
        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
        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 "Excel Sheet Iterator"
        Dim PackageStartIndicatorTask_ForEachLoop As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Package Start Indicator"), package.Executables("Excel Sheet Iterator"))
        PackageStartIndicatorTask_ForEachLoop.Name = "Package Start Indicator" & "Excel Sheet Iterator"
 
 
        '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 "Excel Sheet Iterator" to "Package End Indicator"
        Dim ForEachLoop_PackageEndIndicatorTask As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Excel Sheet Iterator"), package.Executables("Package End Indicator"))
        ForEachLoop_PackageEndIndicatorTask.Name = "Excel Sheet Iterator" & "Package End Indicator"
 
 
        'Saving the package
        app.SaveToXml("I:\Site work\SamplePackage 2008\SamplePackage\SamplePackage.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.

Categories: SSIS, SSIS programming

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

Keith

August 6th, 2009 at 19:38 | #1

Reply | Quote

Do you have more information on your tool “SSIS Component Explorer”?

http://www.perpetualdynamics.com
Arun Mallick

August 7th, 2009 at 01:01 | #2

Reply | Quote

Please visit the below link, download the software “SSIS Component Explorer” and explore it….

I hope you will get enough information.
Thanks
ziegheart

April 6th, 2010 at 18:22 | #3

Reply | Quote

thank you for exam
but i need add the country name from excel sheet(on you exam = india,australia,cananda,japan)continue the last column into the destination
help me please

ps. i’m not strong in english ,sorry
Reddeppa

July 20th, 2010 at 11:34 | #4

Reply | Quote

Excellent Explaination
Thulasi.Ram

June 24th, 2011 at 12:20 | #5

Reply | Quote

Hi,I was try to this navigation,But i was getting error,colud plz explain me clearly
Art DeGaetano II

February 1st, 2012 at 03:15 | #6

Reply | Quote

Help, i am trying to use this and when I try to rename the file to an archive location, i am getting the infamous

The process cannot access the file because it is being used by another process.

How do I close the connection after the excel file has been loaded so I can continue to the next file and then rename to an archive location? I know its probably simple and I am missing something… please help!
Jey

April 25th, 2012 at 18:44 | #7

Reply | Quote

Hi Arun,

Thanks for the demo, I’ve cpl of questios.

1)Step2 says one of the sheet name, but I guess the variable User::CurrentExcelSheet saves the work book path ratherthan sheet name, let me know if I were wrong.

I’m getting error “Error opening rowset for “File path”"

2)To create an excel connection manager , have to give a default file path, even though a variable used, are there any other way to create a excel manager without default file name/path?

Thanks,
2)
JimFive

August 22nd, 2012 at 23:15 | #8

Reply | Quote

I have run into two problems with this. I am looping through a directory of Excel files and looping through the sheets in file.

First, I had to set the RetainSameConnection property to False for the Excel Connection Manager. Without this, I was getting an error that the sheet name was not found in the collection. It appears that it was just keeping the original file open instead of moving to the next file.

Second, It stops with an error after 64 files. I didn’t have time to find a solution so I just moved files around to process them all.

Thanks
JimFive
umesh lade

January 8th, 2013 at 11:35 | #9

Reply | Quote

during Excel source editor it give error Opening a rowset for [sales opt$] faild .check that object exist in database

No trackbacks yet.

TRANSACTION Isolation Levels in SQL Server Programming Foreach Loop Container – Enumerating Excel Files

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 Foreach Loop Container – Enumerating Excel Sheets

All Posts

Recent Posts

Categories

Archives

Recent Comments