I am a Monarch newbie, and I am stuck! Basically, I am trying to figure out the best way to automate a process (with SSIS) that includes a call to Monarch (Pro 10.5). Unfortunately, the things I've tried so far have not been successful.
At first I tried the COM route, using an SSIS Script task to run a script similar to what is seen in the Programmer's Guide. I have never worked with COM objects before, so I am just following examples on this one, but I was able to create a script that worked perfectly when the package was executed manually (using DTEXEC or in BIDS). However, when I execute the package using a SQL Server Agent job, it throws the following exception at runtime: "Cannot create ActiveX component" and does not get past the GetObject() part of the script.
Here is the code I used in my Script task:
Option Strict Off
Public Class ScriptMain
Public Sub Main()
Dim MonarchObj As Object
MonarchObj = GetObject("", "Monarch32")
If MonarchObj Is Nothing Then
MonarchObj = CreateObject("Monarch32")
MonarchObj.Visible = False
openfile = MonarchObj.SetReportFile("Y:\ifchg2", False)
openfile = MonarchObj.SetReportFile("
If openfile = True Then
openmod = MonarchObj.SetModelFile("
If openmod = True Then
Dts.TaskResult = Dts.Results.Success
After troubleshooting the ActiveX error to no avail, I then tried to go the batch file route, using an Execute Process Task in the SSIS package to execute the .bat file. Again, this method works perfectly when I run the package or execute the .bat file manually, but as with the above case I run into issues with running it via SQL Server Agent. In this case, the SQL Agent hangs when the job is started and the Netexec.exe process just sits out in Task Manager without ever completing the script.
Here is what I have in my .bat file:
(Note: I realize the paths in the two examples are not the same, but they are valid in both cases. I'm just trying to get a proof of concept going pointing to local, easy-to-find files before I make that part more dynamic.)
I should mention that in both of the above cases, I have been able to successfully run the package manually, on the SQL Server where it exists, using the Service Account that SQL Agent runs under (and under which the SQL Agent job step is configured to run). So, yes, the permissions should be correct for the account being used to run this job. (Though I am open to the idea that there is some permissions setting somewhere that needs to be changed to get it to work under the SQL Agent... :rolleyes: )
I feel like I'm this close to having a workable, automated solution, but since I'm just making guesses at how all of this works (due to my inexperience/ignorance ) I am stuck spinning my wheels. Does anyone have any suggestions for me as to which route is the better option for automation, or for something I could try to perhaps get one of the two options above working?