Automation of DTS Package Execution

[mindscape]

Hi,

I've been doing some research for possible ways of executing dts packages and it's been challenging cuz apparently there isn't a very straight-forward method of doing this using T-SQL.

So far I've seen 2 recommended methods aside from programmatically performing the task using C# or CLR assembly code. My prob is that we're running on a SQL 2000 platform right now.

I have managed to come up with a solution that seems to work for some DTS packages but not others and was wondering if anyone can provide any suggestions for script that can be implemented in a job to initiate execution.

Here is what I have managed to come up with:

CREATE PROCEDURE dts_execute
@server sysname -- Name of Server where DTS package resides
,@dts_name varchar(100) -- Name of DTS package
,@trusted_connection int -- Bit value indicating if the authenticated connection will be
-- trusted 1 = TRUE or 0 = FALSE
,@user_name varchar(100) = NULL -- (SQL Username) ONLY required if bit is set to 0
,@password varchar(100) = NULL -- (SQL Password) ONLY required if bit is set to 0
WITH RECOMPILE
AS
BEGIN
declare
@name varchar(75)
,@id nvarchar(250)
,@version_id nvarchar(250)
,@error_code int
,@cmd nvarchar(4000)
,@crlf char(2)

SET @crlf = char(13)
DECLARE DTSCURSOR CURSOR
FOR
select dts.name, dts.id, dts.versionid from msdb..sysdtspackages dts
join
(select name, id, max(createdate) [createdate]
from msdb..sysdtspackages
group by name, id) sys
on dts.id = sys.id
and dts.[createdate]=sys.[createdate]
where sys.name=@dts_name
OPEN DTSCURSOR
FETCH NEXT FROM DTSCURSOR
INTO
@name, @id, @version_id
WHILE(@@FETCH_STATUS=0)
BEGIN
FETCH NEXT FROM DTSCURSOR INTO @name, @id, @version_id
END
CLOSE DTSCURSOR
DEALLOCATE DTSCURSOR

IF @trusted_connection = 1
BEGIN
--PRINT 'Trusted Connection ' + @crlf
SET @cmd = '''DTSRUN /S"' + @server + '" ' +
'/E /N"' + @name + '" /G"' + @id + '" ' +
'/V"' + @version_id + '" /W"TRUE"' + ''''
--print @cmd
EXECUTE('master..xp_cmdshell ' + @cmd + '')
END
IF @trusted_connection = 0
BEGIN
--PRINT 'Authenticated Connection ' + @crlf
SET @cmd = '''DTSRUN /S"' + @server + '" ' +
'/U"' + @user_name + '" /P"' + @password + '" ' +
'/N"' + @name + '" /G"' + @id + '" ' +
'/V"' + @version_id + '" /W"TRUE"' + ''''
--print @cmd
EXECUTE('master..xp_cmdshell ' + @cmd + '')
END
SET @proc_error = @@ERROR
IF @proc_error <> 0
BEGIN
RAISERROR('The call to the procedure <msdb..dts_execute> failed.', 16, 1)
END
RETURN @proc_error
-- end procedure body
-- ==============================================
END

The procedure works great and I am willing to share with anyone who can use it. But for some reason it doesn't eem to work when the DTS package makes a call to another remote process. It doesn't fail but it doesn't seem to launch properly either.

Does anyone have some script or suggestion that could help me out??? I know I could right-click the DTS package and select to schedule it but that method doesn't work very efficiently since the GUID could change upon subsequent modification of the package.

I need something that is flexible and could be implemented into a scheduled job. My solution seems to work but not under the conditions when another remote call is being made from the DTS package.

I would really appreciate any helpful suggestions, comments or alternative methods to acheive this.

Thanks

[nosepicker]

You can use dtsrun from a scheduled job if you change the step type to Operating System Command. Then you can run something like this:

DTSRun /S ServerName /E /N "DTS Package Name"

[mindscape]

Thanks nosepicker but that is the same thing that the procedure I posted does dynamically.

Anyway, I found out the problem and it wasn't the stored procedure but rather that the package I was calling was referencing some batch processes that reside on another system which doesn't have a SQL instance installed on it although the DTS package resides on a separate system.

So it was hanging trying to execute batch processes that it couldn't locate on the immediate server. Unfortunately no one conveyed this information to me until after the fact.

Thanks for the feedback though!