Welcome to the world (new face) of Knowledge Sharing Network.
To track the older blog posts refer to our historical knowledge sharing site  and you will find this as your destination for SQL Server knowledge pool.

Follow SQLMaster on Twitter SqlServer-QA.net - Knowledge Sharing Network (@sqlmaster)

February 2009 - Posts - SQL Server Knowledge Sharing Network (@sqlmaster)

February 2009 - Posts

Error: SQL Server 2000 DTS Designer components are required to edit DTS packages. (Microsoft.SqlServer.DtsObjectExplorerUI)

The error message text (partial) on the subject will be familiar for the DTS/SSIS users in SQL Server.

On the background of the issue is that when you attempt of open a DTS package for editing from SQL Server 2008 Client machine, I have seen questions within forums that few users are unable to locate the SQL Server 2000 DTS Designer Components for SQL Server 2008 version and only SQL Server 2005 are available to download. More to the extension of the above error:

TITLE: Object Explorer

SQL Server 2000 DTS Designer components are required to edit DTS packages.
Install the special Web download, "SQL Server 2000 DTS Designer Components"
to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)

Recently I have gone through this error and resolved the issue on a SQL Server instance that has been installed for use on Windows Server 2003 & 2008 X64 enterprise edition. To resolve the error a fair amount of background of process is required for you to understand.

Thanks to the Microsoft Documentation team that they have done a good job in documenting the process and procedures within the Books Onine (SQL Server 2005 & 2008 versions). Such in action the instructions for installing the DTS Design components can be found from this BOL143755 link (as I don't want to refer then again it is best for you to go through).  So as per the documentation process I have copied the relevent .DLL and .RLL files from the 80 (SQL 2000) version to 100 (SQL 2008) directories and then tried to open the DTS packages from the same client machine.

Again a new error has been popped up stating "The DTS host failed to save the package properly". I vaguley remember that one of the KB article refers that this error whent he following conditions are met:

  • The SQL Server 2000 DTS package was last saved from an instance of SQL Server 2000 that is running on a Windows 2000-based computer.
  • The SQL Server 2000 DTS package is stored in a structure storage (.dts) file.

Not only within SQL Server 2005 vresion this problem also occurs because of a compatibility issue with how icons are managed between DTS Designer in SQL Server 2000 and DTS Designer in SQL Server 2005. Talking about backward compatibility of the tools I wanted to check whether the SQL Server 2005 Backward Compatibility Components are installed or not, they aren't. For your reference you can download this SQL Server 2005 Backward Compatibility
 from here and FYI these are updated for the SQL Server 2008 compatibility too.

I also remembered about a discussion that I have had with the PSS engineer in the past that SQL Server 2008 does not include support for "64-bit design-time or run-time" support for DTS packages. Say (you may be aware by this time) on a 64-bit computer, DTS packages, and Integration Services (SSIS) packages that run DTS packages, can run only in 32-bit mode. To run DTS packages in 32-bit mode, you have to install and use the 32-bit version of the dtexec utility. This dtexec.exe is needed if you haven't installed this 32-bit version of the utility during the client Tools or Business Intelligence Development Studio installation at setup process.

Similar to above there is also no 32-bit design-time or run-time support for DTS packages on Itanium-based operating systems. Due to this design constraint the user cannot create, view, modify, or run DTS packages on Itanium-based operating systems too. To avoid either you can download that dtexec utility from the above download link or refer to this link about installing the 32-bit version of of the Integration Services runtime utility. Also it is worth to refer about how to: Run a Package Using the DTExec Utility and finally a note about scheduling these packages. Say if you use SQL Server Agent to run the utility, SQL Server Agent automatically uses the 64-bit version of the utility. SQL Server Agent uses the registry, not the PATH environment variable, to locate the correct executable for the utility. To do this BOL refers (again) that:

To ensure that you run the 64-bit version of the utility at the command prompt, you can take one of the following actions:

  • Open a Command Prompt window, change to the directory that contains the 64-bit version of the utility (<drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn), and then run the utility from that location.
  • At the command prompt, run the utility by entering the full path (<drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn) to the 64-bit version of the utility.
  • Permanently change the order of the paths in the PATH environment variable by placing the 64-bit path (<drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn) before the 32-bit path (<drive>:\ Program Files(x86)\Microsoft SQL Server\100\DTS\Binn) in the variable.

So after all the checkout on the tools for backward compatibility we found that the main issue occurred due to the package_path definition within that DTS package. Documentation refers that package_path or filespec arguments of the /SQL, /DTS, or /FILE options must be enclosed in quotation marks if the path or file name contains a space. If the argument is not enclosed in quotation marks, the argument cannot contain white space. To close up this topic it is a must reference for every SQL user to go about SQL Server Backward Compatibility feature installs the DTS runtime for SQL Server 2008, however it works only on 32-bit platform. Best practices dictates that you need to migrate DTS packages to SSIS environment as and when time is available, as a final reference on this migration process refer to this Migrating DTS Packages to Integration Services link and get the SQL Server 2000 DTS Designer components too.


Help Microsoft BI Development team to Build next version of AdventureWorks databases

Until SQL Server 2000 you may be familiar with pubs & northwind sample databases that were available along with SQL Server installation and since release of SQL 2005 the scenario has been changed for Sample databases.The version of AdventureWorks that is distributed with SQL Server 2005 is different from earlier versions AdventureWorks. If you already have a previous version of AdventureWorks, you should install the most recent version so that the queries in other ReportViewer walkthroughs work as expected. 

To explain briefly and learn more about using the AdventureWorks sample database, you can search for "AdventureWorks Sample OLTP Database" in SQL Server 2005 Books Online. To proceed further Microsoft Product Development team are planning to release next version of  AdventureWorks family of databases and community help will be a great help with their input.

So if you have used the AdventureWorks sample databases it would be wonderful if you would go to DBSamples site and file some work items on the Issue Tracker tab for anything you would like to see change.  Also, a brief note from the team that  if there are things you don't want changed by all means mention that in a work item as well. 

Also if you haven't had a chance to work or know about AdventureWorks sample databases it would be great if you could take a look at them now to feedback on the changes you would like to see. Still there is something else exciting for the users to report, if you see existing work items that you feel are important it would be very helpful if you could vote on them. 

MSFT Product Developmen Team promising to look at the feedback received from the community starting February 11th 2009 and you will agree that the input received from the communith will guide/help the team to decide on what changes they will be making.



January 2009 update for SQL Server 2008 Books Online aka BOL

Here comes another update that you need to concentrate, a newest update to the SQL Server 2008 Books OnLine.

MSDN site specifically refers that the following categories have had new topics added or existing topics updated with significant changes in the 23 January 2009:

Getting Started: New and Updated Topics (23 January 2009)

Integration Services: New and Updated Topics (23 January 2009)

Database Engine: New and Updated Topics (23 January 2009)

Reporting Services: New and Updated Topics (23 January 2009)

As you would see the same if you have downloaded this SQL2008BOL_January2009-Update within your local copies.

Ok, here is I was thinking why there are changes to the documentation too as you have seen many Cumulative Updates packages to come. On their own words SQL Server documentation team confirms that: This update is a quarterly rollup where we have also published it in the Microsoft Download Center, so keep watching this space!