StarQuest Technical Documents
Considerations for using StarSQL (64-bit) with SQL Server 2005/2008
Last Update: 04 February 2009
Product: StarSQL (64-bit)
Version: 5.5x
Article ID: SQV00SQ048
Abstract
StarSQL (64-bit) can be used to access DB2 data from SQL Server 2005/2008 (64-bit) through linked servers and from SQL Server Integration Services packages created with the DTS wizard or with the Business Intelligence Development Studio. This document addresses considerations when using StarSQL (64-bit) with SQL Server 2005/2008 (64-bit).
Solution
Data Transformation Wizard (DTS)
SQL Server Integration Services (SSIS)
Linked Servers
In order to use SQL Server 2005/2008 (64-bit) linked servers with StarSQL (64-bit), you will need the 64-bit OLEDB Provider for ODBC (MSDASQL), which is included in Windows Server 2008 and Windows Vista SP1, and provided as a special download for Windows Server 2003 from the Microsoft Download Center, http://www.microsoft.com/downloads.
Create a SQL Server 2005/2008 64-bit linked server as you would a SQL Server 2005/2008 32-bit linked server. Refer to the StarQuest Technical Document How to Use a Microsoft SQL Server Linked Server to Access DB2 with StarSQL for instructions.
Data Transformation Wizard (DTS)
Launching the 64-bit DTS Wizard
SQL Server 2005/2008 (64-bit) installs both the 32-bit and 64-bit versions of the DTS wizard (DTSWizard.exe). The version launched from the SQL Server Management Studio when selecing Tasks -> Import/Export is the 32-bit version. To use StarSQL (64-bit) data sources with DTS, run the 64-bit DTS Wizard (DTSWizard.exe), which is typically located in the directory C:\Program Files\Microsoft SQL Server\100\DTS\Binn.
Note: DTS is deprecated in SQL Server 2008. Although the DTS wizard is installed and is fully functional in SQL Server 2008, Microsoft recommends using Integration Services as a replacement for DTS.
Additional References
Deprecated Integration Services Features in SQL Server 2008, SQL Server 2008 Books Online (January 2009)
Data Transformation Services (DTS), SQL Server 2008 Books Online (January 2009)
SQL Server Integration Services (SSIS)
The Business Intelligence Development Studio (BIDS), used to design SSIS packages, is a 32-bit application and displays only 32-bit data providers. However, the SSIS package can use the 64-bit version of StarSQL during the execution of the package.
You will need to install both 32-bit and 64-bit versions of StarSQL and configure both 32-bit and 64-bit data sources (DSNs) with identical names. The SSIS runtime engine will select the appropriate version of the driver to use, by default this will be the 64-bit provider.
Additional References
64-bit Considerations for Integration Services, SQL Server 2008 Books Online (January 2009)
How to Use a StarSQL Data Source in a SQL Server 2005/2008 SSIS Package
DISCLAIMER
The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization. The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.