June 14, 2009 at 2:45 am
"We need to gather data from two Oracle database, two SQL 2005 database, and about eight Excel workbooks into a single SQL database. This process will be scheduled to run each evening.
Many of the Excel Workbooks have macros that consolidate the data from other workbooks as well as process the data. Some of these macros use certain third party controls that are only designed for Excel and at this time can only be run in Excel.
We are attempting to build an automated processes that handles this with no (or minimum) user interaction.
"
Eric
What is your final goal with the data in one database? Will you create reports with combination of data (joins) from
different sources? May be you need to create a "small" datawarehous
I would use an approach indicated by Jef and Jim. I use Excel VBA to do everything that you want to do as you say it.
With OLE DB , ADO you can connect to remote servers and use SQL. You can loop through an Excelsheet to insert data
into a database or export the data to a TAB separated text file an then use bulk insert.
Please let us know how you will continue and I do have some code which might help you.
/Gosta
June 14, 2009 at 12:14 pm
I've just seen another thread (http://www.sqlservercentral.com/Forums/FindPost734494.aspx) that included the following link: http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/ .
This link might help you to get your data from Excel.
Regarding the macro issue:
I don't know how complicated those macros are and if they are the same across all worksheets, but maybe rewriting the macros using T-SQL would be an option as well...
June 14, 2009 at 6:44 pm
I think the problem here is scheduling it to run.
I've written macros within excel workbooks to grab data and format and etc but this requires the user to click on a button to execute the macro. Whereas I think what we want is to automate the process with no human interaction.
And with my problem, I also need to email the file.
I don't want to use windows task scheduler as I'll need to set it up on the server machine and was hoping SSIS could handle it instead.
Anyway I think I'll try and see if Script Task and do something about it 🙂
June 15, 2009 at 2:50 am
It is easy to automate an Excel macro.
Put the macro (call to) in the sub "Private sub open Workbook_open"
Set securitylevel so the nacro will run without user interference.
Scedule the excelfile to run then you want it.
Sending mail from VBA. Serach for something like "mail vba excel" and you get 3 170 000 answers.
Iuse CDO take a look at http://www.rondebruin.nl/cdo.htm.
//Gosta
June 29, 2009 at 4:37 am
Sorry for the bad timing of this reply, as you can see, I'm not a frequent flyer here, but I thought I'd clear this up for any future viewers of this thread.
It is absolutley possible to run macro's in an excel workbook from a VB task in SSIS. The examples I'm using have been done with office 2003.
I would like to say that this isn't the best way to go about this problem, it is much more
preferable to convert any macro's within the worksheet to VB.NET code run from SSIS. This is a
legacy process that I've not been allowed to change, so I've just automated it instead.
First you will need the interop assemblies for office which you can get from
here.
These must be downloaded and installed on the machine where SSIS will be run from, (note the
same machine will also need excel installed locally). The assemblies will be put into your GAC
(C:\windows\assembly\gac).
To make the exel assembly available in SSIS, you will need to copy the dll from the global
assembly cache folder which will be called something like microsoft.office.interop.excel to
C:\windows\microsoft.net\framework\v2.0.50727
You will also need the VBE interop assemblies if you plan to change any of the macro's, I can't
remember if these were already available in the SSIS assemblies, but if not you will need to get
this from the GAC (Microsoft.VBE.Interop). Same procedure as above.
Restart all instances of Visual Studio and it should be ready to roll so add the two references you
installed to a script task.
You will also need to change security settings in excel by going to
"Tools->Macro->Security->Trusted Sources tab", make sure "Trust access to Visual Basic Project"
is checked.
This is the code I use to format an xls data extract. The package populates the workbook with
data, the code will then save this book to a new name, run a macro, delete the modules
containing the macro code, then runs a macro in the template to clear down for the next run.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Public Class ScriptMain
Public Sub Main()
Dim objexcel As New Microsoft.Office.Interop.Excel.Application
objexcel.Visible = False
objexcel.UserControl = False
objexcel.DisplayAlerts = False
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim range As Microsoft.Office.Interop.Excel.Range
Dim cell As Microsoft.Office.Interop.Excel.Range
Dim ws As Microsoft.Office.Interop.Excel.Worksheet
Dim macro As Microsoft.Vbe.Interop.VBComponent
Dim vars As Variables
Dim startcell As String
Dim i As Integer
Try
/*Open workbook and assign to a workbook object. This case opens a template and saves to a new file.*/
Dim FileName As String = "c:\Pathtofile.xls"
wb = objexcel.Workbooks.Open(FileName)
wb.SaveAs("c:\PathToFile_" + DateStr() + ".xls")
ws = CType(wb.Sheets(1), Worksheet)
/*Run Macros and delete modules*/
objexcel.Run("Remove_Invalid_Dates")
macro = objexcel.VBE.VBProjects.Item(1).VBComponents.Item("Module1")
wb.VBProject.VBComponents.Remove(macro)
macro = objexcel.VBE.VBProjects.Item(1).VBComponents.Item("Module2")
wb.VBProject.VBComponents.Remove(macro)
/*Change some formatting, and add a date to a cell*/
range = CType(ws.Range("C2"), Range)
range.Value = Left(Now.ToString, 10)
range = CType(ws.Range("C3"), Range)
range.Value = Left(DateAdd(DateInterval.Day, -7, Now).ToString, 10) + " - " + Left(DateAdd(DateInterval.Day, -1, Now).ToString, 10)
wb.Save()
/*Open template file and clear down with a macro saved in the worksheet.*/
wb = objexcel.Workbooks.Open(FileName)
objexcel.Run("Clear_sheet")
Catch ex As Exception
MsgBox(ex.Message)
close_excel(range, ws, wb, objexcel)
Throw
End Try
close_excel(range, ws, wb, objexcel) 'Sub to dispose the excel objects
Dts.TaskResult = Dts.Results.Success
End Sub
The close_excel sub routine looks something like this:
Private Sub close_excel(ByVal range As Microsoft.Office.Interop.Excel.Range, ByVal sheet As Microsoft.Office.Interop.Excel.Worksheet, ByVal obook As Microsoft.Office.Interop.Excel.Workbook, ByVal oExcel As Microsoft.Office.Interop.Excel.Application)
/*Clean-up: Save and close excel interop objects and quit.*/
If range IsNot Nothing Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(range)
range = Nothing
End If
If sheet IsNot Nothing Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet)
sheet = Nothing
End If
If obook IsNot Nothing Then
obook.Save()
obook.Close(False)
System.Runtime.InteropServices.Marshal.ReleaseComObject(obook)
obook = Nothing
End If
If oExcel IsNot Nothing Then
oExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
oExcel = Nothing
End If
System.GC.Collect()
End Sub
The code's a bit rough and ready, and I'm sure some people will see ways to improve it, however this should work for you.
I hope this infromation is helpful for someone.
September 15, 2009 at 9:15 am
Here is a simple way to run a excel macro in a SSIS package using ActiveX Script task but will only work on SQl server 2005 (ActiveX script is not supported in 2008)
Change path
Make sure your macroName is the macroname of the macro in the workbook
On the activeX module click script and EntryMethod must equal function name here it is "Main"
Function Main()
dim ExcelObject
dim MacroName
const InPath = "\\computername\directory\directory\Dashboard_LD.xls"
MacroName = "ClearSheet"
Set ExcelObject=Createobject("Excel.application")
ExcelObject.Workbooks.Open InPath
ExcelObject.Run (MacroName)
ExcelObject.Workbooks(1).Save
ExcelObject.Workbooks(1).Close
ExcelObject.Application.Quit
Set ExcelObject=nothing
'Main = DTSTaskExecResult_Success
End Function
September 17, 2009 at 9:36 am
Hey,
I have recently implemented a similar SSIS package and have found out a very easy and effective way of running the VBA code from SSIS.
You can certainly built on .Net stuff but thats not my area of expertise so I can comment on what you were doing above.
My approach:
1. take a execute command task and point it to Open the excel workbook which has your VBA code.
2. call the VBA function that has your code in the workbook open event.
3. as soon as you execute the task it will open the excel file and your code will automatically get executed.
Hope this Help.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply