SSIS load multiple files with same structure but diff extension using FELC

Newbie Newbie 21 Reputation points
2021-02-25T02:10:37.01+00:00

I have a scenario where I need to load multiple files with different file types but same structure eg: Emp_USA_012021.txt (Empid, name, desig) Empl_UK_012021.csv (Empid, name, desig) Employees_Europe_202101.xlsx (Empid, name, desig) Into sql table dbo. tbl_emp (Empid, name, design) Can this be done using FELC or I have to use individual DFTs? Please advise.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,486 questions
0 comments No comments
{count} votes

Accepted answer
  1. Monalv-MSFT 5,896 Reputation points
    2021-02-25T07:58:39.097+00:00

    Hi @Newbie Newbie ,

    We should use 2 Data Flow Tasks because we have txt, csv, xlsx files.

    We can use Foreach Loop Container to find these files.
    Then drag empty Sequence Container and 2 Data Flow Tasks.
    Set expression in Precedence Constraint between Sequence Container and 2 Data Flow Tasks.

    Please refer to the following pictures:
    71958-cf-variables.png
    71959-flc-collection.png
    71929-pc1.png
    71890-expression.png
    Expression1: TOKEN(@[User::FileName], ".", 2)=="txt" || TOKEN(@[User::FileName], ".", 2)=="csv"
    Expression2: TOKEN(@[User::FileName], ".", 2)=="xlsx"
    71938-dft1.png
    72021-ffcm-expression-connectionstring.png
    71939-dft2.png
    71996-excelcm-expression-excelfilepath.png

    Best regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Yitzhak Khabinsky 25,376 Reputation points
    2021-02-25T06:02:07.097+00:00

    @Newbie Newbie ,

    It is very easy to implement by using SSIS Foreach Loop Container
    Here is a good step-by-step example: SSIS - How To Load Multiple Files ( .txt or .csv ) To a Table With different File Names

    0 comments No comments