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


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]

--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'),

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 

1 comment:

  1. I feel SSIS and other useful aspects actually help one solve complex IT problems and solutions as well.Its undoubtedly very compatible in solving complex IT problems.

    SSIS Upsert