sql server 2008 upgrade advisor - and upgrading

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

sql server 2008 upgrade advisor - and upgrading

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-09-01 : 06:06:42

Hi,

I'm running sql server 2008 upgrade advisor and have come across a couple issues I don't completely understand. I am getting 5 notices from SQL Server 2008 Upgrade Advisor under Data Transformation Services.

Fix Before : SQL Server 2000 Meta Data Services packages are not supported

Affected Objects:
Source Type: MSDB
Package Location: AZV1OL
Instances: 1

Fix Before: SQL Server 2000 DTS Designer components are required to edit DTS packages.

Affected Objects:
Source Type: MSDB
Package Location: AZV1OL
Instances: 1

Fix After: No DTS packages were found on the server that you selected to analyze

Advisory: SQL Server 2000 Data Transformation Services is deprecated

Advisory: Upgrade Advisor reports only the most recent version of each DTS Package

Also 3 for "SQL SERVER"

Fix Before: Full-text search has changed in SQL Server 2008

Advisory: Other Database Engine upgrade issues

After: Non-integer contstants are not allowed in the ORDER BY clause in 90 or later compatibility modes.

To me these look pretty generic, and I should be able to proceed. However on the first 2 MSDB affeced objects, I am not clear if its saying it has found these in my MSDB or not. I don't have any DTS packages as far as I know on the server. (Under SQL2005 ->Management->Legacy-> Data Transformation Services, it is empty.)

Just wondering if anybody can confirm?

Also, any thoughts on "upgrading" my databases to SQL2008, vs installing a new instance beside it, and restoring to it ?

Im thinking I would rather upgrade, but it also sounds more risky. I'll be doing a full backup of all my databases before.

Thanks for any opinions!
mike123

cary1309sqlteam
Starting Member

2 Posts

Posted - 2008-12-26 : 21:57:04

quote:
Originally posted by mike123

Hi,

I'm running sql server 2008 upgrade advisor and have come across a couple issues I don't completely understand. I am getting 5 notices from SQL Server 2008 Upgrade Advisor under Data Transformation Services.

Fix Before : SQL Server 2000 Meta Data Services packages are not supported

Affected Objects:
Source Type: MSDB
Package Location: AZV1OL
Instances: 1

Fix Before: SQL Server 2000 DTS Designer components are required to edit DTS packages.

Affected Objects:
Source Type: MSDB
Package Location: AZV1OL
Instances: 1

Fix After: No DTS packages were found on the server that you selected to analyze

Advisory: SQL Server 2000 Data Transformation Services is deprecated

Advisory: Upgrade Advisor reports only the most recent version of each DTS Package

Also 3 for "SQL SERVER"

Fix Before: Full-text search has changed in SQL Server 2008

Advisory: Other Database Engine upgrade issues

After: Non-integer contstants are not allowed in the ORDER BY clause in 90 or later compatibility modes.

To me these look pretty generic, and I should be able to proceed. However on the first 2 MSDB affeced objects, I am not clear if its saying it has found these in my MSDB or not. I don't have any DTS packages as far as I know on the server. (Under SQL2005 ->Management->Legacy-> Data Transformation Services, it is empty.)

Just wondering if anybody can confirm?

Also, any thoughts on "upgrading" my databases to SQL2008, vs installing a new instance beside it, and restoring to it ?

Im thinking I would rather upgrade, but it also sounds more risky. I'll be doing a full backup of all my databases before.

Thanks for any opinions!
mike123

cary1309sqlteam
Starting Member

2 Posts

Posted - 2008-12-26 : 21:58:00

quote:
Originally posted by mike123

Hi,

I'm running sql server 2008 upgrade advisor and have come across a couple issues I don't completely understand. I am getting 5 notices from SQL Server 2008 Upgrade Advisor under Data Transformation Services.

Fix Before : SQL Server 2000 Meta Data Services packages are not supported

Affected Objects:
Source Type: MSDB
Package Location: AZV1OL
Instances: 1

Fix Before: SQL Server 2000 DTS Designer components are required to edit DTS packages.

Affected Objects:
Source Type: MSDB
Package Location: AZV1OL
Instances: 1

Fix After: No DTS packages were found on the server that you selected to analyze

Advisory: SQL Server 2000 Data Transformation Services is deprecated

Advisory: Upgrade Advisor reports only the most recent version of each DTS Package

Also 3 for "SQL SERVER"

Fix Before: Full-text search has changed in SQL Server 2008

Advisory: Other Database Engine upgrade issues

After: Non-integer contstants are not allowed in the ORDER BY clause in 90 or later compatibility modes.

To me these look pretty generic, and I should be able to proceed. However on the first 2 MSDB affeced objects, I am not clear if its saying it has found these in my MSDB or not. I don't have any DTS packages as far as I know on the server. (Under SQL2005 ->Management->Legacy-> Data Transformation Services, it is empty.)

Just wondering if anybody can confirm?

Also, any thoughts on "upgrading" my databases to SQL2008, vs installing a new instance beside it, and restoring to it ?

Im thinking I would rather upgrade, but it also sounds more risky. I'll be doing a full backup of all my databases before.

Thanks for any opinions!
mike123

Hi. Did you get any responses to your post?

badercheema
Starting Member

1 Post

Posted - 2011-01-14 : 10:52:51

I am having the same issues...did you resolve this issue?

quote:
Originally posted by cary1309sqlteam

quote:
Originally posted by mike123

Hi,

I'm running sql server 2008 upgrade advisor and have come across a couple issues I don't completely understand. I am getting 5 notices from SQL Server 2008 Upgrade Advisor under Data Transformation Services.

Fix Before : SQL Server 2000 Meta Data Services packages are not supported

Affected Objects:
Source Type: MSDB
Package Location: AZV1OL
Instances: 1

Fix Before: SQL Server 2000 DTS Designer components are required to edit DTS packages.

Affected Objects:
Source Type: MSDB
Package Location: AZV1OL
Instances: 1

Fix After: No DTS packages were found on the server that you selected to analyze

Advisory: SQL Server 2000 Data Transformation Services is deprecated

Advisory: Upgrade Advisor reports only the most recent version of each DTS Package

Also 3 for "SQL SERVER"

Fix Before: Full-text search has changed in SQL Server 2008

Advisory: Other Database Engine upgrade issues

After: Non-integer contstants are not allowed in the ORDER BY clause in 90 or later compatibility modes.

To me these look pretty generic, and I should be able to proceed. However on the first 2 MSDB affeced objects, I am not clear if its saying it has found these in my MSDB or not. I don't have any DTS packages as far as I know on the server. (Under SQL2005 ->Management->Legacy-> Data Transformation Services, it is empty.)

Just wondering if anybody can confirm?

Also, any thoughts on "upgrading" my databases to SQL2008, vs installing a new instance beside it, and restoring to it ?

Im thinking I would rather upgrade, but it also sounds more risky. I'll be doing a full backup of all my databases before.

Thanks for any opinions!
mike123

anandkumar.guna
Starting Member

2 Posts

Posted - 2011-10-19 : 02:01:32

Hi All,
I am also facing the same issues. Could you please let me know asap because we are going to migrate 2005 to 2008.

Cheers,
Anandkumar.G

paultech
Yak Posting Veteran

79 Posts

Posted - 2011-10-19 : 08:47:33

The migration wizard will try to use an SSIS equivalent to a DTS task. Sometimes this works and sometimes it does not, as illustrated in our example. The Active X Script task in DTS is the same in SSIS. What is not the same is the way you manipulate the object model from within. The transform data task and the data flow task are similar in DTS and SSIS, and the wizard does an excellent job of migrating that structure along with connection managers.

There are tasks that have no equivalent. One that comes to mind is the dynamic properties task in DTS. It does not exist in SSIS, and the equivalent in SSIS would be to use configurations or expressions, which are now part of the engine and package architecture as opposed to specific tasks.

You may decide to leave DTS running alongside SSIS. We covered the runtime and management sides of DTS within SQL Server 2005, so if the migration wizard is not working for you or you lack test resources to complete such a project, this would be a viable option.

Darren Green is the founder of SQLDTS.com. After a brief experience with SQL Server 6.5 and BCP, he moved onto version 7.0 and the joys of DTS. He is a SQL Server MVP and can frequently be found in the Microsoft public newsgroups. In his spare time he is lead DBA for one of the top independent financial advisor portals in the U.K.

Allan Mitchell began working with SQL Server version 6.5. He is an MCSE on Windows 2000, an MCDBA and a Microsoft SQL Server MVP, and consults on DTS for an array of clients in the U.S. and Europe. He is the co-author of <A href="http://www.sqldts.com/?3001>Real-World SQL-DMO for SQL Serverand has been published in SQL Server Professional Magazine.

http://www.simple-talk.com/sql/ssis/dts-to-ssis-migration/

paultech
Yak Posting Veteran

79 Posts

Posted - 2011-10-19 : 09:43:00

Also try this ideas:

Setup SSIS with DTS 2000

On the migration of 2000 DTS to SSIS 2005/2008. There are many known issues in the migration of DTS 2000. It is recommended to execute the DTS inside SSIS 2005/2008 without migration. And if you need to edit a DTS in 2000 it will be better to recreate from scratch.

Known issues related to migration packages
SQL Server 2005 : http://msdn.microsoft.com/en-us/library/ms143462(SQL.90).aspx
SQL Server 2008 : http://msdn.microsoft.com/en-us/library/ms143462.aspx

Support for Data Transformation Services (DTS) in SQL Server 2008
http://msdn.microsoft.com/en-us/library/bb500440.aspx

What should I install for SSIS developers

You will find the answers in the documentation below:

Installing SQL Server Integration Services by Using Setup
SQL Server 2005 : http://msdn.microsoft.com/en-us/library/ms143510(SQL.90).aspx
SQL Server 2008 : http://msdn.microsoft.com/en-us/library/ms143510.aspx

"When you select Integration Services for installation, Setup also installs support for SQL Server 2000 Data Transformation Services (DTS) packages, including the DTS runtime and DTS package enumeration in SQL Server Management Studio. Run-time support has been updated to allow DTS packages to access SQL Server 2005 data sources. If you are not installing Integration Services, but you need support for DTS packages, then you need to make sure that Legacy Components is selected on the Feature Selection page."

paul Tech

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources