May14
Supporting DTS in SQL Server 2005
Supporting DTS packages in SSIS ( SQL Server Integration Services ) is a daunting task for many. Let’s look at what you need to get the job done and get some good tutorials on the subject.
Firstly, we need to make sure we can run DTS packages from SQL Server 2005. Since DTS and SSIS are two separate products, we should be able to run both at the same time. Also, if all else fails, one can always run an instance of SQL Server 2000 along with an instance of SQL Server 2005.
NOTE: These instructions do NOT cover SQL Server Express!
In order to make sure DTS packages can run we need to either install Integration Services to SQL Server 2005 OR we can install Legacy Components from the Feature Selection when you do your 2005 install. You can also add this later by rerunning the installer and selecting it then.
64bit versions of SQL Server 2005 do NOT support running 32bit DTS packages.
Editing and modifing a DTS package requires the Microsoft SQL Server 2000 DTS Designer Components which are downloaded from Microsoft. These are not included with SQL Server 2005. Also, there are different versions of these so make sure to download the newest one.
SOURCE: Ensuring Support for Data Transformation Services
Even with this installed, there are several issues you may run into.
- SQL Server 2005 can not read DTS Packages saved in the MSDB database
- DTS can not read SSIS packages ( kind of a duh! moment there )
- SSIS can not save files into the DTS Package format
- Editing may fail on certain packages due to a bug in the way the rendering engine for the development tool does its job. Fixes are not always possible, but here is the knowledge base article KB917406
- DTS Packages can’t have a space for the first or the last character of their name KB917420
- DTS Packages should not use ODBC for it is unreliably supported, though mostly fixed in SQL Server 2005 Service Pack 1 KB915189
- DTS Package Designer fails with “dtsui.dll failed to register. HRESULT -2147024770″, this is because you need to install the proper MDAC ( Microsoft Data Access Components ). The correct version you need is the Microsoft Data Access Components (MDAC) 2.6 Service Pack 2 (SP2) or a later version. FYI: The newest version is currently 2.8 something. You can get it here.
- If it says can’t find dtsrun.exe usually that is because the Legacy Components weren’t installed. For info on getting that fixed go to KB928324.
- On BizTalk, EDI packages may fail if you do not remove the old EDI packages on a reconfigure KB939549.
We will continue with the HOW to migrate in part 2 of this post.
