SSIS Script Task not Executing in SQL job sstep

Question

I have struggled and struggled with this issue. I've read a many similar questions concerning this topic, but I'm new at this and I'm reading "Greek to me". Perhaps someone will be willing to help me on an elementary level with this. I created an SSIS package with several steps. The first step is a script task that uses a VB8 script to pull the data from the SFTP server. The script reads like this:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    Public Sub HealthSmart()

        Dim Path As String, Path2 As String ', fileName As String, fo As Object, x As Object, y As Object, z As Object, rs As Object

        Path = "\\RENYSQL1\share\Health_Smart\Received_Files\Test"
        Path2 = "\\RENYMstr1\shared\CLIENT FOLDERS\HEALTHSMART\Original_PDF_Files\Test"

        Shell("""C:\Program Files (x86)\Ipswitch\WS_FTP 12\wsftppro.exe"" -s ""Health_Smart:/Test/AMC_Test/*.pdf"" -d ""local:" & Path & "\"" -quiet", vbMaximizedFocus)
        Shell("""C:\Program Files (x86)\Ipswitch\WS_FTP 12\wsftppro.exe"" -s ""Health_Smart:/Test/AMC_Test/*.pdf"" -d ""local:" & Path2 & "\"" -quiet", vbMaximizedFocus)

        Dts.TaskResult = ScriptResults.Success
    End Sub

End Class

It runs perfectly fine when the step is executed in the SSIS package. It also runs without error when I import the SSIS package into MSDB and select run package. But when I scedule it as a step in a job, it acts as if it ran perfectly. But the first step pulled not data.

I have tried setting the permissions of the SSIS package to a user that has full permissions to everything. I also tried setting the package protection level to DontSaveSensitive with no password.

On the SQL job side, I have tried using Windows authentication in the job as well as the login I mentioned earlier to run the package. Neither worked. Furthermore, I changed it to run on 32 bit - but to no avail.

I understand that the SQL job is run by the SQL agent. Does the agent have a specific login? People in my department say no.

The strange thing is that when I schedule the job to run, it runs and says it is successful. But the first step of getting the SFTP files with the above script runs, but does not pull any data. The Job Activity Monitor says it was Successful.

I have no idea what to do next. Be easy on me. I'm new at this.

Answer 1

While you certainly could have permissions issues if you are not receiving and error it would suggest the code is not executing. Looking at where you placed your code in your script I am thinking it is entirely possible that you did not put a call to your SUB in the Main () Sub. When creating a new script you should see something like:

Public Sub Main()
    '
    ' Add your code here
    '
    Dts.TaskResult = ScriptResults.Success
End Sub

And basically where it says add your code here is where Microsoft was leading you to add your script. It is completely okay to do it in its own sub as you have, but if you do you need to call your sub within the Main() SUB like so:

Public Sub Main()
    '
    ' Add your code here
    '
    HealthSmart()

    Dts.TaskResult = ScriptResults.Success
End Sub

If you actually do have the Main SUB and calling your code. There could perhaps be an error within wsftppro.exe. Have you run the code outside of the script?

If permissions are causing the issue within the exe and not error you could try running via your dev environment as a user that has access to the locations and if it succeeds for you then you could need permissions for your SQL Agent and/or Service Account.