Is it possible to load multiple flat files into table without using foreach loop container?

Question

Is it possible to load multiple flat files into table without using foreach loop container?

p.shabbir

SSCrazy

Points: 2404
More actions
April 21, 2015 at 10:39 am

#303204

Hi Experts,
Is it possible to load multiple flat files into table without using foreach loop container :-)? if so, please let me know how?
Appreciate any replies.
Thanks in advnc.

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

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

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

p.shabbir (4/21/2015)
Hi Experts,
Is it possible to load multiple flat files into table without using foreach loop container :-)? if so, please let me know how?
Appreciate any replies.
Thanks in advnc.

Sure, just create one dataflow per file and run them in series/parallel depending on needs.

Or use a FOR loop instead, if you are allowed.

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

p.shabbir SSCrazy Points: 2404 More actions · Answer 2

Hi phil,

Thanks for the quick response. I know that one DFT per file will do but, what if we have more files(Say 50 or 100 files). It will be pain to create that many DFT's.

Can you briefly tell me how to do with forloop container or provide me a reference link.

Thanks,

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

p.shabbir (4/21/2015)
Hi phil,
Thanks for the quick response. I know that one DFT per file will do but, what if we have more files(Say 50 or 100 files). It will be pain to create that many DFT's.
Can you briefly tell me how to do with forloop container or provide me a reference link.
Thanks,

I can think of a way to do it with a for loop, but the way that comes to mind is just an abstruse way of implementing a foreach loop (FEL).

You will find few examples to refer to, because everyone else will be using a FEL 🙂

Is this some sort of fun technical challenge, or is there good reason not to use a FEL?

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

p.shabbir SSCrazy Points: 2404 More actions · Answer 4

Phil Parkin (4/21/2015)
p.shabbir (4/21/2015)
Hi phil,
Thanks for the quick response. I know that one DFT per file will do but, what if we have more files(Say 50 or 100 files). It will be pain to create that many DFT's.
Can you briefly tell me how to do with forloop container or provide me a reference link.
Thanks,
I can think of a way to do it with a for loop, but the way that comes to mind is just an abstruse way of implementing a foreach loop (FEL).
You will find few examples to refer to, because everyone else will be using a FEL 🙂
Is this some sort of fun technical challenge, or is there good reason not to use a FEL?

Actually I have been asked in an inteview. So just wanted to check and try out once by ssc experts suggestions and sorry for that not mentioning initially.

Phil Parkin SSC Guru Points: 246978 More actions · Answer 5

OK, I'll give you an outline. There will be many ways of achieving this, some of which will have fewer calls to the DB engine, but here is just one.

Assumptions

1) All of the files are in the same folder.

2) All of the files have the same structure (same columns, same column order)

3) All of the files are to be loaded into the same table

Preparation

Before creating your package, create a new 'scratch' table wrk.FileLoadDetails(FileNo int PK, FilePath varchar(255)).

This table will be refreshed and repopulated every time that the package executes.

Package logic

a) Execute SQL task truncates wrk.FileLoadDetails

b) Execute SQL task populates wrk.FileLoadDetails with file info (using something like xp_DirTree) for all of the files to be loaded. FileNo is the equivalent of an identity(1,1) column, though I'd use Row_Number() in my source query just to maintain full control.

c) Return the value of max(FileNo) to a package variable (MaxFileNo).

d) Configure the FOR loop to run from 1 to MaxFileNo.

e) Within the FOR loop

-- An ExecuteSQL task gets the filepath for the current value of the loop counter

-- A dataflow (flat file source to target table) comes next. The data flow's source file path is set by an expression to use the variable populated in (c)

-- Archive the flat file to ensure that it does not get processed more than once.

Job done.

But a foreach loop avoids all of this faffing about.

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

p.shabbir SSCrazy Points: 2404 More actions · Answer 6

Phil Parkin (4/21/2015)
OK, I'll give you an outline. There will be many ways of achieving this, some of which will have fewer calls to the DB engine, but here is just one.
Assumptions
1) All of the files are in the same folder.
2) All of the files have the same structure (same columns, same column order)
3) All of the files are to be loaded into the same table
Preparation
Before creating your package, create a new 'scratch' table wrk.FileLoadDetails(FileNo int PK, FilePath varchar(255)).
This table will be refreshed and repopulated every time that the package executes.
Package logic
a) Execute SQL task truncates wrk.FileLoadDetails
b) Execute SQL task populates wrk.FileLoadDetails with file info (using something like xp_DirTree) for all of the files to be loaded. FileNo is the equivalent of an identity(1,1) column, though I'd use Row_Number() in my source query just to maintain full control.
c) Return the value of max(FileNo) to a package variable (MaxFileNo).
d) Configure the FOR loop to run from 1 to MaxFileNo.
e) Within the FOR loop
-- An ExecuteSQL task gets the filepath for the current value of the loop counter
-- A dataflow (flat file source to target table) comes next. The data flow's source file path is set by an expression to use the variable populated in (c)
-- Archive the flat file to ensure that it does not get processed more than once.
Job done.
But a foreach loop avoids all of this faffing about.

Thank you very much Phil for clear steps. Will try when I get time and update the same thread with results (either any errors or success).

Thanks once again for your time and valuable suggestion:-).

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 7

p.shabbir (4/21/2015)
Phil Parkin (4/21/2015)
p.shabbir (4/21/2015)
Hi phil,
Thanks for the quick response. I know that one DFT per file will do but, what if we have more files(Say 50 or 100 files). It will be pain to create that many DFT's.
Can you briefly tell me how to do with forloop container or provide me a reference link.
Thanks,
I can think of a way to do it with a for loop, but the way that comes to mind is just an abstruse way of implementing a foreach loop (FEL).
You will find few examples to refer to, because everyone else will be using a FEL 🙂
Is this some sort of fun technical challenge, or is there good reason not to use a FEL?
Actually I have been asked in an inteview. So just wanted to check and try out once by ssc experts suggestions and sorry for that not mentioning initially.

Heh... I'd shock and mortify the interviewer by saying that you don't need SSIS to do such a simple thing. It can be done using only T-SQL and it's not that difficult. I'd also explain how I'd do it and why I'd do it instead of using SSIS even if they were all spreadsheets or ragged right or true CSV, etc, etc.

I don't actually recommend that you tell someone that on an interview, though, because they get all huffy and ask you "Who do you think you are? Jeff Moden???". 😉

--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)