SQLServerCentral / Discuss Content Posted by Kristian Wedberg / Article Discussions / Article Discussions by Author / SSIS - Code Reuse and Complex Control Flows / Latest Posts

RE: SSIS - Code Reuse and Complex Control Flows

lucasm-630215 — Wed, 27 Feb 2008 12:47:50 GMT

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

RE: SSIS - Code Reuse and Complex Control Flows

Kristian Wedberg — Wed, 27 Feb 2008 12:22:49 GMT

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

RE: SSIS - Code Reuse and Complex Control Flows

lucasm-630215 — Tue, 26 Feb 2008 14:08:51 GMT

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?

RE: SSIS - Code Reuse and Complex Control Flows

AndreQ1 — Tue, 06 Sep 2005 11:23:00 GMT

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

RE: SSIS - Code Reuse and Complex Control Flows

Kristian Wedberg — Tue, 06 Sep 2005 08:44:00 GMT

Thanks guys, glad you liked it! Kristian

RE: SSIS - Code Reuse and Complex Control Flows

Jamie Thomson — Tue, 06 Sep 2005 02:38:00 GMT

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

RE: SSIS - Code Reuse and Complex Control Flows

Kirk Haselden — Tue, 06 Sep 2005 02:36:00 GMT

Nice article!

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

SSIS - Code Reuse and Complex Control Flows

Kristian Wedberg — Tue, 09 Aug 2005 11:52:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kwedberg/ssiscodereuseandcomplexcontrolflows.asp