Ninjamurai

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.

1. SQL Server 2005 can not read DTS Packages saved in the MSDB database
2. DTS can not read SSIS packages ( kind of a duh! moment there )
3. SSIS can not save files into the DTS Package format
4. 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
5. DTS Packages can’t have a space for the first or the last character of their name KB917420
6. DTS Packages should not use ODBC for it is unreliably supported, though mostly fixed in SQL Server 2005 Service Pack 1 KB915189
   
7. 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.
8. 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.
9. 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.

Scott has a great document on proposed naming conventions for BizTalk artifacts.

Go check it out The BizTalk 2006 Naming Conventions

A decent tutorial on creating an MSI package in BizTalk Adminstration Console. In short, make sure ALL assemblies which are required are in the GAC and redeployed to the target GAC or bad things can happen. Also, make sure you set it to overwrite the assemblies currently there or your dependancies will fail. Biztalk does not check this in every situation and can make mistakes as well.
 

The Biztalk Deployment Framework

This is a heck of a tool and can really help to make the deployment process easier. Developed by Scott Colestock, it is quite the well thought of piece of engineering and has now been updated for BizTalk 2006. Woot.

Biztalk from your Vista development station can be an interesting and sometimes frustrating experience. Here are some bookmarks to articles that might help with this process.

From Microsoft:
Biztalk configuration or installation because of CAB file issues KB942181
Biztalk does not start correctly under Vista. Set delayed start for the service as it depends on ESS ( Enterprise Single Sign-on ) KB942284
Many tools in BizTalk require elevated permissions so “Run As Administrator”

Sometimes Biztalk can get out of sync when deploying. It will just randomly skip steps in redeployment leaving your deployment in a random state. Basically, when this happens a manual uninstall of GAC installed assemblies is necessary. 

One way to solve several deployment issues if you are deploying from Visual Studio is to run Visual Studio as an Administrator. Remember, just being logged in as a user in the admin group doesn’t count. You need to physically right click on VS and “Run as Administrator”.

While I am studying for my BizTalk certification, OUCH!, I thought y’all might benefit from some of the links I have found. I don’t know if it will be anything Earth-shattering, but could have some interesting stuff.

The Official BizTalk Team Blog at Microsoft
Steven W. Thomas’ Blog - 100% Pure BizTalk
Jon Flander’s BizTalk and Windows Workflow Foundation
Nilay Parikh’s Blog from London on BizTalk

The Microsoft MCTS site for BizTalk Server 2006
Free Demo Exams from Transcender 

Be careful looking for Braindumps on this exam. The real Braindump site has none and there are a ton of wannabes who also happen to love spreading bad stuff to your box.