How to Load Files from Specific Folder Paths Saved in a SQL Table By using SSIS Package - SQL Server Integration Services ( SSIS ) Tutorial

Scenario:

We have multiple folders inside our main folder but for our SSIS Package we want to load only files from some specific folders which are assigned to that SSIS Package. The folder paths are saved with SSIS Package Name in a Table. We want to Extract the Folder Paths and then load the files to a SQL Server Table in SSIS Package.

What you will learn in this SSIS Package

  1. How to create a Table to store Folder Path and SSIS Package Name
  2. How to Extract Required records (Folder Paths) in SSIS Package from a SQL Server Table by using Execute SQL Task
  3. Why to use Full Result Set in Execute SQL Task in SSIS Package
  4. How to Loop through Object Type variable by using For-each Loop Container and save value to variable
  5. Use nested For-each Loop to use the Folder path that was returned by outer For-each Loop Container.
  6. How to use Variable value in Flat File Connection Manager by using Expressions
  7. How to Archive Files after loading to SQL Server Table in SSIS Package

Script used in the video to save folder path to SQL Table for Loading

USE [Test]
GO


--drop table [dbo].[FilesToLoad]
CREATE TABLE [dbo].[FilesToLoad](
    [PackageName] [varchar](100) NULL,
    [FolderOath] [varchar](100) NULL
)

insert into dbo.FilesToLoad
values ('Pkg_ASia_Europe_FileLoad','C:\Users\Aamir\Desktop\InputFolder\Customer_AS'),
('Pkg_ASia_Europe_FileLoad','C:\Users\Aamir\Desktop\InputFolder\Customer_EU')

Select * from [dbo].[FilesToLoad]




How to Load Files from Folders which paths are saved in SQL Server Table in SSIS Package



 Related Posts / Videos on Execute SQL Task