Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Posts
    10

    Unanswered: Execute DTS package from ADO in VB

    I'm using ADO objects in a VB Macro for Excel. I'd like to execute a DTS package located on SQLServer.

    What is the syntax to do this?

    Here's my current database connection code:
    Code:
    Option Explicit
    
    Dim db_connection As ADODB.Connection
    Dim db_results As ADODB.Recordset
    Dim db_error As ADODB.Error
    
    Private Sub DB_Initialize()
        Set db_connection = New ADODB.Connection
            db_connection.Open "Provider='SQLOLEDB';Data Source='BACK_SQL';" & _
                                        "Initial Catalog='my_db';Integrated Security='SSPI';"
        Set db_results = New ADODB.Recordset
    End Sub

  2. #2
    Join Date
    Jun 2003
    Posts
    10
    Is it possible to use a stored procedure to execute a DTS package?

    What would the stored procedure be?

  3. #3
    Join Date
    Jul 2003
    Posts
    37
    Originally posted by odinsdream
    Is it possible to use a stored procedure to execute a DTS package?

    What would the stored procedure be?
    I don't know very much about VB but I definately know my DTS packages.. yes you can execute a DTS package via a stored procedure

    server_name=server name dts package is on
    user_name=login to access server
    password=user's password
    package_name = DTS package name
    package_password=DTS package pwd

    Create Procedure sp_ExecuteDTS AS

    exec master.. xp_cmdshell 'dtsrun /Sserver_name /Uuser_name /Ppassword /N"package_name" /Mpackage_password'

    GO
    --------------------

    if your package does not have a pwd then your sp should look like this

    Create Procedure sp_ExecuteDTS AS

    exec master.. xp_cmdshell 'dtsrun /Sserver_name /Uuser_name /Ppassword /N"package_name"

    GO

  4. #4
    Join Date
    May 2003
    Location
    Seattle, WA
    Posts
    4
    So how would one execute that stored procedure from VBA for Access?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •