Skip to main content

Using Foreach Loop Container in SSIS

Being used to DTS 2000, I was very thrilled to see Foreach Loop Container in action with SSIS 2005. It took me some time to figure out how to make this correctly work for my scenario, but once done it worked like a charm!

Mission: To be able to import all .csv files from a particular location to a SQL table, move the file to an archive folder. All of these files had same file structure, but different file names.

Plan:
I am assuming that you are familiar with creating and opening an SSIS Package.
1. Drag and drop the Foreach Loop Container (Found under Control Flow Items in Toolbox) in ControlFlow area.


2. Add 5 new variables in the Variables pane (Right click anywhere in Control Flow area and click on Variables)
Name / Data Type
varFileName / String
varSourceFolder / String
varFilePath / String
varArchiveFolder / String
varArchivePath / String



3. Now its time to change the variables' properties in the Properties pane.
Change the EvaluateAsExpression property to True for varFilePath, varArchiveFolder and varArchivePath.
Change the Expression property as follows:
varFilePath: @[User::varSourceFolder] + "\\" + @[User::varFileName]
varArchiveFolder: @[User::varSourceFolder] + "\\Archived"
varArchivePath: @[User::varArchiveFolder] + "\\" + @[User::varFileName]




4. Double click on the Foreach Loop Container to open its properties.
Click on the 'Collection' section on the left and set the following properties:
Folder: This is where your csv files are saved
Files: this is where you enter your naming pattern - *.csv for all your csv files or ABC*.csv for all your csv files beginning with ABC
Retrieve file name: make sure Name and Extension radio button is checked






5. Now, add the Data Flow Task. Set your source connection as follows and complete the transformation:



Now, we need to move the completed (imported) files to our 'archive' folder.

6. Drop the File System Task (available under Control Flow Items in Toolbox pane) inside the Foreach Loop Container



and make sure its properites are set as follows:



Hit F5 and Enjoy!


Recommended Links:
SSIS Expression Functions: http://msdn2.microsoft.com/en-us/library/ms141671.aspx
Jamie Thomson's Blog 'SSIS Junkie' at http://blogs.conchango.com/jamiethomson/default.aspx
TechRepublic: http://blogs.techrepublic.com.com/datacenter/?p=237
Table Based Foreach Loop: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/03/25/9588.aspx

Regards,

Ankeet Shah


Please consider the environment before printing this blog! Go Green!
Labels:

Comments

Anonymous said…
Thanks for the post! Helped a lot.
April 25, 2008 at 3:30 PM
Arun Kumar said…
For more information on For each loop enumerators (Foreach ADO.NET Schema Rowset Enumerator using Excel Sheets) and programming integration service (SSIS) using C# .Net and VB .Net, please visit the below link:
http://www.sqllion.com/2009/06/programming-foreach-loop-container-%e2%80%93-enumerating-excel-sheets/
June 28, 2009 at 5:47 AM
Unknown said…
Nice to read your article!very informative post. So, please keep posting.
MicroStrategy Online Training india
May 12, 2017 at 1:38 AM
Unknown said…
Thanks for the post, very useful. Would like to suggest the best training on Microstrategy visit
MicroStrategy Online Training hyderabad
May 15, 2017 at 11:41 PM
Post a Comment

The Popular Ones

Using SQL To Calculate XIRR (Internal Rate of Return)

Thanks to binaryworld.net , I was finally able to get a sql way to calculate XIRR. After 2 long hours of search I found this site and the logic as well as the code works perfectly well! XIRR is a function in excel that calculates Internal Rate of Return based on payments/income over a period of time. Without further ado, here is the code (a slightly modified version from BinaryWorld.net. Happy XIRRing! -- First, CREATE XIRR Table to store values CREATE TABLE XIRRTempData( amt float, dt datetime, guid varchar(128) ) go create function dbo.XIRR( @d datetime, @GUID varchar(128) ) returns decimal(18,10) as begin /* USAGE: select @IRR = dbo.xirr(null, guid) select @IRR IRR, @IRR * 100 'IRR %' Note: Leave the first parameter (date) null if you wish to see the XIRR calculated as of the maximum date in the dataset provided else provide a specific date to see the XIRR calculated as the given date. Created By: Ankeet Shah Created On: 7/16/2008 */ IF @d is null SELECT @d = max(d) from Inc
25 comments
Read more

Alternating Row Background Color For SSRS Matrix (Pivot Table)

I had a tough time to apply alternate row colors to a SSRS Matrix and finally figured out! Without further ado, here it is... Assume you have a matrix with more than 1, lets say 2 row groupings; RG1 and RG2. 1. Right-click on RG2 (innermost row group), and select "Insert Group"; for simplicity call it "RowColorGroup" 2. In the "Group On" section, add some constant value - for example ="" or ="ankeet" etc... you get the idea! 3. Select the newly created group "RowColorGroup" and enter the following in its "Value" property: =iif(RunningValue(Fields!RG1.Value & Fields!RG2.Value,CountDistinct,Nothing) Mod 2, "LightSteelBlue", "White") 4. Select the "BackgroundColor" property of "RowColorGroup" and enter "=Value" 5. Set the width of "RowColorGroup" to 0pt and "CanGrow" to false 6. Select the data cell(s) and set their "BackgroundColor" pro
5 comments
Read more

cannot create a column accessor for OLE DB provider "ibmdasql" for linked server

I have a linked server from Microsoft SQL 2008 to a DB2 server. Today when I tried to run a SELECT statement based on the linked server, I hit this error, "cannot create a column accessor for OLE DB provider "ibmdasql" for linked server". Earlier in the day, we had restarted the SQL Server Service. Running the following script on the 'affected' sql server should fix the issue. USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[usp_enum_oledb_providers] AS exec sp_enum_oledb_providers GO sp_procoption 'usp_enum_oledb_providers', 'startup', 1 Restart the sql server service after running above script.
Post a Comment
Read more