sql server dts: This is the second package I have had this trouble with. The first time I
only resolved the problem by recreating the package from the beginning. This
time, recreating the package does not help.

Here is my problem...

I change the catalog of a SQL connection object via script, retrieve some
value then loop back, change the catalog to a different database, retrieve
some, value, etc.

- All the looping works as expected.
- When I execute the steps manually, all values are retrieved from the
correct databases as expected.
- When I run the whole package, the steps loop as expected, but the database
connection refuses to change and I get only the value from the first
database over and over.

- I use msgboxes to display the catalog value of the connection and it shows
the correct database, however, it still uses the wrong one.

It seems I need to force the connection to close so it actually connects to
the new database.
Any ideas?

Jim

In message <uwLIfdO7DHA.3024@tk2msftngp13.phx.gbl>, Jim L
<jimL@integrationgroup.remove.com> writes
[quoted text, click to view]

Two things to watch-

If you change the connection DB, by default this is overridden because
the DataPump task and DDQ is using the three part name. Loose the first
part.

If a connection is open, then you can change the properties, but they
will not take affect on the "open" instance. In a loop, make sure that
the last task to use a connection has the "Close connection on
completion" option set to true. Right-click - Workflow - Properties -
any task to see the option.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org