Firing already made DTS Package -- from vb.net

Someone please help me with this.

I'm trying to fire off an already created DTS package. This package is stored within SQL Server's -- underneith the Data Transformation Services / Local Packages section.

HOW CAN I FIRE THIS OFF FROM A VB .NET APPLICATION

I'm familiar with strored procedures and using them in vb.net so if somone could lead me down that road I would be very much appriciated.

Thanks in advance everyone,

RB

Try this url you may find your solution. Hope this helps.

http://www.sqldts.com

|||

I have this in my stored proc already:

Here is what I am trying to run within my stored proc:

dtsrun /F'CSI_DTSPackages/GTProPaymentExtract.dts'/N'GTProPaymentExtract'/M''

PLEASE TELL ME WHAT I'M DOING WRONG!!!

Thanks in advance everyone.

RB

|||

I gave you Darren Green's site because if you can do it in DTS he may have done it. I have found another link, it may solve your problem. Hope this helps.

http://www.vbdotnetheaven.com/Code/Jun2003/2070.asp

|||

here a stored procedure I got from the DTS site. As this fires off a DTS package on the server your path will have to be a UNC path. In my case the path is source to a file I am reading data from.

CREATE PROCEDURE spExecuteDTSPKG
@.Server varchar(255),
@.PkgName varchar(255), -- Package Name (Defaults to most recent version)
@.ConnName varchar(25) = '',
@.XLSPath varchar(255) = '', -- Path to Source File
@.ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@.IntSecurity bit = 1, -- 0 = SQL Server Security, 1 = Integrated Security
@.PkgPWD varchar(255) = '' -- Package Password
AS
SET NOCOUNT ON

DECLARE @.hr int, @.ret int, @.oPKG int, @.Cmd varchar(1000), @.Connection varchar(255)
Select @.Connection = 'Connections.Item(' + @.ConnName + ').DataSource'
Select @.ret = 0

-- Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT

SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "", "", 256, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'

EXEC @.hr = sp_OAMethod @.oPKG, @.Cmd, NULL

EXEC @.hr = sp_OASetProperty @.oPKG, @.Connection, @.XLSPath
If @.hr <> 0
BEGIN
PRINT '*** OASetProperty for Connection Failed***'
EXEC sp_OAGetErrorInfo @.oPkg
RETURN
END

-- Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'

-- Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'

-- Clean Up
EXEC @.hr = sp_OADestroy @.oPKG

RETURN @.ret
GO