CODEWITHSUNDEEP
c#ssissql-server-2016sql-server-agent
Nathan TSQL_Jr
Nathan TSQL_Jr

Reputation: 117

SQL job does not open cmd

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.

Upvotes: 1

Views: 333

Answers (1)

billinkc
billinkc

Reputation: 61231

@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

Upvotes: 1

Related Questions