How to use Execute SQL Task with Foreach Loop Container ( File Name Validation against Definiton Table) in SSIS Package - SSIS Tutorial


Think about a situation in which we have to read the file names from a folder and then validate against our definition table before we start loading. If file name does not match with out definition then we want to stop the Package and move the file to BadFile Folder.

What you will learn in this video
  1. How to extract file names from a Folder  in SSIS Package
  2. How to use For-each Loop in SSIS Package
  3. How to use Execute SQL Task to Validate File Name against Definition Table in SSIS Package
  4. How to use Data Flow Task to Load flat file to SQL Server Table
  5. How to use File System Task to move File from One Folder to another folder

Script used in Demo: How to Validate File Name before Loading in SSIS Package

--Create File Validation Table
CREATE TABLE [dbo].[FileValidation](
 [FileValidationID] [int] IDENTITY(1,1) ,
 [FileName] [varchar](100))

--Insert few file names in File Validation Table
 Insert into dbo.FileValidation (FileName)
 values ('CustomerFile_EU_')
 Insert into dbo.FileValidation (FileName)
 values ('CustomerFile_AS_')
 Insert into dbo.FileValidation (FileName)
 values ('CustomerFile_NA_')

--Base Script that we used in expressions in SSIS Package
SET @FileName='CustomerFile_NA_20150416 - Copy.txt'
           FROM   [dbo].[FileValidation]
           WHERE  filename = Substring(@FileName, 1, 16))
      SELECT 1 AS FileExistsFlg
Select 0 AS FileExistsFlg

--Just a Select Query to find how how many characters do we need to Extract
--From File Name
  Select SUBSTRING('CustomerFile_NA_20150416 - Copy.txt',1,16)

How to write expressions to Build SQL Statement in Execute SQL Task in SSIS Package for File Name validation before Loading

 Related Posts / Videos on Execute SQL Task