Hi Does any one know how to execute Macros in SSIS Package. I have an Access 2003 Macro which has to be executed in a SSIS package... I need to pass some runtime parameter also . How to go about it.... Thanks & Regards Deepu M.I

Deepu.MI wrote:Hi Does any one know how to execute Macros in SSIS Package. I have an Access 2003 Macro which has to be executed in a SSIS package... I need to pass some runtime parameter also . How to go about it.... Thanks & Regards Deepu M.I

Hi 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 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 Note : Change the Security Level for Access Macro... otherwise when ever the SSIS packageexecute it should popup a Security Warning Message... To avoid this ,,, follow the instruction Open Access -- > Tools --> Macro --> Security Click the Security and Changed to Low Level... Thanks & Regards Deepu M.I

I get errors when I try to compile the script as follows: 1 Typr 'Access.Application' is not defined 2 Type 'Access.ApplicationClass' is not defined The imports Microsoft.Office.Interop.Access seemd to be ok with no error for that line, what going on here??? HELP???

If it works for others and not for you then you have overlooked something. Typically, the reference is still not set or the Imports Microsoft.Office.Interop.Access is missing Arthur My Blog

Solved this by add more Imports that took care of it, but I now get a runtime error as follows: 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() Can anybody help on this, it's been a week or more I have been working on this... Current code************************ 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