Multiple flat files to import into multiple tables (different formats)

Question

Multiple flat files to import into multiple tables (different formats)

Sirisha-631510

Newbie

Points: 3
More actions
August 31, 2009 at 1:03 pm

#138523

I have about 30 flat files each in different formats with different column names.
I need to import them creating new tables respectively. I am open to let it pick the datatypes.
I just need them to get into the database.
Bulk insert doesn't work for me because it inserts into the same table.
I would have to create a new connection for each file to create a table so I wasn't sure if SSIS for each container could do any good.
we get many files from clients throughout the year and so far they were scattered around the year so import wizard or SSIS dataflow task with OLEDB conn worked.
These days I am on a tight deadline with these files so i need a quicker and automated way to do it.
Even a batch file would work. Please help!
Thanks,
Sirisha
Create Dynamic File Connection Manager for all files
Use a For Each Loop to enumerate the file system directory to pick up the file and load the file onto the tables
Include a cleanup task to remove the file after successful load operation

Raunak J

kumar1 Ten Centuries Points: 1185 More actions · Answer 1

Hello Sirisha,

Did you find the solution for this?

I m planning to do the same with my current requirement. But I am dealing with only 3 file formats Text, excel and ASC respectively. So far I am just using import wizard, But requested to automate this.

Note: I really don't need to run a bunch per day, The task will be once a week.

Can you guide me if you already has the solution?

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 2

When you say that you have different file formats and file structures, you can leverage the following to design a SSIS package:

Phil Parkin SSC Guru Points: 246693 More actions · Answer 3

Raunak Jhawar (12/17/2013)
When you say that you have different file formats and file structures, you can leverage the following to design a SSIS package:
Create Dynamic File Connection Manager for all files
Use a For Each Loop to enumerate the file system directory to pick up the file and load the file onto the tables
Include a cleanup task to remove the file after successful load operation

You are responding to a forum newbie and these are high-level ideas which require some elucidation, in my opinion.

I am no newbie, but still don't have a clue how would create a "Dynamic File Connection Manager for all files" - what is this thing?

You mention the use of a FEL, but do not mention data flows at all. Perhaps you have a single 'Dynamic Data Flow for all files' in mind - if so, I'd like to know more please.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

dharmendra.mittal SSC-Addicted Points: 449 More actions · Answer 4

Hello Sirisha,

Did you find the solution for this?

Please let me know the approach you used.

Thanks in advance