1

I have a vbs script that's failing to finish loading a file before moving on to the next steps. I've been using this code for 2+ years so this is likely due to my poor code and error handling - If the SSIS package fails to fully load, the procedure should kick out and alert me - Can someone point me in the right direction to make sure this package fully loads or if it fails, truncate the stage table and try again?

The file can range from 50mb to 1.2Gb

'******************************
Sub ReloadTable(strTableName)
'******************************
    Dim wsh 
    Set wsh = CreateObject("WScript.Shell")

    Dim waitOnReturn 
    waitOnReturn = True

    Dim windowStyle
    windowStyle = 0

    Dim errorCode
    Dim DTEXECStatus


    'Truncate the stage table
    ExecOnSQL "TRUNCATE TABLE essstage." & strTableName

    'Run the SSIS package and wait until complete
        errorCode = wsh.Run("dtexec /File ""\\server.dev.local\Data\SSIS SQL16\" & strTableName & ".dtsx"" ", windowStyle, waitOnReturn)
        If errorCode = 0 Then
            DTEXECStatus = "Success! " & strTableName
            'MsgBox "Success! " & strTableName
        Else
            'Should exit the sub if this fails and notify me
            DTEXECStatus = "FAILED!! " & strTableName
            Create_NOTICE_Email
            MsgBox "Failed! " & strTableName
            'It'd be better if this repeated the steps to clear/attempt to reload again but who knows how to do that 
            Exit Sub
            'ML: Added this END to the script to see if it'll stop erroring out
        End If

End Sub

'******************************
Sub ExecOnSQL(cmdTxt)
'******************************
        Dim strConnSQL
        strConnSQL = "Provider=SQLOLEDB; Server=SQLSERVER.dev.LOCAL; Database=goldmouse; Trusted_Connection=Yes"

    'Open the connection to the database
        Dim cn
        Set cn = CreateObject("ADODB.Connection")
        cn.Open strConnSQL

    'Set the command
        Dim cmd
        Set cmd = CreateObject("ADODB.Command")
        Set cmd.ActiveConnection = cn

    'Set the record Set
        Dim rs
        Set rs = CreateObject("ADODB.recordSet")

    'Prepare the command
        cmd.CommandText = cmdTxt
        cmd.CommandType = 1  'adCmdText
        cmd.CommandTimeout = 3000   '50 minutes
        'cmd.CommandType = 4  'adCmdStoredProc


    'Execute the command
        Set rs = cmd.Execute
        Set cmd = Nothing

    'Close connections
        cn.Close
        Set cn = Nothing

End Sub
Mike
  • 133
  • 1
  • 1
  • 13
  • So what's changed? Code doesn't rot so something in the environment is different. Was the script moved to a different server? Did the SQL Server version get upgraded? What's the value of `strTableName` when the script fails? What's the value of the `errorCode` when it fails? Do you have logging in the packages themselves and if so, what do they report? – billinkc Mar 29 '21 at 17:06
  • @billinkc the file size varies, maybe connection stability with working from home - I'm suspecting the connection stability and the number of users logged into development. strTableName passes through 11 different names, the first 9 load ok, it's usually the largest one that fails. The errorCode is something I don't fully understand, I'd expect if the code returns 0 I wouldn't be here - How do I flesh out the logging for the SSIS packages? – Mike Mar 29 '21 at 19:30
  • 1
    [Package Deployment Model - Logging](https://www.sqlshack.com/overview-of-ssis-package-logging/) That's a design-time setup. If you had used the Project deployment model (deploy the SSISDB), the logging is built into the execution context. – billinkc Mar 29 '21 at 21:00
  • 1
    You can try adding a command line parameter to the `wsh.Run` block. Something like `& ".dtsx"" /rep EIW > \temp\package_run_log.txt"` That will dump the run-time messages into a text file, assuming vbscript isn't doing something weird with the redirect `>` – billinkc Mar 29 '21 at 21:03
  • @billinkc what on earth is error code 6 errorCode = wsh.Run("DTExec /F ""\\server.local\Data\SSIS SQL16\" & strTableName & ".dtsx"" /rep EIW > ""\\server.dev.local\logs\package_run_log.txt"" ", windowStyle, waitOnReturn) – Mike Mar 30 '21 at 17:04
  • No idea what error code 6 is but open the contents of `package_run_log.txt` That should have a host of information about the last run – billinkc Mar 30 '21 at 18:07

0 Answers0