Running DTS from within a Stored Proc

Printable View

10-18-2000, 11:14 AM
Aristotle Spyropoulos

Running DTS from within a Stored Proc

Is there any way to call a DTS package from within a stored procedure?

EXEC(dtsrun <myDTSpackage>)

Thank you.
10-18-2000, 12:14 PM
Marcelo Velasquez

Running DTS from within a Stored Proc (reply)

create proc sp_executepackage
@packagename varchar(255), --package name, gets most recent version
@userpwd varchar(255) = Null, --login pwd
@intsecurity bit = 0, --use non-zero to indicate integrated security
@pkgPwd varchar(255) = '' --package password
as
declare @hr int
declare @object int

--create a package object
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
Begin
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
end

--load the package (ADD integrated security support)
declare @svr varchar(15)
declare @login varchar(15)
select @login = SUSER_NAME()
select @svr = HOST_NAME()
declare @flag int
select @flag = 0

if @intsecurity = 0
if @userpwd = Null
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd
-- ServerName As String, [ServerUserName As String], [ServerPassword As String], [Flags As DTSSQLServerStorageFlags = DTSSQLStgFlag_Default], [PackagePassword As String], [PackageGuid As String], [PackageVersionGuid As String], [PackageName As String], [pVarPersistStgOfHost])
else
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd, @ServerPassword = @userpwd
else
begin
select @flag = 256
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd
end

IF @hr <> 0
BEGIN
print 'LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END

--execute it
EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END

--return the step errors as a recordset
exec sp_displaypkgerrors @object

-- unitialize the package
EXEC @hr = sp_OAMethod @object, 'UnInitialize'
IF @hr <> 0
BEGIN
print 'UnInitialize failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END

-- release the package object
EXEC @hr = sp_OADestroy @object
if @hr <> 0
Begin
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
end
GO

------------
Aristotle Spyropoulos at 10/18/00 12:14:53 PM

Is there any way to call a DTS package from within a stored procedure?

EXEC(dtsrun <myDTSpackage>)

Thank you.
10-18-2000, 05:20 PM
Aristotle Spyropoulos

Running DTS from within a Stored Proc (reply)

Marcelo,
I think I understand. Thank you for your help.

------------
Marcelo Velasquez at 10/18/00 1:14:17 PM

create proc sp_executepackage
@packagename varchar(255), --package name, gets most recent version
@userpwd varchar(255) = Null, --login pwd
@intsecurity bit = 0, --use non-zero to indicate integrated security
@pkgPwd varchar(255) = '' --package password
as
declare @hr int
declare @object int

--create a package object
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
Begin
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
end

--load the package (ADD integrated security support)
declare @svr varchar(15)
declare @login varchar(15)
select @login = SUSER_NAME()
select @svr = HOST_NAME()
declare @flag int
select @flag = 0

if @intsecurity = 0
if @userpwd = Null
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd
-- ServerName As String, [ServerUserName As String], [ServerPassword As String], [Flags As DTSSQLServerStorageFlags = DTSSQLStgFlag_Default], [PackagePassword As String], [PackageGuid As String], [PackageVersionGuid As String], [PackageName As String], [pVarPersistStgOfHost])
else
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd, @ServerPassword = @userpwd
else
begin
select @flag = 256
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd
end

IF @hr <> 0
BEGIN
print 'LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END

--execute it
EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END

--return the step errors as a recordset
exec sp_displaypkgerrors @object

-- unitialize the package
EXEC @hr = sp_OAMethod @object, 'UnInitialize'
IF @hr <> 0
BEGIN
print 'UnInitialize failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END

-- release the package object
EXEC @hr = sp_OADestroy @object
if @hr <> 0
Begin
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
end
GO

------------
Aristotle Spyropoulos at 10/18/00 12:14:53 PM

Is there any way to call a DTS package from within a stored procedure?

EXEC(dtsrun <myDTSpackage>)

Thank you.
10-18-2000, 08:20 PM
Eddie Deeney

Running DTS from within a Stored Proc (reply)

Another Alternative is, if you are familiar with Data Transformation Services (DTS) Designer. A DTS package is a GUI description of all the work that must be performed as part of your transformation.

Having created the Package that you need the package can be saved as file, or stored on a SQL Server. This Package can then be execute by running the dtsrun utility. Which is a command line based utility. Simply pass it the appropriate parameters, depending on whether you saved it as a file or stored it in SQL.

To call this from within a stored procedure use the xp_cmdshell stored procedure. However by default only members of the sysadmin can run this extended stored procedure.

I have included 2 simple examples, one assumes that you saved the DTS package as a file, the other assume that you save it to a SQL Server.

Create Procedure dts_file
Begin
execute master..xp_cmdshell 'dtsrun /F C:MSSQL7dtsDTS_Server01.dts
/N DTS_Server01' -- Package Name
End

----------------------------------------------------------------------

Create Procedure dts_file2
AS
Begin
execute master..xp_cmdshell 'dtsrun /S Server01 --Server name
/E -- Trusted Connection
/N DTS_Server01' -- Package Name
End

Hope that helps
- Eddie

------------
Aristotle Spyropoulos at 10/18/00 6:20:38 PM

Marcelo,
I think I understand. Thank you for your help.

------------
Marcelo Velasquez at 10/18/00 1:14:17 PM

create proc sp_executepackage
@packagename varchar(255), --package name, gets most recent version
@userpwd varchar(255) = Null, --login pwd
@intsecurity bit = 0, --use non-zero to indicate integrated security
@pkgPwd varchar(255) = '' --package password
as
declare @hr int
declare @object int

--create a package object
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
Begin
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
end

--load the package (ADD integrated security support)
declare @svr varchar(15)
declare @login varchar(15)
select @login = SUSER_NAME()
select @svr = HOST_NAME()
declare @flag int
select @flag = 0

if @intsecurity = 0
if @userpwd = Null
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd
-- ServerName As String, [ServerUserName As String], [ServerPassword As String], [Flags As DTSSQLServerStorageFlags = DTSSQLStgFlag_Default], [PackagePassword As String], [PackageGuid As String], [PackageVersionGuid As String], [PackageName As String], [pVarPersistStgOfHost])
else
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd, @ServerPassword = @userpwd
else
begin
select @flag = 256
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd
end

IF @hr <> 0
BEGIN
print 'LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END

--execute it
EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END

--return the step errors as a recordset
exec sp_displaypkgerrors @object

-- unitialize the package
EXEC @hr = sp_OAMethod @object, 'UnInitialize'
IF @hr <> 0
BEGIN
print 'UnInitialize failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END

-- release the package object
EXEC @hr = sp_OADestroy @object
if @hr <> 0
Begin
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
end
GO

------------
Aristotle Spyropoulos at 10/18/00 12:14:53 PM

Is there any way to call a DTS package from within a stored procedure?

EXEC(dtsrun <myDTSpackage>)

Thank you.
10-18-2000, 08:25 PM
Marcelo

Running DTS from within a Stored Proc (reply)

Very good

------------
Eddie Deeney at 10/18/00 9:20:53 PM

Another Alternative is, if you are familiar with Data Transformation Services (DTS) Designer. A DTS package is a GUI description of all the work that must be performed as part of your transformation.

Having created the Package that you need the package can be saved as file, or stored on a SQL Server. This Package can then be execute by running the dtsrun utility. Which is a command line based utility. Simply pass it the appropriate parameters, depending on whether you saved it as a file or stored it in SQL.

To call this from within a stored procedure use the xp_cmdshell stored procedure. However by default only members of the sysadmin can run this extended stored procedure.

I have included 2 simple examples, one assumes that you saved the DTS package as a file, the other assume that you save it to a SQL Server.

Create Procedure dts_file
Begin
execute master..xp_cmdshell 'dtsrun /F C:MSSQL7dtsDTS_Server01.dts
/N DTS_Server01' -- Package Name
End

----------------------------------------------------------------------

Create Procedure dts_file2
AS
Begin
execute master..xp_cmdshell 'dtsrun /S Server01 --Server name
/E -- Trusted Connection
/N DTS_Server01' -- Package Name
End

Hope that helps
- Eddie

------------
Aristotle Spyropoulos at 10/18/00 6:20:38 PM

Marcelo,
I think I understand. Thank you for your help.

------------
Marcelo Velasquez at 10/18/00 1:14:17 PM

create proc sp_executepackage
@packagename varchar(255), --package name, gets most recent version
@userpwd varchar(255) = Null, --login pwd
@intsecurity bit = 0, --use non-zero to indicate integrated security
@pkgPwd varchar(255) = '' --package password
as
declare @hr int
declare @object int

--create a package object
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
Begin
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
end

--load the package (ADD integrated security support)
declare @svr varchar(15)
declare @login varchar(15)
select @login = SUSER_NAME()
select @svr = HOST_NAME()
declare @flag int
select @flag = 0

if @intsecurity = 0
if @userpwd = Null
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd
-- ServerName As String, [ServerUserName As String], [ServerPassword As String], [Flags As DTSSQLServerStorageFlags = DTSSQLStgFlag_Default], [PackagePassword As String], [PackageGuid As String], [PackageVersionGuid As String], [PackageName As String], [pVarPersistStgOfHost])
else
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd, @ServerPassword = @userpwd
else
begin
select @flag = 256
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd
end

IF @hr <> 0
BEGIN
print 'LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END

--execute it
EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END

--return the step errors as a recordset
exec sp_displaypkgerrors @object

-- unitialize the package
EXEC @hr = sp_OAMethod @object, 'UnInitialize'
IF @hr <> 0
BEGIN
print 'UnInitialize failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END

-- release the package object
EXEC @hr = sp_OADestroy @object
if @hr <> 0
Begin
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
end
GO

------------
Aristotle Spyropoulos at 10/18/00 12:14:53 PM

Is there any way to call a DTS package from within a stored procedure?

EXEC(dtsrun <myDTSpackage>)

Thank you.
10-19-2000, 07:52 AM
Aristotle Spyropoulos

Running DTS from within a Stored Proc (reply)

Eddie,
This is very nice. Here is another solution recommended to me:
1. I schedule the DTS pacakge (i.e. create a job) and then disable the job.
2. Run the job from within the usp as:

msdb..sp_start_job @job_name = 'MyJobThatRunsDTSPackage'

I could not run it from within the stored procedure, as I was attempting:
USE msdb
sp_start_job @job_name = 'MyJobThatRunsDTSPackage'

but stored procedures do not allow for "USE" statements. Your suggestion below (master..xp_cmdshell) gave me the clue.

Thank you!

------------
Eddie Deeney at 10/18/00 9:20:53 PM

Another Alternative is, if you are familiar with Data Transformation Services (DTS) Designer. A DTS package is a GUI description of all the work that must be performed as part of your transformation.

Having created the Package that you need the package can be saved as file, or stored on a SQL Server. This Package can then be execute by running the dtsrun utility. Which is a command line based utility. Simply pass it the appropriate parameters, depending on whether you saved it as a file or stored it in SQL.

To call this from within a stored procedure use the xp_cmdshell stored procedure. However by default only members of the sysadmin can run this extended stored procedure.

I have included 2 simple examples, one assumes that you saved the DTS package as a file, the other assume that you save it to a SQL Server.

Create Procedure dts_file
Begin
execute master..xp_cmdshell 'dtsrun /F C:MSSQL7dtsDTS_Server01.dts
/N DTS_Server01' -- Package Name
End

----------------------------------------------------------------------

Create Procedure dts_file2
AS
Begin
execute master..xp_cmdshell 'dtsrun /S Server01 --Server name
/E -- Trusted Connection
/N DTS_Server01' -- Package Name
End

Hope that helps
- Eddie

------------
Aristotle Spyropoulos at 10/18/00 6:20:38 PM

Marcelo,
I think I understand. Thank you for your help.

------------
Marcelo Velasquez at 10/18/00 1:14:17 PM

create proc sp_executepackage
@packagename varchar(255), --package name, gets most recent version
@userpwd varchar(255) = Null, --login pwd
@intsecurity bit = 0, --use non-zero to indicate integrated security
@pkgPwd varchar(255) = '' --package password
as
declare @hr int
declare @object int

--create a package object
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
Begin
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
end

--load the package (ADD integrated security support)
declare @svr varchar(15)
declare @login varchar(15)
select @login = SUSER_NAME()
select @svr = HOST_NAME()
declare @flag int
select @flag = 0

if @intsecurity = 0
if @userpwd = Null
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd
-- ServerName As String, [ServerUserName As String], [ServerPassword As String], [Flags As DTSSQLServerStorageFlags = DTSSQLStgFlag_Default], [PackagePassword As String], [PackageGuid As String], [PackageVersionGuid As String], [PackageName As String], [pVarPersistStgOfHost])
else
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd, @ServerPassword = @userpwd
else
begin
select @flag = 256
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd
end

IF @hr <> 0
BEGIN
print 'LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END

--execute it
EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END

--return the step errors as a recordset
exec sp_displaypkgerrors @object

-- unitialize the package
EXEC @hr = sp_OAMethod @object, 'UnInitialize'
IF @hr <> 0
BEGIN
print 'UnInitialize failed'
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
END

-- release the package object
EXEC @hr = sp_OADestroy @object
if @hr <> 0
Begin
EXEC sp_displayoaerrorinfo @object --, @hr
RETURN
end
GO

------------
Aristotle Spyropoulos at 10/18/00 12:14:53 PM

Is there any way to call a DTS package from within a stored procedure?

EXEC(dtsrun <myDTSpackage>)

Thank you.