RunningLooping Through DTS Packages

Running/Looping Through DTS Packages

One of the features that every DTS developer wants to use but that is missing is looping. The tool simply does not allow you to create tasks to be executed in a loop. The solution is to use a scripting language (such as T-SQL or VBScript) to launch a group of tasks (organized into a single DTS package) in a loop. The following procedure loops through a list of databases and executes a DTS package with a database name as an input parameter (a global variable in DTS terminology):

 ALTER PROCEDURE util.ap_DTS_Loop -- Loop through Asset databasesrun   --  run the DTS package for each of them -- test:     exec util.ap_DTS_Loop 1    @debug int = 0 As set nocount on declare @intCount int,         @intCounter int,         @chvDOS varchar(2000),         @chvDB sysname,         @chvServer sysname Declare @intErrorCode int,         @chvProcedure sysname set xact_abort on set nocount on set @chvProcedure = 'util.ap_DTS_Loop' if @debug <> 0    select '**** '+ @chvProcedure + ' START ****'    Create table #db(Id int identity(1,1},                     Name sysname)    insert into #db (Name)       select Name from master.dbo.sysdatabases       where name like 'Asset%'    -- set loop    select @intCount = Count(*),           @intCounter = 1,           @chvServer = @@SERVERNAME    from #db -- loop through list of databases while @intCounter <= @intCount  begin    -- get db       select @chvDB = Name       from #db       where Id = @intCounter       SELECT @chvDOS = 'dtsrun /S' + @chvServer                 + ' /Udbo /E /NDW_ETL'                 + ' /ADatabase:8='  + @chvDB                 + ' /AServer:8=' +  @chvServer       if @debug = 0          EXEC master.dbo.xp_cmdshell @chvDOS, no_output       else           select @chvDOS    -- let's go another round and get another property       set @intCounter = OintCounter + 1 end drop table #db if @debug <> 0    select '**** '+ @chvProcedure + ' END ****' return 

If you run the procedure, it will generate and execute the following set of commands:

 dtsrun /SA1000 /Udbo /E /NDW_ETL /ADatabase:8=Asset /AServer:8=A1000 dtsrun /SA1000 /Udbo /E /NDW_ETL /ADatabase:8=Asset7 /AServer:8=A1000 dtsrun /SA1000 /Udbo /E /NDW_ETL /ADatabase:8=Asset2000 /AServer:8=A1000 dtsrun /SA1000 /Udbo /E /NDW_ETL /ADatabase:8=Asset2000_2 /AServer:8=A1000 dtsrun /SA1000 /Udbo /E /NOW_ETL /ADatabase:8=Asset5 /AServer:8=A1000