|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, September 24, 2009 3:09 PM
Points: 1,
Visits: 0
|
|
Hello all - newbie here so be gentle!
Ok, here is the situation. I have a number of files with weekly sales information:
Sales1.xls
Sales2.xls
Sales3.xls
Each of these have identical data sets and I have succeeded in creating a foreach loop container..lovely stuff!
However, each of these weekly reports have slightly different sheet names:
Sales1.xls >>> Wk1
Sales2.xls >>> Wk2
Sales3.xls >>> Wk3
Now this poses a problem with the forloop each container.
I attempted to created a variable based on the worksheet name and change the excel file so that it looks at the table with varible and selected that worksheet variable.
However, when I attempt to run this, it runs through the first file, executes that data flow but then fails when it encounters the second file.
Has anyone got any ideas how I can sort this and/or a step by step procedure to complete this action?
Apologies for being thick and any help gratefully received.
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Yesterday @ 2:22 PM
Points: 71,
Visits: 173
|
|
I used the method in http://msdn.microsoft.com/en-us/library/ms345182.aspx
go down about halfway to the section titled: "To loop through Excel tables by using the Foreach ADO.NET Schema Rowset enumerator"
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Thursday, March 11, 2010 10:13 PM
Points: 223,
Visits: 283
|
|
Those instructions are for looping within a single excel file and then importing data from the tables with different name in that Excel file.
Would that apply when we need to import data from multiple Excel files with same definitions but different table names?
So, do we need to use two For Each Loop Container components for this purpose. One for Files and one for tables within those files.
or just make a different dataflow task for each individual excel file.
Maybe Ricardo can help here by telling how he did it Or Anybody is welcome to put their views forward.
Thanks.
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 310,
Visits: 482
|
|
| I would use the SQL Command from Variable option on the Excel data source - the standard query is Select ... From ['Sheet1$'] so you could use a script component as the first item in the for each loop to set your variable up with the required select statement depending on the file name in the loop variable and then follow the script with a data flow to use the variable as the source sql command.
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Thursday, March 11, 2010 10:13 PM
Points: 223,
Visits: 283
|
|
Hi P. Jones,
I had three large Excel files to import. All Excel files had only one table but different names. For Each Loop Container was failing. So, I used UNION ALL task after setting up Excel Source task for each file and it worked just fine.
I got all the data into sql table but it was not in order. Like few hundred records from first file then few hundred from second and then third and so on so forth. I also used a derived column task with a variable as expression which could be the reason behind disorder but it didn't matter as long as I had the complete data. 
Thanks.
|
|
|
|
