14 Replies Latest reply: May 15, 2014 10:09 AM by Valerie _ RSS

    Need some advice regarding automation...

    Valerie _

      Hi all,

       

      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

      Imports System

      Imports System.Data

      Imports System.Math

      Imports Microsoft.SqlServer.Dts.Runtime

       

       

      Public Class ScriptMain

       

          Public Sub Main()

       

              Dim MonarchObj As Object

       

              MonarchObj = GetObject("", "Monarch32")

       

              If MonarchObj Is Nothing Then

                   MonarchObj = CreateObject("Monarch32")

              End If

       

              MonarchObj.Visible = False

       

              openfile = MonarchObj.SetReportFile("Y:\ifchg2", False)

              openfile = MonarchObj.SetReportFile("
      domain\dfs\MISData\Unprocessed\ifchg2", False)

       

              If openfile = True Then

                  openmod = MonarchObj.SetModelFile("
      domain\dfs\mis\analyst\monarchIO\Models\ifchg2.xmod")

       

                  If openmod = True Then

                      MonarchObj.ExportTable("
      domain\dfs\MISData\Unprocessed\ifchg.txt")

                  End If

              End If

       

              MonarchObj.CloseAllDocuments()

       

              MonarchObj.Exit()

       

              Dts.TaskResult = Dts.Results.Success

          End Sub

       

      End Class

      /code

       

      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:

       

      "
      domain\dfs\Datastore\monarchv10\program\Netexec.exe" /rpt:"C:\temp\ifchg2" /mod:"
      domain\dfs\mis\analyst\monarchIO\Models\ifchg2.xmod" /exp:"C:\temp\ifchg2.txt" /T[/code]

       

      (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?

       

       

      TIA,

      Valerie

        • Need some advice regarding automation...
          Data Kruncher

          Hello and welcome to the forum Valerie.

           

          Now I have zero experience with your environment, so I should probably keep away from this one, but it looks to me that your problem is possibly similar to:

          [URL="http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/80b7b4de-9408-426a-a869-fa34b2301545/"][/URL][URL]http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/80b7b4de-9408-426a-a869-fa34b2301545/[/URL]

           

          which include a recommendation to follow direction found here:

          http://support.microsoft.com/kb/912911[/URL]

           

          Any help at all?

            • Need some advice regarding automation...
              elginreigner _

              I wish I could help more, but I'm still on SQL 2000. Not sure if this affects your version (2005 I think) but when I specify a DTS package, I also need to specify a username/password of a SQL user authorized to run the package. But, I am running my automation from batch files.

                • Need some advice regarding automation...
                  Valerie _

                  I wish I could help more, but I'm still on SQL 2000. Not sure if this affects your version (2005 I think) but when I specify a DTS package, I also need to specify a username/password of a SQL user authorized to run the package. But, I am running my automation from batch files.[/QUOTE]

                   

                  Thanks for your reply!

                   

                  Yes, I am using SQL Server 2005 for this, and as with SQL2K/DTS, I have to specify the account under which I want the package to run.  I have verified that the package is running under that account when it is scheduled, and I have verified that I can run it manually with success.  I feel like there is some SQL Agent-related setting that I am missing (e.g. permissions on the COM object) but so far I have not found it and am running out of things to try.

                   

                  So when you say you run your automation from batch files, are you calling the batch files from your DTS packages, or are they scheduled to run in a stand-alone job/task?

                    • Need some advice regarding automation...
                      Data Kruncher

                      It's all Martian to me.

                       

                      Hopefully you'll get it worked out soon.

                        • Need some advice regarding automation...
                          Valerie _

                          It's all Martian to me.

                           

                          Hopefully you'll get it worked out soon.[/QUOTE]

                           

                          Hee hee - everything about this feels like Martian to me too, really.    I'm just taking (not-so-good) guesses at everything and obviously it's not working out so well.

                           

                          Thank you, I hope so, too.  This is driving me crazy!

                           

                          Do you know if other people have had success using SSIS to automate with Monarch?  (I have seen that the Datapump product has good integration with SSIS, but unfortunately I don't have that and am not in a position to demand it, so I'm kind of stuck with what I've got for now... )

                            • Need some advice regarding automation...
                              Data Kruncher

                              No, I don't know of anyone automating Monarch in this way.

                               

                              I'd have expected that automating by executing Monarch batch file would be easier to handle, but apparently not. Then again, as mentioned, I have no experience with the environment.

                              • Need some advice regarding automation...
                                Valerie _

                                I finally figured this one out!  In order to get the SSIS package to run via SQL Agent, I had to set the "InteractiveMode" property on the package to "False" so that when it ran it wouldn't try to actually launch a Monarch window. 

                                 

                                I set this property in BIDS by right-clicking on the package in Design view, going to Properties->Expressions, selecting the "InteractiveMode" property from the dropdown, and entering "False" (no quotes) for the Expression value.

                                 

                                Then to call Monarch, I just added a simple Script task to my control flow and used the VB.NET Shell() function to execute the program and pass the different variables to it (/rpt, /mod, etc.)   Here is what my final script looks like, though I should add a disclaimer that I have little knowledge of Visual Basic and can't claim that this is the most elegant solution. 

                                 

                                Option Strict Off

                                Imports System

                                Imports System.Data

                                Imports System.Math

                                Imports Microsoft.SqlServer.Dts.Runtime

                                Imports Microsoft.VisualBasic.Interaction

                                Imports System.IO

                                 

                                Public Class ScriptMain

                                 

                                    Public Sub Main()

                                 

                                        Dim i As Integer

                                        Dim rpt As String

                                        Dim unproc As String

                                 

                                        unproc = "
                                domain\dfs\MISData\Unprocessed\ifchg"

                                 

                                        For i = 1 To 2

                                 

                                            rpt = unproc + CStr(i)

                                 

                                            Shell("""
                                domain\dfs\Datastore\monarchv10\program\Netexec.exe"" /rpt:""" + rpt + """ /mod:""
                                domain\dfs\mis\analyst\monarchIO\Models\ifchg2.xmod"" /exp:""" + rpt + ".txt"" /T", AppWinStyle.Hide, True, 100000)

                                 

                                            File.Move(rpt, "
                                domain\dfs\Public\BusinessOffice\Epic Charges\IF Reports\Archive\ifchg" + CStr(i) + "." + CStr(Now.Year) + CStr(Now.Month) + CStr(Now.Day))

                                 

                                        Next i

                                 

                                        Dts.TaskResult = Dts.Results.Success

                                    End Sub

                                 

                                End Class

                                /code

                                 

                                Hope this helps someone, somewhere along the way...

                                  • Need some advice regarding automation...
                                    Grant Perkins

                                    Hope this helps someone, somewhere along the way...[/quote]

                                     

                                    Hi Valerie,

                                     

                                    Can I just say thanks for taking the trouble to give this feedback for those who may follow here. Not everyone does that for what may be a 'one off' visit to the forum when using a technology combination (if I may phrase it that way) that may not (yet) be in common use amongst Monarch forum users.

                                     

                                    I think people here are always grateful to have the knowledge added to the forum records  - it may well save someone a lot of time at some point down the line.

                                     

                                    My automation activity has been limited to a few batch scripts and I don't 'do code' so I am always impressed by those who do and are prepared to share their knowledge and discoveries with others. Especially when I can sort of understand how it was done!

                                     

                                     

                                    Grant

                                      • Need some advice regarding automation...
                                        Valerie _

                                        Can I just say thanks for taking the trouble to give this feedback for those who may follow here. Not everyone does that for what may be a 'one off' visit to the forum when using a technology combination (if I may phrase it that way) that may not (yet) be in common use amongst Monarch forum users.[/QUOTE]

                                         

                                        Hi Grant,

                                         

                                        You're very welcome for the post.  In trying to get my solution working, I encountered many posts on various forums where the OP either abandoned the thread or just posted a, "Hey it works!" without further explanation...and that was so frustrating.  Given that, I vowed not to be one of those OPs and sincerely hope that someone will glean something useful from my solution.

                                         

                                        Glad you were able to follow my solution and that it made some kind of sense.    I am not much of a coder myself, but I was able to cobble this together and if you are trying to use SSIS for your automation, it is easy to implement in this way, even if you don't have advanced coding skills. 

                                         

                                        Anyway, thanks for the comment. 

                                         

                                        Valerie

                                        • Need some advice regarding automation...
                                          elginreigner _

                                          Valerie, I run my DTS from the batch files, via a schedule task on a file server. I use this command:

                                           

                                          DTSRUN /S SERVERNAME /U USERNAME /P PASSWORD /N PACKAGENAME

                                            • Need some advice regarding automation...
                                              joey

                                              Valerie, one thing you may find helpful if you find yourself using Monarch a lot in SSIS is Data Pump Pro.  It has built-in integration with SSIS, and plenty of other great features. I can't tell you much about this as we  use the standard version here.

                                                • Need some advice regarding automation...
                                                  Valerie _

                                                  Valerie, one thing you may find helpful if you find yourself using Monarch a lot in SSIS is Data Pump Pro.  It has built-in integration with SSIS, and plenty of other great features. I can't tell you much about this as we  use the standard version here.[/QUOTE]

                                                   

                                                  Hi Joey,

                                                   

                                                  Yes, I have read/heard that Data Pump Pro plays quite nicely with SSIS.    Unfortunately, I do not have the sway at my organization to catalyze an upgrade to that product, but I found myself wishing for it a lot over the past couple of weeks.    If we do end up doing a lot of automation with Monarch, I will keep it in mind and see if I can get it into our environment.

                                                   

                                                  Thanks for the tip!

                                                   

                                                  - Valerie

                                                • Need some advice regarding automation...
                                                  Valerie _

                                                  Valerie, I run my DTS from the batch files, via a schedule task on a file server. I use this command:

                                                   

                                                  DTSRUN /S SERVERNAME /U USERNAME /P PASSWORD /N PACKAGENAME[/QUOTE]

                                                   

                                                  Ah, okay.  I was trying to do the opposite - call a batch file (to run Monarch) from my SSIS package.    But, as I posted above, I got it working another way...

                                                   

                                                  Thank you for the reply!

                                    • Need some advice regarding automation...
                                      Valerie _

                                      Thanks for the reply, Data Kruncher.

                                       

                                      Actually, the way that I have my SQL Agent job set up is how they recommend in those links you cited: I am calling my SSIS package from the job step, and the job step runs under a Proxy that is linked to a Credential with all the correct permissions, yadda yadda.    I can run the package manually when logged in to my server as the user set up to run the SQL job, but when I try to execute the package from the job itself, it does not run correctly.  Judging from what I have seen on Google, all of that setup seems to be the cause for about 98% of the instances in which the "Cannot create ActiveX component" error is received.  How lucky I am to be in the other 2%!  :rolleyes: