Load all flat files from folder into sql table one by one

Question

Load all flat files from folder into sql table one by one

vipin_jha123

Hall of Fame

Points: 3085
More actions
April 7, 2015 at 11:15 pm

#296693

Hi,
I have created a ssis package where I am loading a data from flat file to staging table and staging to import table.
In this package input parameter is Filename.
First task of this package is For each loop container.
under that I am using executesql task to load the information like filename and load date and load id into table.
Filename of latest load id is a input to the package.
for single file its working fine.
but how to handle this when multiple files are there.
i want to take one file insert into file information table and load it into staging.
then go to next file
please suggest
regards,
Vipin jha

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

This is a common requirement and there are numerous online articles available which describe the process. Which part of this process are you having trouble with? Or is it just that you want someone else to do all the work for you?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 2

Hi ,

I have created the second part of the package and its working fine

I want to know how can i load the file information one by one and go to next step once completed the second step again come to second file and load file info to table and go to nextr step

Under the FL container I am using execute sql task which is used to insert filename into table.

after that there are so many task which use to laod data from flat file to staging and then staging to import table.

I am not using flat file connection for loading , I am using bulk insert to do same.

i want if i have 3 files in my source folder then my activity should be like below

1-Read the file information like filepath,filename and load into table.

2-then after that run the remaining task of the package

3-once done with that come to 1st task to load the second fileinfo and load into table and so on

regards,

Vipin jha

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Use a for each loop with a wildcard, store the filename into a variable and use a flat file connection manager with an expression on the filename using that variable. Use a data flow to import the flat file.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP