Programming Foreach Loop Container – Enumerating Excel Files

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

Programming Foreach Loop Container – Enumerating Excel Files

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

Sometimes we were not aware of the contents of any object and we have to do certain tasks as many times as the no. of contents of that object. For example, a folder contains some text files with employee information data and we have to load all the text files data to our SQL server employee table. Here, we don’t know about the count of the source but we have to roll over every file. For this type of situation, SSIS have a unique container called “Foreach Loop Container” that will enumerate with each text file in the folder.

It can be used in some other cases too like enumerating each sheet of a excel file, or parsing each row of a table, enumerating each child node of a given tree, etc. In SQL server 2000 DTS package, it is bit difficult to loop over any task but fortunately SSIS has a special container to accomplish such tasks.

Benefits from SSIS Foreach Loop Container:

It keeps the package neat and clean (avoids building of repeated tasks)
It can iterate any object dynamically (even if the contents count is not available)

The foreach loop container acts as a repeating control flow in a package. Its operations are similar to work of Foreach keyword in any advanced programming language. We have a definite type of enumerator for each type of objects. Foreach loop container uses one of this enumerator to carry out its operation:

Below are the lists of various types of enumerators provided by SSIS Foreach Loop Container:

Foreach File Enumerator: It enumerates files in a folder. The plus point here is it can traverse through subfolders also.
Foreach Item Enumerator: It enumerates items in a collection. Like enumerating rows and columns in an Excel sheet.
Foreach ADO Enumerator: Useful for enumerating rows in tables.
Foreach ADO.NET Schema Rowset Enumerator: To enumerate through schema information about a data source. For example, to get list of tables in a database.
Foreach From Variable Enumerator: Used to enumerate through the object contained in a variable. (if the object is enumerable)
Foreach NodeList Enumerator: Used to enumerate the result set of an XML Path Language (XPath) expression.
Foreach SMO Enumerator: It enumerates through SQL Server Management Objects (SMO) objects.

Let’s consider the below example;

Here we have a set of Excel files in a folder containing different data but of same structure. Our requirement is to load all the data from these excel files to our destination table in the SQL Server database.

For this example, the excel files contain information about the sales of Pencil across different countries. We have a total of 5 excel files in the folder named “SampleExcelFiles“. To achieve this, we have to take the help of FOR EACH Loop Container provided by SQL Server Integration Service (SSIS).

[Note: All other components and tasks are described in the earlier article named "SSIS Programming Basic"]

After creation of necessary components, the “Foreach Loop Container” is created where only one Data Flow Task has been kept. The single Data Flow Task will load all the excel files data to the destination after changing the source connection string each time the loop iterates.

For this, one more package level variables have to be created named:

CurrentExcelPath: This will keep track of the path of current excel file after each iteration.

Create one Excel connection manager to handle all these excel files. Take one existing excel file to avoid validation errors. This workbook is just for validation purpose. At runtime i.e. while package will execute the Connection string will automatically be updated to the value of CurrentExcelPath. Or else you can keep it empty provided DelayValidation property of this connection manager is set to True. In this example the connection manager name is “Excel Connection Manager“.

In the connection manager property, select “Expressions” and then set the first property as “ConnectionString“.

Property Expression Editor

Click on the ellipse button, and in the “Expression Builder” dialog box, Enter the following code:

“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + @[User::CurrentExcelPath] + “;Extended Properties=\”Excel 8.0;HDR=Yes\”"

Expression Builder

Note: Here in this expression, the [User::CurrentExcelPath] has been used to complete the expression at runtime.

Then drag the “Foreach Loop Container” to the package area and double click to open the Foreach Loop Editor. On the Collection page, select For Foreach File Enumerator. In Enumerator Configuration, select the Folder where all the excel files are kept or stored. State the File Filter to be considered. For this example, the folder path is “I:\Site work\SampleExcelFiles” and the File filter is “*.xls”.

Foreach Loop Editor

Then in the Variable Mappings page, map the Index 0 to the user defined string variable that is defined above for storing the current Excel file path i.e. CurrentExcelPath. Click OK to close the Foreach Loop Editor.

Now create the dataflow task that will use the “Excel Connection Manager” as its connection manager to handle Excel files. Add the Data Flow components like Source, row counter and destination and then execute the package.

Execute Package

Output:

Output

Let’s do the same in SQL Server Integration Programming.

The creation name for Foreach Loop Container is {STOCK:FOREACHLOOP} and for Excel Source is {DTSAdapter.ExcelSource.1}.

Here we will see how to add Excel as source and how to change its connection string dynamically by integration programming.

The code below shows the above package programmatically and the code itself is self-descriptive.

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 Files) 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 CurrentExcelPath = package.Variables.Add("CurrentExcelPath", false, "User", @"I:\Site work\SampleExcelFiles\India.xls");
            CurrentExcelPath.Name = @"CurrentExcelPath";
            //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\India.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";";
            ExcelSource.Name = "Excel Connection Manager";
            ExcelSource.SetExpression("ConnectionString",@"""Provider=Microsoft.Jet.OLEDB.4.0;Data Source="" + @[User::CurrentExcelPath]   + "";Extended Properties=\""Excel 8.0;HDR=Yes\""""");
 
            //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 FileEnumeratorInfo = app.ForEachEnumeratorInfos["Foreach File Enumerator"];
            ForEachEnumeratorHost FileEnumeratorHost = FileEnumeratorInfo.CreateNew();
            FileEnumeratorHost.CollectionEnumerator = false;
            FileEnumeratorHost.Properties["Directory"].SetValue(FileEnumeratorHost,@"I:\Site work\SampleExcelFiles");
            FileEnumeratorHost.Properties["FileSpec"].SetValue(FileEnumeratorHost, @"*.xls");
            //0 = "Fully qualified" ; 1 = "Name and extension" ; 2 = "Name only"
            FileEnumeratorHost.Properties["FileNameRetrieval"].SetValue(FileEnumeratorHost, 0);
            FileEnumeratorHost.Properties["Recurse"].SetValue(FileEnumeratorHost, "False");
            ForEachLoopContainer.ForEachEnumerator = FileEnumeratorHost;
 
            //To Set Foreach loop container variable mappings
            ForEachVariableMapping CurrenFileVariableMapping = ForEachLoopContainer.VariableMappings.Add();
            CurrenFileVariableMapping.VariableName = @"User::CurrentExcelPath";
            CurrenFileVariableMapping.ValueIndex = 0;
 
            //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", 0); // Mode 0 : OpenRowset / Table - View
            srcDesignTime.SetComponentProperty("OpenRowset", "Sheet1$");
            // 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 Files) 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 CurrentExcelPath As Variable = package.Variables.Add("CurrentExcelPath", False, "User", "I:\Site work\SampleExcelFiles\India.xls")
        CurrentExcelPath.Name = "CurrentExcelPath"
        '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\India.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";"
        ExcelSource.Name = "Excel Connection Manager"
        ExcelSource.SetExpression("ConnectionString", """Provider=Microsoft.Jet.OLEDB.4.0;Data Source="" + @[User::CurrentExcelPath] + "";Extended Properties=\""Excel 8.0;HDR=Yes\""""")
 
        '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 FileEnumeratorInfo As ForEachEnumeratorInfo = app.ForEachEnumeratorInfos("Foreach File Enumerator")
        Dim FileEnumeratorHost As ForEachEnumeratorHost = FileEnumeratorInfo.CreateNew()
        FileEnumeratorHost.CollectionEnumerator = False
        FileEnumeratorHost.Properties("Directory").SetValue(FileEnumeratorHost, "I:\Site work\SampleExcelFiles")
        FileEnumeratorHost.Properties("FileSpec").SetValue(FileEnumeratorHost, "*.xls")
        '0 = "Fully qualified" ; 1 = "Name and extension" ; 2 = "Name only"
        FileEnumeratorHost.Properties("FileNameRetrieval").SetValue(FileEnumeratorHost, 0)
        FileEnumeratorHost.Properties("Recurse").SetValue(FileEnumeratorHost, "False")
        ForEachLoopContainer.ForEachEnumerator = FileEnumeratorHost
 
        'To Set Foreach loop container variable mappings
        Dim CurrenFileVariableMapping As ForEachVariableMapping = ForEachLoopContainer.VariableMappings.Add()
        CurrenFileVariableMapping.VariableName = "User::CurrentExcelPath"
        CurrenFileVariableMapping.ValueIndex = 0
 
        '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", 0)
        ' Mode 0 : OpenRowset / Table - View
        srcDesignTime.SetComponentProperty("OpenRowset", "Sheet1$")
        ' 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 Files) 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 CurrentExcelPath = package.Variables.Add("CurrentExcelPath", false, "User", @"I:\Site work\SampleExcelFiles\India.xls");
            CurrentExcelPath.Name = @"CurrentExcelPath";
            //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\India.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";";
            ExcelSource.Name = "Excel Connection Manager";
            ExcelSource.SetExpression("ConnectionString", @"""Provider=Microsoft.Jet.OLEDB.4.0;Data Source="" + @[User::CurrentExcelPath]   + "";Extended Properties=\""Excel 8.0;HDR=Yes\""""");
 
            //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 FileEnumeratorInfo = app.ForEachEnumeratorInfos["Foreach File Enumerator"];
            ForEachEnumeratorHost FileEnumeratorHost = FileEnumeratorInfo.CreateNew();
            FileEnumeratorHost.CollectionEnumerator = false;
            FileEnumeratorHost.Properties["Directory"].SetValue(FileEnumeratorHost, @"I:\Site work\SampleExcelFiles");
            FileEnumeratorHost.Properties["FileSpec"].SetValue(FileEnumeratorHost, @"*.xls");
            //0 = "Fully qualified" ; 1 = "Name and extension" ; 2 = "Name only"
            FileEnumeratorHost.Properties["FileNameRetrieval"].SetValue(FileEnumeratorHost, 0);
            FileEnumeratorHost.Properties["Recurse"].SetValue(FileEnumeratorHost, "False");
            ForEachLoopContainer.ForEachEnumerator = FileEnumeratorHost;
 
            //To Set Foreach loop container variable mappings
            ForEachVariableMapping CurrenFileVariableMapping = ForEachLoopContainer.VariableMappings.Add();
            CurrenFileVariableMapping.VariableName = @"User::CurrentExcelPath";
            CurrenFileVariableMapping.ValueIndex = 0;
 
            //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", 0); // Mode 0 : OpenRowset / Table - View
            srcDesignTime.SetComponentProperty("OpenRowset", "Sheet1$");
            // 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 CurrentExcelPath As Variable = package.Variables.Add("CurrentExcelPath", False, "User", "I:\Site work\SampleExcelFiles\India.xls")
        CurrentExcelPath.Name = "CurrentExcelPath"
        '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\India.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";"
        ExcelSource.Name = "Excel Connection Manager"
        ExcelSource.SetExpression("ConnectionString", """Provider=Microsoft.Jet.OLEDB.4.0;Data Source="" + @[User::CurrentExcelPath] + "";Extended Properties=\""Excel 8.0;HDR=Yes\""""")
 
        '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 FileEnumeratorInfo As ForEachEnumeratorInfo = app.ForEachEnumeratorInfos("Foreach File Enumerator")
        Dim FileEnumeratorHost As ForEachEnumeratorHost = FileEnumeratorInfo.CreateNew()
        FileEnumeratorHost.CollectionEnumerator = False
        FileEnumeratorHost.Properties("Directory").SetValue(FileEnumeratorHost, "I:\Site work\SampleExcelFiles")
        FileEnumeratorHost.Properties("FileSpec").SetValue(FileEnumeratorHost, "*.xls")
        '0 = "Fully qualified" ; 1 = "Name and extension" ; 2 = "Name only"
        FileEnumeratorHost.Properties("FileNameRetrieval").SetValue(FileEnumeratorHost, 0)
        FileEnumeratorHost.Properties("Recurse").SetValue(FileEnumeratorHost, "False")
        ForEachLoopContainer.ForEachEnumerator = FileEnumeratorHost
 
        'To Set Foreach loop container variable mappings
        Dim CurrenFileVariableMapping As ForEachVariableMapping = ForEachLoopContainer.VariableMappings.Add()
        CurrenFileVariableMapping.VariableName = "User::CurrentExcelPath"
        CurrenFileVariableMapping.ValueIndex = 0
 
        '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", 0)
        ' Mode 0 : OpenRowset / Table - View
        srcDesignTime.SetComponentProperty("OpenRowset", "Sheet1$")
        ' 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 (11) Trackbacks (0) Leave a comment Trackback

Chaudhury Sourya Prakash Dash

June 26th, 2009 at 12:32 | #1

Reply | Quote

The style has a professional look and formal navigation process…
kkluwang

January 5th, 2010 at 21:04 | #2

Reply | Quote

I’ve tried this piece of code.It works fine but my requirement is to provide the user to map the fields in the web page. I’ve a datatable which has the selected excel column names as well as the selected sql table column names. I want to override the column names while mapping. Is this possible? I’m not able to access the column names directly .How do i customize the code below:

//map external metadata to the inputcolumn
foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)
{
IDTSExternalMetadataColumn90 exMetaColumn = (IDTSExternalMetadataColumn90)input.ExternalMetadataColumnCollection[inputColumn.Name];
inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
}

Please reply urgently…………………
Bob

April 21st, 2010 at 04:54 | #3

Reply | Quote

What does the data flow component look like? I cannot get the excel source to work.
Arun Mallick

April 21st, 2010 at 09:33 | #4

Reply | Quote

Hi Bob,
To check out how Data Flow component works, follow the link
http://www.sqllion.com/2009/05/ssis-programming-basic/
Arnie Rowland

August 3rd, 2010 at 20:08 | #5

Reply | Quote

Arun,

I invite you to add this to the SQL Server Technet wiki.

http://social.technet.microsoft.com/wiki/contents/articles/sql-server-overview.aspx
zizou

August 26th, 2010 at 17:23 | #6

Reply | Quote

the package fails when i run it i don’t know why instead of green i have red
Arun Mallick

August 26th, 2010 at 17:26 | #7

Reply | Quote

Hi zizou,
if you can post the exact error, then only i may suggest you some solution.
The code is tested on both SQL 2005 and SQL 2008; its working fine.
vinoth

August 30th, 2010 at 10:56 | #8

Reply | Quote

HI Friends,
I need a urgent help, i have a xml data, i want to import this data into data base using ssis.But i want to use for loop for checking this data and import to database.

checking means if any rows contain wrong data, that is not import to the correct it will be import into error created table. how can i do this pls help me with screnn shots
Nick

August 30th, 2010 at 16:34 | #9

Reply | Quote

Hi Vinoth,
Use error handling in SSIS to check for in-correct data and redirect it to the error handling tables.
Hope that will resolve your problem.
vinoth

August 31st, 2010 at 12:59 | #10

Reply | Quote

Hi Nick,
Thanks for your immediate reply, could u pls give some small example for this, because i am very new for this ssis.

pls help me thanks
vinoth

September 1st, 2010 at 10:16 | #11

Reply | Quote

HI Friends,
I need another urgent help with example.
Task:-
1.Create ssis package with import multi format file into same table like excel and xml and db resources.
2.And create loop for checking this data and import into correct table and error table
3. And create meta data and read this data path from meta table to import

pls any one my friends help me with small examples

My email is : vinothlilly@gmail.com

No trackbacks yet.

Programming Foreach Loop Container – Enumerating Excel Sheets Extreme Catalog Pro

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 Files

All Posts

Recent Posts

Categories

Archives

Track Posts

Recent Comments

June 2009
M	T	W	T	F	S	S
« May				Jul »
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30