How to Reject Already Loaded Files and Load Only New Files in SSIS Package - SQL Server Integration Services (SSIS) Tutorial

Scenario: 

We get the files from our client that we need to load on daily basis to SQL Server Table. Sometime there are chances that the client might provide the same file again. In that case we want to move that file to Rejected Folder and don't want to load. If any new file will be received in the folder, we will load that after confirming the file is not loaded already.

What you will learn in this video

1. How to read the files from Folder by using For-each Loop Container
2. How to Log the file name into the SQL Server Table for validation
3. How to Check if file is already loaded. You will build your query in Execute SQL Task by using Expressions
4. How to use File System Task to move file from Source To Reject Folder in SSIS Package
5. Move the new file to Archive folder after loading first time by using File System Task.

Script used in the video to handle already loaded files to move to RejectFolder and Load New Files in SSIS Package

--Create FileLoadStatus Table
CREATE TABLE [dbo].[FileLoadStatus](
 [ID] [int] IDENTITY(1,1) ,
 [FileName] [varchar](100),
 LoadDateTime datetime default getdate())


--Base Script to Check if File is already loaded or not
DECLARE @FileName VARCHAR(100)
SET @FileName='CustomerFile.txt'
IF EXISTS (SELECT 1
           FROM   [dbo].[FileLoadStatus]
           WHERE  filename =@FileName)
  BEGIN
      SELECT 1 AS FileExistsFlg
  END
ELSE
BEGIN
Select 0 AS FileExistsFlg
END

How to Archive Already Loaded Files and Load New Files To SQL Server Table in SSIS Package