Nice article!

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

Nice one Kristian. Reusability is key to the SSIS architecture. There's lots of possibilities in v1 with much more to come in v2 I'm *sure*!

-Jamie

It IS a very good article and a long time in coming.  We've been able to stretch the DTS considerably with setting global variables.  Never did get looping figured out in SQL 2000 so our solution?  Cheat.

We had a stored procedure do the looping and then set the variables it needs through a temporary table, as below:

 while ( @TName is not null )
 begin
  print 'TableName is ' + @TName

  exec master..xp_cmdshell 'dtsrun /S"ServerName" /E /N"ReUsableDTSName"'

  if @TName = @TNameLast break
  
  set rowcount 1
  select @TName = TableName
  from LastUpdated
  where TableName not in (
   select TableName
   from  DenverStats )
  order by TableName
  set rowcount 0

end

It wasn't the most elegant solution, but it worked for getting the rowcounts for every table in the database of a remote Oracle schema.  It takes a while to learn but global variables have saved me a lot of copy/paste.  Thanks for bringing it up.

Andre