Increment Variable within Foreach File Enumerator

Posted on 2009-12-29
Last Modified: 2013-11-10

I have a 2008 SSIS package that reads a dynamic number of files and loads it into a table.  I am using the Foreach File Enumerator that runs a data flow to populate a table.  I also have logic to move each file processed into an \archive folder and rename it with a timestamp.  I also added some event handling in the case the load errors, in which I store the ErrorDescription into a variable object as an ArrayList in the OnError event, then consolidate the ArrayList descriptions into one big string to send via an email from the OnPostExecute event.

One of the issues I have is that if this becomes scheduled, and there happens to be no files to process in the source directory, the SSIS package will run, but still kicks off the event handling portion, which I do not want it to do.  I would like to somehow not make the event handling execute if there are no source files to process so no email is sent out.

I was thinking of creating a variable that counts how many times the Foreach Enumerator runs; if no files exist, then the value would be zero, then I can use that to prevent the email script task being executed.  The problem is, I do not know how to increment a variable.  I tried creating a script task that takes a variable as the input, but it won't let me use the same one as output.

I'm sure this is pretty easy to do; I just happen to suck and only have about 1.5 days of SSIS experience.

Question by:gometang
    LVL 30

    Expert Comment

    by:Reza Rad
    as you said solution is to create an script task inside foreach loop
    but don't use your increment variable as readonly variable, you must set it in ReadAndWrite variables property and inside this script task plus one to variable.
    does it make sense to you or not?

    LVL 30

    Accepted Solution

    I created a sample for you
    look at attachements


    Author Comment

    Perfect!  Btw, how did you get the "Variable=17" window to pop up?


    LVL 30

    Expert Comment

    by:Reza Rad
    It's just a message box
    in second script task
    in Main() method
    write code below:

    Glad to help,


    Author Comment


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now