For Each Loop Container in SSIS

It defines a repeating control flow in a package. Loop implementation in the for each loop container is similar to the ForEach loop concept in programming language.
Types of EnumeratorsForeach File Enumerator – This enumerate files in a folder. We can get list of files which has extension .jpg from any folder and its subfolders
Foreach Item Enumerator – Enumerate values in an item
Foreach ADO Enumerator – Enumerate rows in tables
Foreach ADO.Net Schema Rowset Enumerator – Enumerate a schema
Foreach from variable Enumerator – Enumerate the value in a variable
Foreach nodelist Enumerator – Enumerates nodes in an XML document
Foreach SMO Enumerator – Enumerate a SMO Object.



Foreach Loop Container Properties1. Enumerator – Select appropriate enumerator (select Foreach File Enumerator)
2. Folder – Select source folder from where you want to enumerate files
3. Files – Specify extension (*.xls)
4. Drag the script task inside the for each loop container
5. Bind the variable User::File_Name to ReadOnly Variable
6. Below code snippet displays list of files which are matched with .xls extension from the specified folder.


MsgBox(Dts.Variables("File_Name").Value)
Dts.TaskResult = ScriptResults.Success

SSIS : Test connection failed because of an error in initializing provider. Unrecognized database format

Test connection failed because of an error in initializing provider. Unrecognized database format 'C:\Testdata.xlsx'.

 

If you encounter excel connection error then we need to type Excel 12.0 on Extended properties as below

 

 

Variables in SSIS

Variables
We can define variables in SSIS using variables window with specific scope. We can define variables at different scope levels and with different datatype, for example defining variable at package level, defining variable at tool level (specific to that particular tool for example variable at for loop level, variable at Execute SQL Task level etc.,)
Below are few examples to create variables

BIDS – View – Other Windows – Variables – It displays variables window and it consists the following icons
1. Add variable
2. Delete Variable
3. Show System Variables
4. Show All Variables
We can declare a variable with required data type and we can initialize value at the time of declaration itself.

To make use of variable we need to bind variable to tool, for example if you are working with script task below is the example.

Create two variables varInt (Int) = 123, varString (string) = “hello” at package scope level. Whenever if you create a variable then it should be bind with any control, otherwise we can’t access that variable.

Example using Script Task
1. Drag the script task to control flow window
2. Right Click – Edit – Edit Script
Public Sub Main()
' Add your code here
MsgBox(Dts.Variables.Count) -- This code returns zero because no binding done
Dts.TaskResult = ScriptResults.Success
End Sub
To bind the variables to script task
1) Right click on script task – Edit
2) ReadOnlyVariable – Click on Ellipse and browse the variables which you want to use in the script.
3) Insert the below snippet to get the variable names
        For i = 0 To Dts.Variables.Count - 1
            MsgBox(Dts.Variables(i).Name.ToString())
        Next
 

How to change script language in SSIS

From 2008 onwards we can implement c# script along with vb script in SSIS.

To change language for script task.
BIDS -- Tools - Options
Business Intelligence Designers - Integration Service Designers -- General
Script Language -- Choose the script language from here.

Working with variables in SSIS

Variables

We can define variables in SSIS using variables window with specific scope. We can define variables at different scope levels and with different datatype, for example defining variable at package level, defining variable at tool level (specific to that particular tool for example variable at for loop level, variable at Execute SQL Task level etc.,)

Below are few examples to create variables

BIDS – View – Other Windows – Variables – It displays variables window and it consists the following icons

1. Add variable
2. Delete Variable
3. Show System Variables
4. Show All Variables

We can declare a variable with required data type and we can initialize value at the time of declaration itself.

To make use of variable we need to bind variable to tool, for example if you are working with script task below is the example.
Create two variables varInt (Int) = 123, varString (string) = “hello” at package scope level. When ever if you create a variable then it should be bind with any control, otherwise we can’t access that variable.

Example using Script Task

1. Drag the script task to control flow window
2. Right Click – Edit – Edit Script

Public Sub Main()
' Add your code here
MsgBox(Dts.Variables.Count) -- This code returns zero because no binding done
Dts.TaskResult = ScriptResults.Success
End Sub

To bind the variables to script task
1) Right click on script task – Edit
2) ReadOnlyVariable – Click on Ellipse and browse the variables which you want to use in the script. 
3) Insert the below snippet to get the variable names
       For i = 0 To Dts.Variables.Count - 1
                MsgBox(Dts.Variables(i).Name.ToString())
       Next