Foreach Loop with Dynamic Flat File Connection

Introduction

One of my friends working with SSIS told me that he is using a Foreach Loop Containers to connect with a flat file from a specified directory. The directory contains more than one flat file with the name with date. That means the flat file name comes with date extension like this File2652013.txt and each day one flat file is stored within this directory. He needs to read the current dated flat file only.

To do this he use one of my preciously posted article named "SSIS Dynamic Flat file Connection" 

He is able to read current date file but his problem is due to Foreach Loop containers. As the folder contains multiple flat file the loop moving according to that and read the current dated flat file multiple time and hence records are duplicated in the destination table. As he is not using any primary key in the destination table, he gets the duplicate records in the table and if he use the primary key constraint in the destination he get a duplicate key error.

How to solve this problem

To solve this problem he needs a break like statement to break the loop (in case any other programming language an IF condition and Break statement). Here in SSIS it is not possible. To solve this problem I am using a Script Task and a Package level variable. The initial value of the package level variable in "0". When it passes to the script task it check the value of the variable. If  it is "0" then the script result is Success others it failure. In this way I control the looping of the For each Loop.

So let's Start

Step-1 [ The source flat file details and Destination table objects  ]

We have 2 source flat file in the folder path "F:\Practice_SQL\SSIS\SSIS Examples\SourceFlatFile"

IF OBJECT_ID(N'tbl_PRODUCTDETAILS', N'U') IS NOT NULL

   BEGIN

     DROP TABLE tbl_PRODUCTDETAILS;

   END

GO

CREATE TABLE tbl_PRODUCTDETAILS

       (PRODID      VARCHAR(50)   NOT NULL,

        PRODNAME    VARCHAR(100)  NOT NULL,

        PRODQTY     DECIMAL(20,0) NOT NULL,

        PRODUOM     VARCHAR(7)    NOT NULL);

G0

Step-2 [ Create the Package level variable ]

Step-3 [ SSIS Control Flow and Data Flow ]

Step-4 [ Foreach Loop Containers Editors ]

Step-5 [ Script Tasks Editor ]

        public void Main()

        {

            Int32 intCount = 0;

            intCount =Convert.ToInt32(Dts.Variables["v_CountLoop"].Value);

            if (intCount == 0)

            {

                Dts.Variables["v_CountLoop"].Value = 1;

                Dts.TaskResult = (int)ScriptResults.Success;

            }

            else

            {

                Dts.TaskResult = (int)ScriptResults.Failure;

            }

        }

Step-6 [ Flat File connection String Expression ]

@[User::v_FilePath]  + "File"+(DT_STR,4,1252)DAY(getdate())+(DT_STR,4,1252)MONTH(getdate())+(DT_STR,4,1252)YEAR(getdate())+".txt"          

Step-7 [ Running the Package ]

Step-8 [ Destination Output ]

SELECT * FROM tbl_PRODUCTDETAILS;

PRODID      PRODNAME                                       PRODQTY            PRODUOM

PROD1         PWB PLYWOOD 1x4x19MM              200                         PCS

PROD2         PWB PLYWOOD 2x4x19MM              300                         PCS

PROD3         PWB PLYWOOD 3x4x19MM              300                         PCS

   

Hope you like it.