Automation of DTS Package Execution
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 :)
