Sunday 26 May 2013

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" (Link: http://www.sqlknowledgebank.blogspot.in/2013/05/ssis-dynamic-flat-file-connection.html) Posted at date: 1st May 2013.

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.


Posted by: MR. JOYDEEP DAS

1 comment:

  1. Gr8 post, helped me a lot. I just got struck in this for a while searched a lot to overcome this but haven't found any help. Suddenly came across this post. Hope to find more gr8 posts..

    ReplyDelete