How to use SSIS to import data file by looping though the files in the folder?

Posted on 2011-10-14
Last Modified: 2013-11-10
How to use SSIS to import data by looping though the files in the folder?

Suppose we have data folders as follows:


The file types can be: .zip, xls, csv and xml.

How do we dynamically configure the data source to achieve the goal?

The destination is always SQL 2008R2.

Question by:chuang4630
    LVL 21

    Assisted Solution

    by:Alpesh Patel
    1. Create variable in that dynamically create folder path
    2. assign this path for the Foreach loop as source folder.
    3. Loop through each file and insert into table.

    For connection to file create dynamic connection string using Expression.
    LVL 30

    Accepted Solution

    as file types are different you need to create different sources for them.
    I mean -> excel source for xls files
    flat file source for csv files
    xml source for xml files
    also you need to unzip the compressed file with the execute process task first and then read its data.

    also you should note the structure of data , is all structure same? I mean number of columns and data type and name of them..?

    by the way I think you mean something else;
    did you mean that you want to load these physical files into image or varbinary fields into database?
    if yes, you just need a data flow task with IMPORT COLUMN TRANSFORM, I wrote a sample here:
    LVL 1

    Author Comment

    Where do you do foreach loop? So far I have just created the File System Task. Any new task needed?

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now