sql server dts: Multiple loops in one package? -- sql server dts

RE: Multiple loops in one package?

petery NO[at]SPAM online.microsoft.com
8/31/2005 12:00:00 AM

Hello Peter,

Based on my scope, this is not currently support in SQL 2000 DTS package.
You may want to check if SQL 2005 can meet your requirement because it has
enhancement on this respect.

For more information on this, please go to SQL 2005 newsgroup:

http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005&sl
cid=us

Thanks & Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: Peter A. Schott <paschott@newsgroup.nospam>
| Subject: Multiple loops in one package?
| Date: Tue, 30 Aug 2005 11:30:55 -0500
| Message-ID: <v729h1dq10tmpqho1i9e4g275gn5k7t0i3@4ax.com>
| X-Newsreader: Forte Agent 3.0/32.763
| MIME-Version: 1.0
| Content-Type: text/plain; charset=us-ascii
| Content-Transfer-Encoding: 7bit
| Newsgroups: microsoft.public.sqlserver.dts
| NNTP-Posting-Host: 216.178.160.231
| Lines: 1
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.dts:14435
| X-Tomcat-NG: microsoft.public.sqlserver.dts
|
| I know how to loop through one set, but wasn't sure if there was an easy
way to
| loop over two variables, something like:
|
| Get list of servers
| Get next server from Server recordset --a
| For Each Server
| Get List of Databases
| Get next database from DB Recordset --b
| For Each Database on server
| Do Stuff
| Loop Databases (back to b)
|
| Loop Servers (back to a)
| Exit
|
|
| I know I can do one or the other without too much trouble, but has anyone
done
| this successfully? Do I just need to point the DB loop to B with a
success to
| Loop Servers which will then take me back to a?
|
| Thanks.
|

Re: Multiple loops in one package?

Peter A. Schott
8/31/2005 1:20:22 AM

Thanks for the quick response, Peter. This does work in SSIS. I've actually
got this doing exactly what I needed it to. I was kind of wondering if it were
possible in DTS 2000 as that would allow me to easily deploy to current and
not-yet-upgraded systems in the future. We're trying to make a generic (and
basic) DB Size Trending system that will pull in everything. I know I can make
things loop with some ActiveX magic, I just wasn't sure about nested loops. I
can probably mimic it with an Exec Package task - just not the cleanest way to
handle that. :-(

Thanks again.

-Pete Schott

[quoted text, click to view]

Re: Multiple loops in one package?

Darren Green
8/31/2005 11:32:09 AM

You can loop in DTS, by manipulating the workflow, and it works fine as you
probably know. Nesting loops is of course possible as well, just takes a bit
more brain power to work it all out. However I have seen perfectly designed
systems just fail when the workflow manipulation became to complex. I
suggest you split this into two packages, one for the server loop, and one
for the databases. Easier to code, manage and more reliable I expect.

I would also suggest that why not just loop servers and perform the work all
on one connection, enumerating the databases servers side. It will be
faster, especially for remote servers, and simpler.

--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.