SSIS Package to Read A DIrectory and Import many CSV files into a Table

Hi Guys

We have an application that we need to import a bunch of small CSV files (all the same structure) into a single existing table then delete the small csv file on an iterative basis.

We can do this on a single file by file basis but I need some help to make it iterative. The files are names EPOS_date_time.csv so each filename is unique.

Cheers C

Who is Participating?
Jason Yousef, MSConnect With a Mentor Sr. BI  DeveloperCommented:
You can use the FOR EACH LOOP CONTAINER as 8080  said.
here's some help about it:

Also I've attached some screen shots of what the package would look like 1 2 34.jpg
lcohanConnect With a Mentor Database AnalystCommented:
Here's what I would do - lots of SQL dynamic code but is possible:

exec xp_cmdshell 'DIR C:\Folder_name\EPOS*.* > C:\Folder_name\EPOS_list.txt'

--read that file in a SQL table like:

create table #tempfile (line varchar(8000))
exec ('bulk insert #tempfile from "C:\Folder_name\EPOS_list.txt"')
select * from #tempfile where line like 'EPOS%'
--drop table #tempfile
--Use this to build a dynamic SQL fora each CSV and import it into a permanent table same as we did above.
8080_DiverConnect With a Mentor Commented:
There are native SSIS components that will facilitate this processing.  For instance, you can set up the following:
A File connection type that uses a variable to determine the pathed search file name;
Then set up a For Each Loop container that will drive off of that File Connection;
Within that container, 1) Set up a Data Flow Task to import the data to a staging table;
2) set up a File System Task to delete the source file;
Finally, outside of the For Each Loop Container, set up another Data Flow Task to process the staging table's data to add it to the ultimate target table.

Handling the different filenames is not really a problem because the For Each Loop Container can be set to use an Expression for the Directory Path and for the File Spec and those Expressions are driven by User Variables that you control.  In a Script Task, you set those variables (although, if the folder path is constant, that can be set once when the variable  is defined) using VB.Net.  (I generally set the File Spec to the Path + left part of the FileName + * but, in your case I would set it to Path + "EPOS_*.csv".)

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Alpesh PatelAssistant ConsultantCommented:
Yes huslayer: is right

Use Foreach to Directory location to get each file and insert into table using data flow task. and last wish to create then you can.
Jason Yousef, MSSr. BI  DeveloperCommented:

Any luck?
Do you need more help?

Jason Yousef, MSSr. BI  DeveloperCommented:
Hey Pedro, thanks for the cleaning job, great efforts man.

I'm fine by splitting the points between  @lcohan , @8080_Diver and @Huslayer, I think the 3 of us supplied a valid answer.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.