Hi,
Ours is a product across multiple platforms and databases - Oracle 8.1.7 , 9i, SQL server on Solaris, AIX windows etc. I have to make sure that the DTS packages are maintainable. i.e I should not be maintaining multiple DTS packages - one for each environment.

The strategy I have used in DTS is defining the DTS connections as "other ODBC data source", namely "SOURCE1". SOURCE1 could inturn be any configured to any other ODBC drivers, oracle or SQL server.

Is this a right approach ?

"SQL server on Solaris" ????

Yes DTS packages should be as maintenance free as possible. That said it is
not always possible to split as easily. Different RDBMSs play slightly
differently to each other and that is where you can come unstuck.

It is relatively easy in DTS to simply change Source Servers/Catalogs if the
underlying table structure is common throughout. You can change all kinds
of properties in the Object Model and through Disconnected Edit. You can
set these at runtime.

If the Question is "Can I build a package that is infinitely dynamic i.e.
suck from different databases, different tables with differing structures
and move to different tables with different structures" then the answer is
"Yes" but with a lot of recreating and messing about.



--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Break It" <anonymous (AT) discussions (DOT) microsoft.com> wrote

packages are maintainable. i.e I should not be maintaining multiple DTS
packages - one for each environment.
to any other ODBC drivers, oracle or SQL server.

It was not supposed to be "SQL server on Solaris". Just wanted too give you an idea of the various combinations.

Can you decode your second paragraph a bit. Mainly
"You can change all kinds of properties in the Object Model and through Disconnected Edit. You can
set these at runtime."

Can you give me some pointers/links how to get this done?

Thanks in Advance

Yep

DTS has an object model and using that object model allows you to change
most if not all parts of the package DataSource server, Catalog, destination
filename etc etc etc.

Disconnected Edit is something that was added to DTS in SQL Server 2000
(Right click designer white space | Disconnected Edit). It will show you
the object model hierarchically and allow you to set properties to the
values of INI files, DB row values, constants etc. These are read in at
Runtime.

We have lots of examples of manipulating packages on www.SQLDTS.com


--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Break It" <anonymous (AT) discussions (DOT) microsoft.com> wrote