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

Madhu.b.rokkam
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 16568 red flag
Rating: 5 out of 5  
 1 vote(s)

This article will guide you on how to loop through a folder and process and load all the txt file data into SQL Server using 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.

Page copy protected against web site content infringement by Copyscape

About the Author

Madhu.b.rokkam
Full Name: Madhu Rokkam
Member Level: Bronze
Member Status: Member,MVP
Member Since: 1/13/2011 3:13:20 PM
Country: India
Thanks and Regards Madhu
http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Posted by: Tripati_tutu on: 2/21/2011 | Points: 25
Better article on SSIS...
Posted by: Madhu.b.rokkam on: 2/21/2011 | Points: 25
Will post much much better articles in the comming weeks

Login to post response

Comment using Facebook(Author doesn't get notification)