Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pssheba
Yak Posting Veteran
95 Posts |
 Posted - 2009-01-10 : 04:17:53
|
| Hi everyone,Inside a folder i have few hunderds excel sheets of a same structure. I want it all to be aggregated into one OLE DB table.Importing 300 times excel sheet to a table is literally impossible. Creating SSIS project with 300 connections, each conncetion to a different excel sheet is not a much better job.The excel sheets carry names that resemble each other and it goes:file1.xlsfile2.xlsfile3.xls...file300.xlsit all resides in the same folder as i mentioned and of the same structure. Any idea how i could make a loop making all the files inside a folder to be added into an OLE DB table?Thansk |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-10 : 12:19:56
|
| And you can transfer already processed xls files to Archive folder with File system task inside ForeachLoop container. |
 |
|
|
pssheba
Yak Posting Veteran
95 Posts |
Posted - 2009-01-11 : 07:32:53
|
quote:
Originally posted by visakh16
yup. you can use For Each Loop container with File enumerator which looks for all files in folder and transfers ithttp://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
Thanks, this link shows only how to match file name in a folder to a variable. It does not show how to add data from several excel files reside in one folder to an OLE DB table. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-11 : 07:36:26
|
quote:
Originally posted by pssheba
quote:
Originally posted by visakh16
yup. you can use For Each Loop container with File enumerator which looks for all files in folder and transfers ithttp://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
Thanks, this link shows only how to match file name in a folder to a variable. It does not show how to add data from several excel files reside in one folder to an OLE DB table.
Inside ForEachLoop container, You can add Data Flow Task with Excel Source and OLEDB destination and move the processed files to archive folder with File System Task |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-11 : 11:51:50
|
quote:
Originally posted by sodeep
quote:
Originally posted by pssheba
quote:
Originally posted by visakh16
yup. you can use For Each Loop container with File enumerator which looks for all files in folder and transfers ithttp://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
Thanks, this link shows only how to match file name in a folder to a variable. It does not show how to add data from several excel files reside in one folder to an OLE DB table.
Inside ForEachLoop container, You can add Data Flow Task with Excel Source and OLEDB destination and move the processed files to archive folder with File System Task
and make sure you map the datasource property of excel file to variable into which you get filename from for each loop container |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-11 : 20:20:11
|
| You are looking for this one .Right!!http://pragmaticworks.com/community/blogs/nayanpatel/archive/2008/11/13/how-to-loop-through-files-in-a-specified-folder-load-one-by-one-and-move-to-archive-folder-using-ssis.aspx |
|
|
|
|
|
|
turning many excel sheets into one OLE DB