Someone Else
3/16/2007 4:48:00 PM
3rd way:
DECLARE
@Package nvarchar(100),
@Server nvarchar(50)
SET @Package = 'some package'
DECLARE @hr int
DECLARE @oPKG int
IF(@Server is null) SET @Server = @@SERVERNAME
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
-- DTSSQLServerStorageFlags :
--- DTSSQLStgFlag_Default = 0
--- DTSSQLStgFlag_UseTrustedConnection = 256
DECLARE @cmd nvarchar(500)
SET @Cmd ='LoadFromSQLServer("' + @Server + '", "", "", 256, , , , "'
+ @Package + '")'
EXEC @hr = sp_OAMethod @oPKG, @cmd, NULL
IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN
END
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
GO
On 16 Mar, 15:03, "Russell Fields" <russellfie...@nomail.com> wrote:
> You can do one of two things.
>
> 1 - Create a SQL Agent job to run the DTS package. Use sp_start_job to
> cause the job to start. This will then execute asynchonously from your
> stored procedure.
>
> 2 - Use xp_cmdshell to execute DTSRUN with the appropriate parameters. This
> will be synchronous so your stored procedure will wait for the package to
> run. (Xp_cmdshell comes with some security issues, so you have to decide
> whether to use it.)
>
> RLF"slimleh" <slim...@discussions.microsoft.com> wrote in message
>
> news:9375CCDE-FFBC-4947-BAEA-7CA557495565@microsoft.com...
>
>
>
> > Hi,
>
> > Can I know is there a way to call a DTS from a store procedure?
>
> > If so can someone provide a sample code?
>
> > Thanks- Hide quoted text -
>
> - Show quoted text -