How to Call an SSIS Package From VBScript

By Jim Campbell

An SSIS package provides you with saved SQL Server commands that you can run on the database server. The package runs in VBScript using the Windows shell functions. The package is stored on the database server, so you use the VBScript code to load and run a package remotely or during system boot. SSIS packages contain the DTSX file extension.

Click the Windows "Start" button and type "notepad" in the search text box. Press "Enter" to open the file editor. If you already have a VBS file set up, open it in Notepad. Add the package code to an existing VBScript file, or create a new file to run the SSIS package.

Create the shell class variable. The following code creates a variable for the Windows shell program:

Set windows_shell = CreateObject("WScript.Shell")

Run the package. If the package is large, it can take several minutes to run. You can send a "Please Wait" message to the user, so the user knows the package is running. The following code runs the file "package.dtsx" and sends a "Please Wait" message"

echo "Please wait..."

lngReturnCode = shell.Run("dtexec /f E:\package.dtsx")

Close the shell variable and return a message to the user that the package is complete. The following code closes the variable and tells your user that the package ran successfully:

echo "The SSIS package has completed."

set windows_shell = Nothing