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
DROP TABLE tbl_PRODUCTDETAILS;
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);
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;
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