SSIS - Code Reuse and Complex Control Flows

  • Kristian Wedberg

    SSC Veteran

    Points: 297

    Comments posted to this topic are about the content posted at

  • Kirk Haselden


    Points: 1683

    Nice article!


  • Jamie Thomson


    Points: 11805

    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*!


  • Kristian Wedberg

    SSC Veteran

    Points: 297

    Thanks guys, glad you liked it!


  • AndreQ1

    Right there with Babe

    Points: 758

    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 )


      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



    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.


  • lucasm-630215

    Old Hand

    Points: 302

    I am attempting to use a for loop construct to pass a user variable into a script which will be used in the Select statement as a parameter. Reading your discussion helps me understand the for loop, but I don't see how I could pass a variable. Any thoughts or ideas?

  • Kristian Wedberg

    SSC Veteran

    Points: 297

    It's not clear from your post where you're running the select; in script task, in execute sql task, or in data flow?

  • lucasm-630215

    Old Hand

    Points: 302

    Sorry Kristian,

    I have data flows developed that execute the SQL in OLE DB Source objects. That is where the Parameter is located.


Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply