August 9, 2005 at 11:52 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kwedberg/ssiscodereuseandcomplexcontrolflows.asp
September 6, 2005 at 2:36 am
Nice article!
Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden
September 6, 2005 at 2:38 am
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
September 6, 2005 at 8:44 am
Thanks guys, glad you liked it!
Kristian
September 6, 2005 at 11:23 am
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
February 26, 2008 at 2:08 pm
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?
February 27, 2008 at 12:22 pm
It's not clear from your post where you're running the select; in script task, in execute sql task, or in data flow?
February 27, 2008 at 12:47 pm
Sorry Kristian,
I have data flows developed that execute the SQL in OLE DB Source objects. That is where the Parameter is located.
Mitch
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply