one ssis package to load all .csv files into relevant tables

Question

one ssis package to load all .csv files into relevant tables

arkiboys

Hall of Fame

Points: 3958
More actions
June 3, 2016 at 1:17 pm

#308165

Hi,
How is it possible to use just one package to load all the .csv files into the related tables?
thank you

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

arkiboys (6/3/2016)
Hi,
How is it possible to use just one package to load all the .csv files into the related tables?
thank you

"all the .csv files" ... in the world? In your organisation? In a particular folder?

If option 3, then yes.

If option 2, then probably.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

GSquared SSC Guru Points: 260824 More actions · Answer 2

You can load multiple files into multiple tables in one SSIS package.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

stevefromOZ

SSC-Forever

Points: 43646

June 3, 2016 at 2:42 pm

#1883204

Phil P we really need a "+1" button 😀

Steve.

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 4

arkiboys (6/3/2016)
Hi,
How is it possible to use just one package to load all the .csv files into the related tables?
thank you

Unless the format of all the files were structurally identical, then only by the system knowing what to expect in the form of either a "header checker" or "file location checker" , both of which would require a configuration table, and then by using some dynamic code that would probably be quite difficult to pull off in SSIS.

Personally and unless you have dozens of different file formats, that seems like serious overkill. And, if someone makes a mistake in that single package, then ALL of your imports will tank.

If all the files are structurally identical, then a "FOR LOOP" (I think that's what they call it... I avoid SSIS) would probably be what you're looking for because, as GSquared mentioned above, it's a pretty common practice to divert parts of rows to different tables in SSIS.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arkiboys Hall of Fame Points: 3958 More actions · Answer 5

arkiboys

Hall of Fame

Points: 3958

June 5, 2016 at 9:51 am

#1883280

Thank you

Thom A SSC Guru Points: 99128 More actions · Answer 6

A Foreach loop is what you're after.

I have something similar, that checks our SFTP "In boxes" once a day to see if we're received any new files. Files in specific folders have specific formats, but those in different folders often have different. It package therefore checks which folder it has found the package in, and then calls a different package, passing the filepath, etc, as parameters. The package to call has it's name as an expression, which is worked out in case statement in a script file.

if you do have differing file formats, this might work well for you. Although the whole load will still "tank" if one is wrong, trouble shooting will be a lot easier if you only handle files of that structure in a specific package, rather than having one package to handle any and every structure.

Cheers.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk