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: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 SubIs it possible to use a stored procedure to execute a DTS package?

What would the stored procedure be?|||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|||So how would one execute that stored procedure from VBA for Access?

Execute DTS from .sql batch

1)
How do i execute myDTS.dts file which is located on c:\...\myDTS.dts from
sql batch ?
2)
How do i execute DTS job "myDTS" which is saved on sql server ? from sql
batch ?1) You can run it via xp_cmdshell using the dtsrun command line tool. See
BOL for details e.g
exec xp_cmdshell 'dtsrun /Ffilename /Npackage_name'
2) If you mean a SQL job then you can call sp_start_job. See BOL for
details.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mikus" <Mikus@.discussions.microsoft.com> wrote in message
news:F2732A01-18FE-4326-BAF7-286DE1110DC1@.microsoft.com...
> 1)
> How do i execute myDTS.dts file which is located on c:\...\myDTS.dts from
> sql batch ?
> 2)
> How do i execute DTS job "myDTS" which is saved on sql server ? from sql
> batch ?

Execute DTS from .sql batch

1)
How do i execute myDTS.dts file which is located on c:\...\myDTS.dts from
sql batch ?
2)
How do i execute DTS job "myDTS" which is saved on sql server ? from sql
batch ?
1) You can run it via xp_cmdshell using the dtsrun command line tool. See
BOL for details e.g
exec xp_cmdshell 'dtsrun /Ffilename /Npackage_name'
2) If you mean a SQL job then you can call sp_start_job. See BOL for
details.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mikus" <Mikus@.discussions.microsoft.com> wrote in message
news:F2732A01-18FE-4326-BAF7-286DE1110DC1@.microsoft.com...
> 1)
> How do i execute myDTS.dts file which is located on c:\...\myDTS.dts from
> sql batch ?
> 2)
> How do i execute DTS job "myDTS" which is saved on sql server ? from sql
> batch ?

Execute DTS from .sql batch

1)
How do i execute myDTS.dts file which is located on c:\...\myDTS.dts from
sql batch ?
2)
How do i execute DTS job "myDTS" which is saved on sql server ? from sql
batch ?1) You can run it via xp_cmdshell using the dtsrun command line tool. See
BOL for details e.g
exec xp_cmdshell 'dtsrun /Ffilename /Npackage_name'
2) If you mean a SQL job then you can call sp_start_job. See BOL for
details.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mikus" <Mikus@.discussions.microsoft.com> wrote in message
news:F2732A01-18FE-4326-BAF7-286DE1110DC1@.microsoft.com...
> 1)
> How do i execute myDTS.dts file which is located on c:\...\myDTS.dts from
> sql batch ?
> 2)
> How do i execute DTS job "myDTS" which is saved on sql server ? from sql
> batch ?