troubleshooting Question

Executing Remote DTS Package from MS Access Client

Avatar of ogdenbt
ogdenbt asked on
Microsoft AccessMicrosoft SQL Server
5 Comments1 Solution1196 ViewsLast Modified:
I am writing an MS Access 2003 application which needs to execute a DTS package on the server from a client machine.  
The following code attempts to execute the LoadFromSQLServer method on the created DTS.Package object.
The error "Invalid Class String" occurs at the "LoadFromSqlServer" line.  The LoadFromSQLServer method executes successfully on my development
machine where the client and server are on the same machine.
      
Based on research on EE and the Net I have taken the following steps so far to resolve the problem:
      
* Manually copied over the DTS DLL and RLL files from my development machine to the test client machine and registered them as documented
in the "redist.txt" file on my SQL Server distribution disks.  System Admin is currently getting the SQL Server install disks for me to
I can try to install the SQL Server Client Tools on the client to make sure that all DLLs and RLLs are present and registered.
* Found that the error may be due to the package containing unregistered Analysis services steps.
So I created a test DTS package that contains only a DTS Connection object. The error persisted.

As I said, I have attempted to research all applicable solutions on EE and elsewhere.  Any suggestions out there?

I also have a more general question (or set of related questions):
When the LoadFromSQLServer method is executed, where does the package get executed i.e. where does the package execution process run,
on the client or the remote SQL Server?  If on the client then do all executables included in the package command line tasks have to be installed
on every client from which the package is executed (thus making each client nearly as "heavy" as the server)?

Thanks for your help.

Environment:
Client: Windows XP, MS Access 2003
Server: Windows 2003 Server, SQL Server 2000

MS Access VBA Code:

    Const DTSSQLStgFlag_Default = 0
    Const DTSStepExecResult_Failure = 1
    Const DTSSQLStgFlag_UseTrustedConnection = 256
   
    Dim objPkg As DTS.Package
    Dim objStep As DTS.Step
   
    Dim strMsg As String
    Dim ADOErr As ADODB.Error
    Dim strError As String
    Dim boolRC As Boolean
   
    boolRC = False
   
    On Error GoTo Gen_ErrorHandler
       
    Set objPkg = CreateObject("DTS.Package")
    With objPkg
        .LoadFromSQLServer "RemoteServer", "", "", DTSSQLStgFlag_UseTrustedConnection, "", "", "", "DTSPackageName"
        .Execute
    End With
ASKER CERTIFIED SOLUTION
twoboats

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros