vbscript: Function RunSSIS

Run a SQL 2005 SSIS package from vbscript. Useful if you need to do some table manipulation prior to an MA import – such as when generating a Delta table.

Note the comment in the script about using objExec.stdOut. I employed a wait loop and objExec.Status before, but I had a problem with certain SSIS packages not returning a status to vbscript. even though they had completed. I have found this method to be more stable.

 

Const MIIS_FOLDER = “C:\Program Files\Microsoft Identity Integration Server”
Const DB_CONNECT_STRING = “Provider=SQLOLEDB;Data Source=(local);Initial Catalog=DB-name;Integrated Security=SSPI”
Const SSIS_SUCCESS = “The package execution returned DTSER_SUCCESS”
Const SSIS_LOG_FILE = “log_file_path”
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Const Unicode = -1

‘—————————————————–
‘  Function RunSSIS
‘  + Call a SQL SSIS package
‘  + Returns 0 for success
‘—————————————————-

Function RunSSIS(packageName)
  Dim strCmd, strOutput
  Dim objShell, objExec
  Dim iCount
Dim SSISLogFile, objFS, objSSISLogFile

Set objFS = CreateObject(“Scripting.FileSystemObject”)
Set objSSISLogFile = objFS.OpenTextFile(SSIS_LOG_FILE, ForWriting, TRUE)

  WriteLog “Running SSIS package ” & packageName
 
  strCmd = “dtexec /DTS “”\MSDB\” & packageName & “”” /SERVER server-name /MAXCONCURRENT “” -1 “” /CHECKPOINTING OFF  /REPORTING V”
  Set objShell = CreateObject(“WScript.Shell”)

  Set objExec = objShell.Exec(strCmd)
‘ –need to use objExec.stdOut straight after Exec to force vbscript to wait
  strOutput = objExec.stdOut.ReadAll

  If InStr(strOutput, SSIS_SUCCESS) > 0 Then
    RunSSIS = 0
    WriteLog “Package completed successfully.”
  Else
    RunSSIS = 1
    WriteLog “Package failed. See ” & SSISLogFileName & ” for the full output.”
  End If
 
  objSSISLogFile.Write(strOutput)

End Function