DTS To SSIS Migration: Unable To Cast COM Object Issue
I need to migrate the exact package from SQL Server 2000 DTS to SQL Server 2008 SSIS. This particular package is OK to migrate using the “Migrate DTS 2000 Package” Wizard since I don’t have those complicated tasks that won’t simply migrate.
So I right-click the wizard but when I run it I am getting a “Unable to cast COM object…” error. More related errors will show up if you skip that error dialog.
When the issue involves “object” issues, chances are you are missing some needed libraries for the program to run. And that is the case in this particular issue. I am missing the Data Transformation Services 2000 Runtime. Please take note that this service is deprecated (Check info here).
Some forums (ok, fora) suggest that you install the DTS Runtime from the SQL Server 2000 installation files. If you do not have access to the installation files, you can download a feature pack from Microsoft that includes the runtime. For this case, you particularly need to download the ” Microsoft SQL Server 2005 Backward Compatibility Components”. Although the title mentions the 2005 version, it is also compatible with SQL Server 2008 SSIS. You can download the package from this link.