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.AccessTryDim objAccess As New Access.ApplicationobjAccess.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 = NothingCatch ex As ExceptionSystem.Windows.Forms.MessageBox.Show(ex.ToString())End TryDts.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 Public Class 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. Public Sub Main() ' Try Dim objAccess As New 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()) End Try Dts.TaskResult = Dts.Results.Success End Sub End Class

Hi Mike: Are running Access 2002 or 2003. I am running 2003 and I get an error that says it cannot load file or assembly Microsoft.Office.Interop.Access. I dont have a c:\Windows directory on my machine but have a c:\WINNT folder which is where I dropped the dll into. Any ideas?

Hi, I had the same issue with office 2003. Installing office 2003 on the server resolved it. Hope this helps. Vijay

I get an error when running the script on the objAccess.DoCmd.RunMacro("macro_name") The error is: Sysytem.Runtime.InteropServices.COMException (0x800A09C5): The Run Macro action was canceled. at Microsoft.Office.Interop.Access.DoCmd.RunMacro(Object MacroName, Object RepeatCount, Object RepeatExpression) at ST_f9c79a2b3f9e4c6b86d4210c2e6d006c.vbproj.ScriptMain.Main() What am I doing wrong????? Code Begin::::::: 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 <System.AddIn.AddIn( "ScriptMain", Version:="1.0", Publisher:="", Description:="")> _ <System.CLSCompliantAttribute( False)> _ Partial Public Class ScriptMain Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase Enum ScriptResults Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure End Enum ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts property. Connections, variables, events, ' and logging features are available as members of the Dts property as shown in the following examples. ' ' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value ' To post a log entry, call Dts.Log("This is my log text", 999, Nothing) ' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True) ' ' To use the connections collection use something like the following: ' ConnectionManager cm = Dts.Connections.Add("OLEDB") ' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;" ' ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Help, press F1. Public Sub Main() ' Try Dim objAccess As New Access.Application objAccess.OpenCurrentDatabase( "Z:\report_test_i3_dialer.mdb", False) ' Add the Access File Path objAccess.DoCmd.RunMacro( "Test_SSIS_Macro") 'Add your Macro name objAccess.CloseCurrentDatabase() objAccess.Quit(Access.AcQuitOption.acQuitSaveNone) objAccess = Nothing Catch ex As Exception System.Windows.Forms.MessageBox.Show(ex.ToString()) End Try ' Add your code here ' Dts.TaskResult = ScriptResults.Success End Sub End Class

I have added the following Imports statement which did not help, still get the same error: Imports System.Runtime.InteropServices.COMException

You have probably overlooked something, or may be having some versioning problems. I suggest you start a new post, this way you will be able to attract more people because the new question will be in un-answered status. If you do proceed with the new post please be as specific as it makes sense listing all your steps.Arthur My Blog