FIX: Properties Dialog Box of a Transform Data Task Takes a Long Time to Open (303822)

SYMPTOMS

The Transform Data Task Properties dialog box in the Data Transformation Services (DTS) Designer may take several minutes to open, depending upon these factors:

The number of tables and views in the source database.
The naming convention of the tables and views.
The collation or sort order of the source database.
The collation or sort order of the locale used by the client account.
The amount of RAM and CPU speed of the client computer that is running the DTS Designer.

While you wait for the Transform Data Task Properties dialog box to open, in the Task Manager you can see that the Mmc.exe process consumes a lot of memory and also see 100% CPU usage on the client computer.

CAUSE

To fill the Table/View drop-down combo box, in the Transform Data Task Properties dialog box, the DTS Designer uses the standard Microsoft Windows API, SendDlgItemMessage, with the CB_ADDSTRING message. Because the drop-down combo box was created with the CBS_SORT style, all the entries are sorted according to the locale of the current user.

If the sort order of the database is the same as the sort order on the client computer, and the names of the views are all greater than the names of the tables, everything works quickly because SendDlgItemMessage only has to append to the end of the list.

However, if the two sort orders differ or the names of the views have to be sorted in between the names of the tables, SendDlgItemMessage must insert every new item into the correct place.

The Windows SendDlgItemMessage API is not designed to sort tens of thousands of entries, so the sort may take a long time.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

Hotfix

NOTE: The following hotfix was created prior to Microsoft SQL Server 2000 Service Pack 2.

The English version of this fix should have the following file attributes or later:

   Date          Time     Version    Size     File name    Platform
   ----------------------------------------------------------------
   29-Jun-2001   15:49    8.00.405   455232  Ssqlgui.dll   Intel

NOTE: Due to file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.

WORKAROUND

To work around this problem, use these steps:

Export only the tables and views that are needed by your Data Transformation Services (DTS) package to a new database.
Design and test your DTS package against the new database.
Point the DTS package back to the original database before deployment.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 2000. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

242391 INF: DTS Package Development, Deployment and Performance

Please refer to the documentation of the combo box message "CB_ADDSTRING" in the Platform SDK for more details.