February 12, 2010 at 7:23 am
I built a VB Script to open an Access data base and import CSV files. The package Script task has been tested and run perferctly in Debug mode. When I put the package in SQL Server Agent all the steps perform perfectly but the Script task.
I know that the Scrpt task does open Access because it Creates a lock on the Access database. So I know that part of the script is working. Through further testing it hangs on the first docmd and never executes it.
The Below code I got from codeproject.com, which got me started and allowed me to build a Scrpt task with 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[\b]
Since the package has already been debugged and working it should also work in SQL Server Agent so I can schedule the task.
I have been trying to get this to work for the past 3 days trying different things the code always works in visual studio where the package is built. but has never worked being executed by SQL Server Agent.
Any help would be greatly appreciated.
February 12, 2010 at 8:29 am
It could possibly be a permission issue, does your account that runs the SQL agent have the correct permission to access the MS Access database?
Also do your SQL agents logs give any useful errors?
February 12, 2010 at 8:35 am
Where would I find those log files that I can read. They might
February 12, 2010 at 8:42 am
in SSMS
expand SQL server agent --> then expand jobs
Find the job that you created to run the package and right-click and select View History
February 12, 2010 at 8:55 am
Sorry, yes I know how to read the history. Remember the process hangs so I do not get an error message it is a 5 minute process and I let it run for 2 hours and still not error.
I am confused that it works fine in visual studio but when put in sql agent it never finishes. Actually it barley starts. It only gets to the point of opening the access database.
February 12, 2010 at 9:09 am
okay, you could check the windows system logs to see if there is anything there also..
Have you checked the account that the agent is running under has permissions to access MS acess?
Is the access db on the same domain as your sql server?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply