LEgacy DTS package migration - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Administration

LEgacy DTS package migration

Author

Topic

itsonlyme4
Posting Yak Master

109 Posts

Posted - 2015-01-27 : 11:27:15

We are standing up a new SQL Server 2008 environment to replace an existing SQL Server 2005 environment.

We first attempted to just bring the legacy DTS packages forward into SQL Server 2008 by following this article

http://technet.microsoft.com/en-us/library/ms143755(v=sql.105).aspx

According the to article, we installed the following:
SQLServer2005_BC.msi
SQLServer2005_DTS.msi

Then copied the following files:

1.Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from
C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn folder to
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE folder.

2.Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from
C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\Resources\1033 folder to
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033 folder.

We also set all user Databases to 2008 compatibility mode like this:
ALTER DATABASE [Admin] SET COMPATIBILITY_LEVEL = 100

We were able to export the packages from the 2005 Server and import them into the 2008 Server and open the packages but we ran into permissions and compatibility issues when trying to execute some of the packages.

Our developers are now looking at migrating the packages to SSIS instead.

That is going fairly well but they are running into the following errors executing some of the packages:

Error: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.
at DTS.PackageClass.Execute()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread();

and

"the query uses non-ANSI outer-join operators. To run this query without modification, set the compatibility_level for the current Database to 80........

Looking for some guidance by anyone who has experience migrating DTS packages to SSIS.

Should I set the User Databases in question back to 80 compatibility? If I do that can they be brought back to 90 or 100 compatibility if needed?

Any other suggestions would be greatly appreciated!

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-28 : 09:25:41

Just open the packages and double click on the connections and click ok and save. This will validate all the packages and will be saved with 2008 version.

Regards
Viggneshwar A

itsonlyme4
Posting Yak Master

109 Posts

Posted - 2015-01-28 : 09:33:40

so you are suggesting to open the packages and double click on the connections and click ok and save to validate all the packages and will be saved with 2008 version and THEN migrate to SSIS ?

itsonlyme4
Posting Yak Master

109 Posts

Posted - 2015-01-28 : 11:00:37

The packages that we are having problems with do not have a connections step. We are calling an INI file to set the connections and credentials...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources