April 21, 2015 at 10:39 am
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.
April 21, 2015 at 10:44 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 21, 2015 at 10:58 am
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,
April 21, 2015 at 11:08 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 21, 2015 at 11:13 am
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.
April 21, 2015 at 12:56 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 21, 2015 at 1:30 pm
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:-).
April 22, 2015 at 9:22 am
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
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply