SQL Server Performance Forum – Threads Archive

Error Accessing Package via VB

Hi, Bit of a long one this [:I] I’m coming up against a problem trying to run a DTS package through an Access front end. Background. Windows Server 2003 SQLServer 2000 sp 4. Access 2003 db created as Access2000 1: Package was created by me in Enterprise Manager on my local PC. Pulls data from third party ODBC for our ERP into SQLServer table. At this point i -ChrisB- did not have a profile/login on windows server or the SQLServer if i need to access the server i used VNC from my local PC and logged in under Administrator and the SQLServer under sa with password.
3: Referenced dtspkg.dll in Access
4: Accessed packaged using this VBA.
Initially i used the sa login and password with DTSSQLStgFlag_Default authentication to access the package.
Option Compare Database
Option Explicit Public Function RunDTS(PackageName As String)
Dim pck As New DTS.Package
Dim msDTSServer As String
Dim msDTSUser As String
Dim msDTSPwd As String
Dim msDTSPkg As String msDTSServer = ********
msDTSPkg = PackageName pck.LoadFromSQLServer msDTSServer, , , DTSSQLStgFlag_UseTrustedConnection, , , , msDTSPkg
pck.Execute
Dim istatus As Long
Dim lperrorcode As Long
istatus = True
lperrorcode = -1
Dim i As Integer
Dim ErrSource As String
Dim ErrDescription As String For i = 1 To pck.Steps.Count
If pck.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
With pck.Steps(i)
.GetExecutionErrorInfo lperrorcode, ErrSource, ErrDescription
Debug.Print lperrorcode
Debug.Print ErrSource
Debug.Print ErrDescription
End With
istatus = False
Dim STR As String STR = STR & "Package Name;" & pck.Steps(i).Name & " " & vbCrLf & _
"Package Description;" & pck.Description & " " & vbCrLf & _
"Package CreatorName;" & pck.CreatorName & " " & vbCrLf & _
"Package CreatorComputerName;" & pck.CreatorComputerName & " " & vbCrLf & _
"Package PackageID;" & pck.PackageID & vbCrLf & vbCrLf & vbCrLf
End If
Next i Debug.Print STR
MsgBox STR
If pck.Steps(1).ExecutionResult = 0 Then MsgBox "Update has been successful", vbInformation, "Package Authority"
Else MsgBox "Update has failed; please contact the IT dept", vbCritical, "Package Authority" End If
MsgBox pck.Steps(1).ExecutionResult
Set pck = Nothing
End Function This worked on my local PC. 5: I then created a Windows account as ChrisB and added that to the SQLServer login with full admin rights and ammended the code to use windows authentication. The code still works fine.
6: For test purposes I then created a second Windows account with the same priviliges as mine for a different user and added them to the SQLServer again with the same account permissions. 7: I installed the enterprise manager components on this test machine so i could access dtspkg.dll 8: I installed the Access front end on the desktop and tried to run the code. It failed with the following.
9: I wondered if the error was caused by the owner of the package being me.
So i saved the package on the server via VNC so the owner now read Administrator.
It still worked on my machine and still failed on the test machine.
The authentication is working; i changed the name of the package to one that doesn’t exist and the error returned informed me of a missing package so i believe it is at least accessing the server. Appreciating the fact that the error says it is a problem with "Microsoft OLE DB Provider for ODBC Drivers" I checked the connection in the package. Data Source: Microsoft ole db provider for SQL Server
Server: Myserver
Authentication: Use Windows Authentication I’m now at a loss to explain this error. Can anyone shed some light on it? Thanks.

---

Hi,<br /><br />Found the problem, we have a third party ODBC driver for our ERP. The DTS package appears to be trying to run on the local machine which doesn’t have a copy of the driver. What threw me was the error source being "Microsoft OLE DB Provider for ODBC Drivers" <br /><br />We have limited licences for the driver so it looks like i’ll have to find another way of doing this <img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />

---

Try to download the latest drives in this case and try again, also search under MS-Access related forums for optimum resolution. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

]]>