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 

3 comments:

  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

    ReplyDelete
  2. Hello Everyone,

    Welcome to the future! Financing made easy with Prof. Mrs. DOROTHY LOAN INVESTMENTS

    Have you been looking for financing options for your new business plans, Are you seeking for a loan to expand your existing business, Do you find yourself in a bit of trouble with unpaid bills and you don’t know which way to go or where to turn to? Have you been turned down by your banks? MRS. DOROTHY LOAN INVESTMENTS says YES when your banks say NO. Contact us as we offer financial services at a low and affordable interest rate of 2% for long and short term loans. Interested applicants should contact us for further loan acquisition procedures via profdorothyinvestments@gmail.com

    Services rendered include:

    * Refinancing Loans
    * Car Loan
    * Truck Loans
    * COVID-19 Financing Loan
    * Home Loan
    * Mortgage Loan
    * Debt Consolidation Loan
    * Farm Loan
    * Business Loan [secure and unsecured]
    * Personal Loan [secure and unsecured]
    * Students Loan and so many others.

    For more info; Contact us via Email: profdorothyinvestments@gmail.com

    With Prof. Mrs. DOROTHY LOAN INVESTMENTS. you can say goodbye to all your financial crisis and difficulties as we are certified, trustworthy, reliable, efficient, fast and dynamic

    ReplyDelete
  3. It can be textured for slip resistance in wet conditions.
    Concrete and Asphalt Paving

    ReplyDelete