How to open an existing Excel file from SSIS
-
Hi,
I thought I'd use Script Task and use VB .Net to open an excel workbook but for some strange reason there's no COM tab in the Add Reference box, so I can't add the Excel Object reference to the script.
I'm new to SSIS, so any help would be appreciated.
I don't mind what method is used as long as I can simply open an Excel file.
Thanks
-
skids (6/10/2009)
Hi,I thought I'd use Script Task and use VB .Net to open an excel workbook but for some strange reason there's no COM tab in the Add Reference box, so I can't add the Excel Object reference to the script.
I'm new to SSIS, so any help would be appreciated.
I don't mind what method is used as long as I can simply open an Excel file.
Thanks
This is complicated to accomplish and not recommended. Is there a reason why you cannot use the Jet provider to read the Excel data?
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
CozyRoc (6/11/2009)
This is complicated to accomplish and not recommended. Is there a reason why you cannot use the Jet provider to read the Excel data?
Rather than read excel data, I need to transfer data from SQL Server to Excel.
What I really need to do is
1. grab data from SQL Server and place it into several spreadsheets in an excel workbook.
2. Format the data (color, filter, width, titles etc)
3. Save the file as [Date].xls
3. Email the file somewhere
---
So what I thought I'd do was have macros in Excel do all the data extraction, formatting etc save as some file and place the main macro in the Workbook_Open() module and have SSIS simply open the workbook and email file.
This might not be the best solution but I thought would be one of the easier ones if I could simply get SSIS to open the excel... but if you say it is complicated then I'm not sure anymore. ?
And from what I've seen, no one know hows to run a macro from Excel within SSIS neither. :
-
The recommended solution is to setup template excel workbook with the required macros, worksheets inside and then use the Jet provider to populate specific portions with data. In this way you will avoid your exposure to Excel automation (which is unpredictable and unstable) and you wouldn't need to install Office at all on your server.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
CozyRoc (6/11/2009)
The recommended solution is to setup template excel workbook with the required macros, worksheets inside and then use the Jet provider to populate specific portions with data. In this way you will avoid your exposure to Excel automation (which is unpredictable and unstable) and you wouldn't need to install Office at all on your server.I forgot to mention, this is a scheduled job and needs to be fully automated where files will most likely end up being saved on the server... that or a mapped drive connected to the server.
Unfortunately I'm still a newbie! So I'm not sure I understand what you've suggested.
If I have a template workbook with the macros how will they run? And when you say Jet provider, are you simply referring to the Excel Connection Manager in SSIS? Sorry, I only just started with all this when I needed to do what I mentioned above...
-
I'm sorry. I didn't understand properly. So if you want to use macros to do formatting of the data after you insert then this will obviously not work. And yes, the Excel connection manager is in fact based on the same old Jet provider.
If you purchase a third-party Excel manipulation library, you will have more control. It will give you ability to do formatting and be generally more stable.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply