Multiple Flat files To SQL Server

Prerequisite :

1. Source :

Copy this contents in text file with name Product1.txt

KATE,VEG,2

KATE,SODA,6

KATE,MILK,1

KATE,BEER,12

KATE,VEG,3

Copy this contents in text file with name Product2.txt

FRED,MILK,3

FRED,BEER,24

1. Make destination table by using figure 12 by using New button.

To create a new SSIS package, follow these steps:

1. Launch Business Intelligence Development Studio.
2. Select File > New > Project.
3. Select the Business Intelligence Projects project type.
4. Select the Integration Services Project template.
5. Select a convenient location.
6. Name the new project ISProject1 and click OK.

[A] : Import multiple CSV files without file names.

Figure : 1

Figure : 2

Figure : 3

Figure : 4

Figure : 5

Figure : 6

Figure : 7

Figure : 8

Figure : 9

Figure : 10

Figure : 11

Figure : 12

Figure : 13

Figure : 14

Figure : 15

Figure : 16

[B] : Import multiple CSV files with file names.

• Right click on flat file source
• Show Advanced editor ==> Component properties 

Figure : 17

Figure : 18

Figure : 19

Figure : 20

Figure : 21

Script to remove pathname from filename column.
-- =================================================
-- Make sure the column name is "Filename" and
-- replace [Table_Name] with actual table name
-- =================================================

Update [Table_Name]
Set [FileName] =

Right(([Filename]),CHARINDEX('\',Reverse([Filename]))-1)

Where [FileName] Like

'%\%'