Execute SQL 2005 SSIS Packages



Below is a function I used in the past that executes a SQL 2000 DTS job. I'm
trying to find new vba code that can execute a SQL 2005 SSIS Package. I
realize MS is jamming .Net into everything and I was curious if it's even
possible to get Access and SSIS 2005 to communicate via VBA?

I'm even open to executing a SSIS Package using ASP web pages if it's
possible to do without .Net.

I did look through my Access 2003 Reference Libraries and noticed a "SQL
Serve 2005 Integration Services Debugging Host Type Library", but not sure
if it is capable. Any input would be appreciated.


Function executeDTS()
'executes SQL 2000 DTS Job to update SQL 2000 db

Dim oPKG As DTS.Package, oStep As DTS.step
Set oPKG = New DTS.Package

Dim sServer As String, sUsername As String, sPassword As String
Dim sPackageName As String, sMessage As String
Dim lErr As Long, sSource As String, sDesc As String

sServer = "myServer"
sUsername = "user"
sPassword = "password"
sPackageName = "myDTSPackage"

' Use mixed mode authentication
oPKG.LoadFromSQLServer sServer, sUsername, sPassword, _
DTSSQLStgFlag_Default, , , , sPackageName

' Use Trusted Connection
'oPKG.LoadFromSQLServer sServer, , , _
' DTSSQLStgFlag_UseTrustedConnection, , , , sPackageName

' Set Exec on Main Thread
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True


' Get Status and Error Message
For Each oStep In oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
sMessage = sMessage & "Step """ & oStep.Name & _
""" Failed" & vbCrLf & _
vbTab & "Error: " & lErr & vbCrLf & _
vbTab & "Source: " & sSource & vbCrLf & _
vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
sMessage = sMessage & "Step """ & oStep.Name & _
""" Succeeded" & vbCrLf & vbCrLf
End If


Set oStep = Nothing
Set oPKG = Nothing

MsgBox sMessage

End Function



Paul Shapiro

Look at SQL Server documentation to see if you can run the SSIS package from
a stored procedure, or a SQL Agent Job. Then execute that stored proc or job
from Access.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question