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

Scenario:

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
DECLARE @FileName VARCHAR(100)
SET @FileName='CustomerFile_NA_20150416 - Copy.txt'
IF EXISTS (SELECT 1
           FROM   [dbo].[FileValidation]
           WHERE  filename = Substring(@FileName, 1, 16))
  BEGIN
      SELECT 1 AS FileExistsFlg
  END
ELSE
BEGIN
Select 0 AS FileExistsFlg
END


--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 

• Execute SQL Task Demo for Max Size of SQL Statement in Execute SQL Task Query Editor and Solution
• Execute SQL Task Demo- How to Build Dynamic SQL Query to Execute Multiple Stored Procedures those names are saved in a SQL Server Table
• Execute SQL Task Demo - How to Execute SQL Queries from a SQL Server Table in SSIS Package ( SQLStatementType= Variable)
• Execute SQL Task Demo - How to Execute SQL Queries from Excel file in SSIS Package ( Record Set Destination, Object Type Variable and Foreach Loop Container Demo as well)
• Execute SQL Task Demo - How to use Single Row Result Set in Execute SQL Task and Control the Flow Tasks by using that value ( Precedence Constraint Demo as well).
• Execute SQL Task Demo - How to use Insert Query in Execute SQL Task and Map the variables to Parameters ( Insert File Name and Record Count after Loading the File).
• Execute SQL Task Demo - How to use Stored Procedure with Input/Output Parameters in Execute SQL Task in SSIS Package( Load File Name, Package Name , Record Count and File Load Time in SQL Server Table).
• Execute SQL Task Demo - How to use Full Result Set in Execute SQL Task ( Get Folder Path and File Names from SQL Table and Load those files)
• Execute SQL Task - How to Load Files from Specific Folder Paths Saved in a SQL Table By using SSIS Package
• Execute SQL Task ( use Variable value in Expressions Demo) - How to Reject Already Loaded Files and Load Only New Files in SSIS Package
• Execute SQL Task ( Handle Single Row Result Set Blank Error) -Single Row result set is specified, but no rows were returned in Execute SQL Task in SSIS Package
• Execute SQL Task ( Save Result Set to Variable Demo) - How to Return Deleted and Update 
• Record Count from Execute SQL Task and Write to Flat File in SSIS Package
• Execute SQL Task ( Build Dynamic Query in Variable and use as Source ) - How to Save Query in Variable and Use in Execute SQL Task 
• Execute SQL Task Expression's Demo - Using Variable in IF Clause in Execute SQL Task in SSIS Package
• Execute SQL Task ( Parameter Mapping Demo for Stored Procedures) - How to Run multiple Stored Procedure in Execute SQL Task with Input Parameters
• Read Single Cell Value from an Excel File in SSIS Package