Looping through all the files in a folder and loading data to SQL (SSIS)

Introduction

If you have gone thru the articles posted by karthik let me guide you further on how to loop thru an input folder and pick files from there and load them into SQL Database.

So Lets start...

Step 1 - Open Visual Studio, Select Integration Services Project template from Create New Project option. You will get Package.dtsx loaded on to the screen.

Step 2 - Drag drop the foreach loop container on to the designer and in it drop one Data Flow Task.

Step 3 -Double click on for each loop container, Select Collection option and configure the values as shown in the figure.

Select Variable Mapping, below the variable option select new variable and name it say CurrentFile to save the filename while looping thru the folder, finally click ok to close the window.

Step 4 -Add two flat file connection manager one for input file and one for error file and one OleDB connection manager to connect to database table.

Step 5 - Configure the inputfile connection manger by specifing the sample file path and then click on columns to preview the data in the file. Then click on advanced tab to set the column datatypes matching the table datatypes then click ok.

Similary set the errorfileconnection manager also.

Then double click on the oledb connection manger point into the table in the database where you want to lad the data.

Step 6 - Double click on the Data Flow Task and Drop one Flat File source, Oledb destination and one file destination.

Double click on each component and point to the respective connection managers.

And in case of OLEDB map the columns as per the columns in the table.

And in the error input tab you need to specify as below

Thats it from the package level.

Lets create a table in the Database

Create table Student(
rollno int primary key,
nm varchar(50),
age int,
sex char(1),
loc varchar(10))

Similarly you can specify some other test files in the input folder.

Then execute the package you will see that the data gets loaded into Table and any error records will be moved to the error file.

This shows that the package got executed successfully.

Conclusion

Hope this article will take you to the next level of SSIS package creations.

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.