how to load specific column with multiple files in ssis

Question

How can I load only id and name columns from multiple files into sql server using an ssis package?

Each file has a different structure but those two required columns are available in all files. The columns order are different between each files.

Source Files Folder is : D:\SourceFolder

Files are like below :

File1 Name : Emp_20190102
Emp_20190102 File data :
id,sal,deptno,name
1,100,10,h

File2 Name : Emp_20190102_1
Emp_20190102_1 File data :
id,name,sal
11,ac,101

File3 Name : Emp_20190102_2
Emp_20190102_2 FIle Data 
id,sal,name,deptno,loc
3,200,y,30,che
File4 Name : Emp20190102
Emp20190102 FIle Data:
id,sal,deptno,dname,flag,name
10,400,40,hr,1,un

Here I want to load only the id and name column information into the sql server table. The sql table structure :

CREATE TABLE [dbo].[Emp](
    [id] [int] NULL,
    [name] [varchar](50) NULL
)

based on above 4 files I want load data in emp table

id |Name
1  |h
11 |ac
3  |y
10 |un

This is what I tried:

step1 : created 2 variable for foldername and filename filelocation : D:\SourceFolder FileName : Emp_20190102_1.txt

then drag and drop foreachloop container and select typeofenumerator : foreach fileenumerator and variable mapping : filename variable and inside drag and drop flat file source and configure source file after that crated dynamic connection for flatfile connection then drag and drop oledb destination and configure it

After executing the package I am getting incorrect results. Here we should use only one dataflow task to load all files

can you please tell me how to implement a package to achieve this task in ssis?

This may helps: Loop through Flat Files in SQL Server Integration Services — Yahfoufi

Tab Alleman Tab Alleman · Accepted Answer · 2019-08-27T14:11:37

You cannot do this with a single dataflow in a loop. Since all files have a different structure, you will have to create a separate dataflow for each of them.

This is because the meta data for a dataflow must be set at design time. It cannot be dynamic. Furthermore, with a flat file source, you cannot SELECT only the columns you want in the source, the way you can with an OLEDB source.

The only way you would be able to do this with a single dataflow is if all files had the exact same columns in the exact same order.

how to load specific column with multiple files in ssis

1 Answers