Looping through specific files – SSIS

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

As you may know, there is an enuerator in Foreach Loop Container which help you to loop through files, named File Foreach Enumerator. you can specify a location for searching through files and get files as enuerator in loop.
But, there are some times which you need to loop through specific files not all of files in the directory.
you can use MASK in file names and then just loop through match files. I want to talk about another common real world problem now.

Suppose you have a table which has file names inside, and you have a directory of physical files. now you want to loop through files in directory and check if the file is in table do task1 else do task2.

let’s go on sample, through this sample you will find how to implement
foreach loop container for file enumerator and use execute sql task with
precedence constraint.


First of all our table structure in database is:
CREATE TABLE [dbo].[RawFiles](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [DateTime] [datetime] NOT NULL,
    [FileName] [nvarchar](max) NULL,
    [SourceFileBin] [varbinary](max) NULL,
    [FileType] [int] NULL,
    [Switch] [bigint] NULL,

 CONSTRAINT [PK_RawFiles] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

the [FileName] column has exact file name values inside.

now create a SSIS package,
Add two package scope variables:
FileName    datatype: string
Status        datatype: Int32
Add a Foreach Loop Container
double click on foreach container and Foreach Loop Editor window will open,
go to Collection tab, set Enumerator as Foreach File Enumerator
in Enumerator configuration, set Folder to the folder your physical files exists there.
in the Retrieve file name section select NAME ONLY , because we have only file names in table we want to lookup.


go to Variable Mappings tab,
select User::FileName in variable column, and set Index column as 0.


now Foreach loop container setting is finished, this will loop through files in the specified folder and in each iteration fills the FileName variable.

Add an Execute SQL Task inside the Foreach Loop container,
double click on it to open Execute SQL Task Editor,
connect it to your database with an OLE DB Connection Manager,
set ResultSet property to Single Row.
set SQLStatement as :
select count(*) as cnt from RawFiles where FileName=?

question mark in this statement means parameter,

now go to Parameter Mapping tab,
Add a line there,
set Variable Name as User::FileName ,
set Data Type as Nvarchar and parameter size as 500 , this is because filename field in table has Nvarchar data type,
set Parameter Name as 0 ,
Note that when you use OLE DB Connection managers, then you should name your parameters with 0,1,2,3 …


this will fill the question mark parameter with the value from FileName variable.

now go to Result Set tab,
Add a line there,
set Result Name as cnt ,
set Variable Name as User::Status ,


this will fill the Status variable with result of query above, that means if there was a record with this filename in table then Status will be 1 or more, else will be 0 .

now , filling status made complete, the only step remains is that you should check value of Status and send it to appropriate task. suppose you want to do this:
status>0  -> message box which shows "exists!"
status<=0  -> message box which shows "NOT exists!"

so, Add two Script tasks after Execute sql task inside the foreach loop,
connect green arrow from execute sql task to these script task,
for the first script task, right click on green arrow, select Edit
Precedence Constraint Editor will open,
set Evaluationi Operation as EXPRESSION,
type this expression in EXPRESSION text box:
@Status>0

do same thing for another script task and set expression there as @Status<=0

write a MessageBox.Show("exists") in first script task, and MessageBox.Show("NOT exists") in second script task.

All done, just run the package.

this is whole schema:


Fine.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply

Your email address will not be published. Required fields are marked *