I found this and have done everything it says to do, but I can't get the script to compile. Any ideas on how to run a access macro in SSIS?
Baiscally to execute an Access Macros in SSIS package we need to Download
Microsoft.Office.Interop.Access DLL from Office XP PIAs.
Download site
http://www.microsoft.com/downloads/details.aspx?FamilyId=C41BD61E-3060-4F71-A6B4-01FEBA508E52&displaylang=en
1) Extract the Microsoft.Office.Interop.Access DLL from Oxppia.exe
2) Drag and Drop Microsoft.Office.Interop.Access DLL to Global Assembley Directory(GAC) ie: C:\WINNT\assembly for Windows 2000 -- C:\WINDOWS\assembly for ( Win Xp and Win 2003)
3) Copy paste Microsoft.Office.Interop.Access to C:\WINNT\Microsoft.NET\Framework\v2.0.50727 for Windows 2000 -- C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 ( Win Xp and Win 2003)
4) Add DLL reference in the Script Task
5) Add the below Code
1) Create a New Project in SSIS
2) Drag and Drop Script Task
3) Copy Paste the code in script task editor
Imports Microsoft.Office.Interop.Access
Try
Dim objAccess As New Access.Application
objAccess.OpenCurrentDatabase("D:\TestMacro.mdb", False) ' Add the Access File Path
objAccess.DoCmd.RunMacro("Macro1") ' Replace Macro1 with the name of your macro
objAccess.CloseCurrentDatabase()
objAccess.Quit(Access.AcQuitOption.acQuitSaveNone)
objAccess = Nothing
Catch ex As Exception
System.Windows.Forms.MessageBox.Show(ex.ToString())
End Try
Dts.TaskResult = Dts.Results.Success
I actually figured this out. Had to reference alot more than just the access dll. Below is the final code to get it to work if anyone else wants to do something like this.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Access
Imports ADODB
Imports dao
Imports mscomctl
Imports msdatasrc
Imports stdole
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.OWC
PublicClass ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
PublicSub Main()
'
Try
Dim objAccess AsNew Access.Application
objAccess.OpenCurrentDatabase("D:MyDB.mdb", False) ' Add the Access File Path
objAccess.DoCmd.RunMacro("Macro1") 'Add your Macro name
objAccess.CloseCurrentDatabase()
objAccess.Quit(Access.AcQuitOption.acQuitSaveNone)
objAccess = Nothing
Catch ex As Exception
System.Windows.Forms.MessageBox.Show(ex.ToString())
EndTry
Dts.TaskResult = Dts.Results.Success
EndSub
EndClass
