Load Excel Dynamically
-
hi all,
I have 4 excel files which are to be loaded in a table.
The data flow task when run 4 times to load the excel sheets, works fine.
But when I place it in the Foreach loop container, the loading is successful for one file only.
Reason being that the 4 excel files have different name for work books.
For example, June09.xls has worksheet - June09, July09.xls has worksheet July09.
Is the possible to assign the values of the workbook dynamically ?
Thanks in advance.
-
touchmeknot123 (8/11/2009)
hi all,I have 4 excel files which are to be loaded in a table.
The data flow task when run 4 times to load the excel sheets, works fine.
But when I place it in the Foreach loop container, the loading is successful for one file only.
Reason being that the 4 excel files have different name for work books.
For example, June09.xls has worksheet - June09, July09.xls has worksheet July09.
Is the possible to assign the values of the workbook dynamically ?
Thanks in advance.
You have to do the following:
1. Setup a variable, which depends on the current for each iteration and will contain SQL statement to select data from the excel worksheet. Something similar to that:
SELECT COL1, COL2, COL3 FROM $@[User::WorksheetName]
2. In the data flow for your Excel Source choose to use "SQL command from variable" and then select the variable you have setup on the first step.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Thank you very much! I got it!
-
CozyRoc,
another quick question regarding the same.
I could do it because the woskheet names were extracted from filenames.
How to solve the same issue where the filenames and worksheets aren't related ?
example :
June09.xls , Sheet4
July09.xls , Sheet2
-
touchmeknot123 (8/11/2009)
CozyRoc,another quick question regarding the same.
I could do it because the woskheet names were extracted from filenames.
How to solve the same issue where the filenames and worksheets aren't related ?
example :
June09.xls , Sheet4
July09.xls , Sheet2
You have to setup mapping somewhere that June09.xls maps to Sheet4. You must most probably create custom script task for this.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Or, if you can be bothered setting things up so that you have access to the Excel object model via script (a bit of a pain), you can get the sheet names in code:
myExcel =CreateObject("Excel.Application")myWorkBook=myExcel.WorkBook.Open("my Excel File")
Dim tWorkSheet as Excel.Worksheet
For Each tWorkSheet In myWorkBook.Worksheets
myTableName(i) = tWorkSheet.Name
Next tWorkSheet
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 -
Thanks Phil.
I am trying it out your way.
I am having trouble since the dll - 'Microsoft.Office.Interop.Excel' is missing.
I have installed the dll but I do not know how to add it to the project.
The 'Add Reference' in the project doesn't have any browse button 🙁
I have no idea since I am new to .NET.
Please help.
-
touchmeknot123 (8/14/2009)
Thanks Phil.I am trying it out your way.
I am having trouble since the dll - 'Microsoft.Office.Interop.Excel' is missing.
I have installed the dll but I do not know how to add it to the project.
The 'Add Reference' in the project doesn't have any browse button 🙁
I have no idea since I am new to .NET.
Please help.
If you are not experienced with .NET I would recommend not to use this approach. In general automation of Excel is quite troublesome and requires too many additional installed components / permissions. Try the other approaches.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
I can learn it if I don't know it with your help 🙂
Other appraoches ?? Can you suggest, how else can it be done ?
-
You can also use the OleDB GetSchema() method. I can't remember the exact syntax, but a search of msdn should help.
This allows you to query the excel catalog information without the need for Excel references...
HTH
Kindest Regards,
Frank Bazan
-
touchmeknot123 (8/14/2009)
I can learn it if I don't know it with your help 🙂Other appraoches ?? Can you suggest, how else can it be done ?
It will take a couple of years ot learn 😉 And even this will not help with you with the troubles of Excel automation.
Now getting back to the other approach. If you are able to setup mapping between file name and sheet name then I would recommend you stick to this approach.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Try reading this thread and you'll get an idea of the complexities involved:
http://www.sqlservercentral.com/Forums/Topic531295-148-2.aspx#bm734565
I won't be able to help any further though - I haven't tried it (don't really want Excel installed on my SSIS servers).
If you decide to push ahead with it, good luck to you.
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 -
Frank Bazan (8/14/2009)
You can also use the OleDB GetSchema() method. I can't remember the exact syntax, but a search of msdn should help.This allows you to query the excel catalog information without the need for Excel references...
HTH
Yes, this is exactly what I was thinking, too. You can check this script for ideas how to get the list of available worksheets using the OLEDB Schema method.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Oh that is a much tidier idea, try that first! Nice one.
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply