Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS - Code Reuse and Complex Control Flows Expand / Collapse
Author
Message
Posted Tuesday, August 09, 2005 11:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 12, 2013 12:43 PM
Points: 7, Visits: 52
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kwedberg/ssiscodereuseandcomplexcontrolflows.asp
Post #208835
Posted Tuesday, September 06, 2005 2:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 14, 2008 4:00 AM
Points: 63, Visits: 3

Nice article!

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

Post #217132
Posted Tuesday, September 06, 2005 2:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, November 10, 2013 11:52 AM
Points: 877, Visits: 188

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



Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Post #217135
Posted Tuesday, September 06, 2005 8:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 12, 2013 12:43 PM
Points: 7, Visits: 52
Thanks guys, glad you liked it!

Kristian
Post #217269
Posted Tuesday, September 06, 2005 11:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 17, 2006 10:06 AM
Points: 28, Visits: 1

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

Post #217336
Posted Tuesday, February 26, 2008 2:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 06, 2012 3:51 AM
Points: 4, Visits: 39
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?
Post #460553
Posted Wednesday, February 27, 2008 12:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 12, 2013 12:43 PM
Points: 7, Visits: 52
It's not clear from your post where you're running the select; in script task, in execute sql task, or in data flow?
Post #461202
Posted Wednesday, February 27, 2008 12:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 06, 2012 3:51 AM
Points: 4, Visits: 39
Sorry Kristian,
I have data flows developed that execute the SQL in OLE DB Source objects. That is where the Parameter is located.

Mitch
Post #461221
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse