We are planning to upgrade to SQL Server 2005. We use lots of DTS packages. I
undestand that SQL Server 2005 can run DTS, but will I be able to maintain
then (add new ones and update existing ones).

If this is not possble, can someone recommend a way to get around this --
besides re-building them all in SSIS.

Thanks,
Craig

I have recently upgraded one of my DEV SQL 2k servers to SQL 2005. I had
quite a few DTS packages in effect and each of them have come over without a
problem. The largest issue that I have had is that some of them that use the
copy server objects task have not worked properly but the rest of the
operations seem to be working properly.

You can manage the existing DTS packages after upgrade as long as you
install the add-in from the November 2005 feature pack named "Microsoft SQL
Server 2000 DTS Designer Components". When I initially installed these
components I continued to receive an error message when I tried to open a DTS
package for editing until I uninstalled and reinstalled both the "Microsoft
SQL Server 2000 DTS Designer Components" and the "Microsoft SQL Server 2005
Backward Compatibility Components". After uninstalling and reinstalling both
of those packages I was finally able to manage DTS packages. Both add-ins
can be downloaded from here:
http://www.microsoft.com/downloads/d...displaylang=en

Creating a new DTS package is something that I have only briefly tested.
The only way that I have done this so far is to open an existing package and
then choose the "save as" option under the "Package" menu. This has allowed
me to create new DTS packages that seem to work appropriately.

As for SSIS packaging I am currently in the process of converting each of
the DTS packages to SSIS and testing them. This is a very simple process
that simply requires a right click on the package in "Microsoft SQL Server
Management Studio" and selecting the "Migrate" option. After doing this, and
a delay of about 20 seconds, a wizard pulls up that allows you to simply
convert your DTS packages to SSIS. I have had a fairly good success rate so
far with the only complication in the task of copying of server objects from
a SQL 2k server to the SQL 2005 server.

I do hope that this will help you somewhat.

-Mabuti

VERY helpful -- thanks !

Gald I could help.

Also in case you do upgrade I found a solution to my issue with the copy
server objects in SSIS.

================================================== ==
Run this stored proc to set the compatability level to 90:

EXEC sp_dbcmptlevel DatabaseName, 90

The SQL 2000 databases that were brought up to SQL 2005 during the upgrade
still have the compatability level of 80. Changing this to the new level of
90 solves the issue and allows the SSIS packages to run properly on that
database.
================================================== ==

-Mabuti

Hello Craig,

With the DTS runtime installed then sure you can run them to your heart's
content using DTSRun. There is a feature pack which allows you to edit DTS
packages through Management studio that you can get through the MS website.
To create a new package you could edit an existing one and save it as something
different. If you have 2K5 now I cannot see a reason I would want to create
new DTS packages to be run on the 2K5 server. Anything new I created for
that server would be SSIS.


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

what are the clicks and strokes to edit a dts package?

"Mabuti" <Mabuti (AT) discussions (DOT) microsoft.com> wrote