1

I have an SSIS package which has a script task that executes a program via cmd promt.

The task runs fine when I execute the package from the integrated service catalog however when I run it as part of a sql server agent job the job reports successful but it does not do anything.

It’s almost a if the task does not execute at all, because when I run the package via the ssis catalog I get a cmd popup with a log of the what’s happening, where as when I run it via the job I don’t get this cmd pop up.

I’m suing sql server 2016 and the job is run with a proxy user account.

Nathan TSQL_Jr
  • 117
  • 1
  • 8
  • It does run? Maybe check permissions for the job to ensure the ID running the job has proper permissions, when you run it yourself your using your permissions, when the job runs it uses the agent account – Brad Feb 07 '19 at 15:31
  • 1
    What does the job's history show? You *won't* get any popups from jobs though. They run under different accounts, without a desktop. – Panagiotis Kanavos Feb 07 '19 at 15:32
  • @Brad I’m running the job with a proxy user so it should be running under the security context of the proxy account so it should be using the agent account – Nathan TSQL_Jr Feb 07 '19 at 16:11

1 Answers1

1

@PanagiotisKanavos has likely hit the nail on the head. Script Tasks have a Read Only Variable available to them System::InteractiveMode You can check the value of that Variable within a Script task to determine whether the process can communicate with the desktop aka "someone's at the wheel."

If you attempt to interact with the desktop from a job, the Script Task should throw an Error. For bonus points, an account can be marked in Active Directory as ... denied desktop or something like that which even if you wanted to run a job in some interactive mode, you won't be able to.

Within my Script Tasks, if I have the possibility of opening windows as you describe, I always wrap it in a logic block like

        string message = "Some diagnostic message";
        if ((bool)this.Dts.Variables["System::InteractiveMode"].Value)
        {
            MessageBox.Show(message);
        }

Sample of the code running for a different purpose is on my blog

http://billfellows.blogspot.com/2015/02/is-my-ssis-package-running-in-32-or-64.html

And likely duplicates

billinkc
  • 59,250
  • 9
  • 102
  • 159