• Marek Vavrovic

Foreach Loop Container in SSIS

Updated: Aug 18

https://docs.microsoft.com/en-us/sql/integration-services/control-flow/foreach-loop-container?view=sql-server-ver15


Content


Exercise 1: create a package that will generate 12 folders.

Exercise 2: Load three (or multiple) .txt files into SQL server.

Exercise 3: How to import tables from different servers.


There are different types of enumerators in the SSIS Foreach Loop Container. You can select the enumerator type from the collection tab within the SSIS Foreach Loop Container editor form:

  • Foreach item enumerator: Loop over a set of items that can be defined manually within the SSIS Foreach Loop Container editor

  • Foreach File enumerator: Loop over files within a specific directory

  • Foreach ADO enumerator: Loop over file rows in an ADO Recordset

  • Foreach ADO.NET Schema Rowset enumerator: Loop over schema information from a specific data source (tables in a database)

  • Foreach from Variable enumerator: Loop over items stored within an SSIS variable of type object (must be enumerable)

  • Foreach NodeList enumerator: Loop over a result set of an XML Path Language (XPath) expression

  • Foreach SMO enumerator: Loop over SQL Server Management Objects (SMO) objects, such as available servers

  • Foreach HDFS File enumerator: Loop over files located within a Hadoop distributed file system directory

  • Foreach Azure Blob: Loop over blobs in a blob container in Azure Storage

  • Foreach ADLS File: Loop over files in a directory in Azure Data Lake Store

  • Foreach Data Lake Storage Gen2 File: Loop over files in a directory in Azure Data Lake Store Gen2

Exercise 1:


I want to create a package that will generate 12 folders (CurrentYear_months)


step 1: I am going to use Foreach Item Emumerator.

(Foreach item enumerator: Loop over a set of items that can be defined manually within the SSIS Foreach Loop Container editor)

step 2:

2.1 click on the button Columns.

2.2 add new column: Column 0

2.3 data type: string

step 3:

Add 12 months

step 4: go to Variable Mapping and create a variable that will map these months.


step 5: I will use File System Task to generate these directories for me. I need to prepare couple of variables.

@MonthNames: I have created this variable for the container

@DestinationFolder: this variable holds the path to the place where the folders should be created.

@ConnectionManager: I will join @DestinationFolder + Year(getdate())+@MonthNames and use this variable for File system task to create the directiries.

step 6

Click on the dots in Expression to start the Expression Builder.

step 7: build the expression

@[User::DestinationFolder] + (DT_WSTR, 4)Year(getdate())+"_"+ @[User::MonthNames]

step 8: Bring the File system task inside the Foreach Loop Container. Set it up to create directory. Use @ConnectionManager as a SourceVariable


step 9 : run the package




Exercise 2:

Load three (or multiple) .txt files into SQL server.


step 1: Create a variable @FolderPath, value of the variable is the path to the directory with files for loading.


Step 2:

Get Foreach Loop Container in.

Enumerator: Foreach File Enum (Loops over files within a specific directory)

Expressions: click on the dots.

Property Expression Editor: Find Directory

Expression: click on the dots

[..or you can just click on the Browse button and set it up without expression.]

Step 3: add the variable @FolderPath in , close it and go to Variable mapping.

Step 4: add a NEW variable @FileName. This variable will hold .txt file names.

Click on OK to come out from the setting.


Step 5 : Bring the Data Flow Task.



Step 6: Set up the Flat File Source

-click on Browse, load 1 file, Foreach loop Container will loop throw all of them.

Go to Advanced to check the data types, Columns, Preview...

Step 7: Set up the destination

Now I am going to set up the Flat File Connection Manager to loop throw the .txt files and extract them all.

7.1 click on the Flat File Con. Manager,

7.2 Expressions

7.3 in the Property Expressions Editor: ConnectionString.



step 8: write the expression

@[User::FolderPath]+ @[User::FileName]

step 9 : run the package


Exercise 3:

How to import tables from different servers

I have 2 tables on 2 different servers and I want to load these data into the 3. server.

For the data loading, I will need the server name and the database name. I am going to get them from a server table.



Step 1: create 3 variables for server and database names.

(@vTable, data type: object) is not on this picture.


Step 2: add Execute SQL Task to fetch these values from SQL Server into @vTable variable

ResultSet: Full result set

SQLStatement: Select [ServerName], [DatabaseName] from Config_Servers where IsActive=1


Step 3: go to Result Set, map @vTable as a Result set variable. You can change the Result Name to 0.

Step 4: Add Foreach loop container.

To loop through the object variable use Foreach ADO Enumerator

ADO object source variable: @vTable.


Step 5: map the variables for @vTable.


Step 6: Add Data Flow Task


Step 7: configure source

Step 8: Configure destination

Step 9: I need to convert the source connection to expression by using Property Expression Editor.

9.1 click on the source connection, press F4 to open Properties

9.2 click on the dots [...] next to the Expressions in properties

9.3 Property ServerName: assign variable @ServerName

9.4 InitialCatalog: assign variable @DatabaseName

click OK


Step 10 : UNCHECK Work Offline


Step 11 : Run the package.





30 views0 comments

Recent Posts

See All