Execute SQL 2005 SSIS Packages

[Scott]

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.


CODE
**************

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
Next

oPKG.Execute

' 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
Else
sMessage = sMessage & "Step """ & oStep.Name & _
""" Succeeded" & vbCrLf & vbCrLf
End If
Next

oPKG.UnInitialize

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.