MS-Access - DTEXEC issues

Question

I have an Access database that I am trying to run an SSIS package from. Here is the code I am using:

Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1

wsh.Run "dtexec /f "J:\SQL Server Programming\SSIS\MyPackage\MyPackage.dtsx"", windowStyle, waitOnReturn

But whenever I run through the Access database, I get a Compile error: Expected: end of statement.

Can someone please tell me what I am doing wrong with this query? I am using a solution from this post:

Wait for shell command to complete

And I have searched through that post and could not find an answer that helped me with my issue. Any help is appreciated.

Answer 1

There is a problem with quotes in the string you're trying to build for the first argument to wsh.Run. I suggest you work out that string separately.

Here is an example from the Access Immediate window which creates a valid string which includes a quoted file path:

Debug.Print "dtexec /f ""J:\SQL Server Programming\SSIS\MyPackage\MyPackage.dtsx"""
dtexec /f "J:\SQL Server Programming\SSIS\MyPackage\MyPackage.dtsx"

Assuming that is the string you want, you can adapt your code to use it with wsh.Run. I would store the string in a variable and Debug.Print it before calling wsh.Run:

Dim strCommand As String
strCommand = "dtexec /f ""J:\SQL Server Programming\SSIS\MyPackage\MyPackage.dtsx"""
Debug.Print strCommand
wsh.Run strCommand, windowStyle, waitOnReturn