?
Solved

Executing Remote DTS Package from MS Access Client

Posted on 2007-07-26
5
Medium Priority
?
1,152 Views
Last Modified: 2013-11-30
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
0
Comment
Question by:ogdenbt
  • 3
  • 2
5 Comments
 
LVL 14

Accepted Solution

by:
twoboats earned 2000 total points
ID: 19576933
Run the DTS package on the server from a stored procedure using

xp_cmdshell

to issue a dtsrun dos command. That way you don't need to install anything on the clients, and the package will run on the server.
0
 

Author Comment

by:ogdenbt
ID: 19577215
So the solution looks something like:

From MS Access client:
* Connect to the remote SQL Server
* Execute a stored procedure on the remote SQL Server  (I expect to use ADO to do so)
* The executed stored procedure contains an "xp_cmdshell" statement like:
      xp_cmdshell 'dtsrun RemoteServer\local /E /N DTSPackageName'
* The dtsrun command line RemoteServer refers to the remote SQL Server;
the DTSPackageName refers to the DTS Package on that server
* The dtsrun command line /E switch allows a trusted connection
* The dtsrun command line may include /L or /W switches to log or write the result status to
determine the outcome of the package execution respectively

THE dtsrun COMMAND EXECUTES ON THE RemoteServer, NOT THE CLIENT (making the client "light"
as you described)

Is this essentially what you are proposing?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19577240
You got it :)
0
 

Author Comment

by:ogdenbt
ID: 19577256
I will give that a shot, thanks very much for your help.
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19577269
No problem. Post back with how you get on, and if you need any assistance. Good luck.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question