StarQuest Technical Documents

Considerations for using StarSQL (64-bit) with SQL Server (64-bit)

Last Update: 03 October 2014
Product: StarSQL (64-bit)
Version: 5.5x
Article ID: SQV00SQ048

Abstract

StarSQL (64-bit) can be used to access DB2 data from 64-bit SQL Server through linked servers and from SQL Server Integration Services packages created with the Import/Export Wizard or with the Business Intelligence Development Studio. This document addresses considerations when using 64-bit versions of StarSQL and SQL Server.

If you will be working with both 32-bit and 64-bit applications, we recommend that you install both 32-bit and 64-bit versions of StarSQL and either configure both 32-bit and 64-bit data sources (DSNs) with identical names or use DSN-less connection strings. This allows you to work in both the design and execution environments of SSIS without worrying about the bit width of the application.

If only the 64-bit version of StarSQL is to be installed, then care should be taken to avoid using 32-bit applications that require a 32-bit driver, as highlighted below in red.

Solution

Linked Servers

Data Transformation Services 2000 runtime (DTS)

SQL Server Integration Services ( SSIS) Import/Export Wizard (dtswizard.exe)

Business Intelligence Development Studio (BIDS) -SSIS Design

dtexecui - SSIS Package Execution (GUI)

dtexec.exe - SSIS Package Execution (command line)

SQL Server Agent - SSIS Package Execution

Linked Servers

In order to use SQL Server (64-bit) linked servers with StarSQL (64-bit), you will need the 64-bit OLEDB Provider for ODBC (MSDASQL), which is provided as a special download for Windows Server 2003 from the Microsoft Download Center, http://www.microsoft.com/downloads, and is included in all later Windows operating systems.

Create a SQL Server 64-bit linked server as you would a SQL Server 32-bit linked server, supplying the name of a 64-bit ODBC data source. Refer to the StarQuest Technical Document How to Use a Microsoft SQL Server Linked Server to Access DB2 with StarSQL for instructions.

Data Transformation Services 2000 runtime (DTS)

You can run DTS packages created with SQL Server 2000 by installing the Data Transformation Services 2000 runtime (DTS) from the SQL Server 2005 Backward Compatibility package. This support is 32-bit only.

Note: DTS is deprecated in SQL Server 2008. Microsoft recommends using SQL Server Integration Services (SSIS) 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) Import/Export Wizard (dtswizard.exe)

Launching the 64-bit DTS Wizard

SQL Server (64-bit) installs both the 32-bit and 64-bit versions of the Import/Export Wizard (DTSWizard.exe). If you use SQL Server Management Studio and select Tasks -> Import/Export, the 32-bit version of the wizard is launched. If you use Business Intelligence Development Studio (BIDS) and choose "Import/Export Wizard" from the drop down for Packages, the 32-bit version of the wizard also is used. To use StarSQL (64-bit) data sources with the 64-bit version of the Import/Export Wizard, select the shortcut "Import and Export Data (64-bit)" shortcut in the SQL Server program group, or run DTSWizard.exe from the directory \Program Files\Microsoft SQL Server\100\DTS\Binn

Business Intelligence Development Studio (BIDS) - SSIS design

The Business Intelligence Development Studio (BIDS), used to design and debug SSIS packages, is based on Visual Studio. It is a 32-bit application and displays only 32-bit data providers when viewing a connection under Connection Managers. However, the SSIS package can use the 64-bit version of StarSQL during the execution of the package; the choice of whether the 32-bit or 64-bit runtime is used when executing a package from within BIDS is controlled by the Run64bitRuntime flag in the Project/Debug properties; the default setting is 64-bit.

Additional design considerations:

• If your package specifies a connection using DSN=, then it will be portable to both 32-bit and 64-bit environments, as long as an ODBC data source by the appropriate name and bit width exists on the target system.
• If your package specifies a connection using a DSN-less connection string, then it can be deployed to other machines without having to configure ODBC data sources on all the target machines. However, it will be either 32-bit or 64-bit specific, as part of the connection string contains either Driver={StarSQL 32} or Driver={StarSQL (64-bit)}.

dtexecui - SSIS Package Execution (GUI)

When you select Execute Package Utility from the Integration Services program group of SQL Server, the GUI application dtexecui.exe is invoked. dtexecui is a 32-bit application and uses the 32-bit version of StarSQL.

dtexecui.exe is also invoked when you use SQL Server Management Studio to connect to Integration Services of a local or remote SQL Server database. You can view the packages that are stored in the SQL Server database by expanding Stored Packages/MSDB. If you right click on a package and select "Execute", then dtexecui.exe (a 32-bit application) is run on the local machine. In this scenario, the remote SQL Server database is being used only as a storage location for the packages.

dtexec.exe - SSIS Package Execution (command line)

The command line version of dtexec.exe is supplied as both 32-bit and 64-bit versions (e.g. in C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn and C:\Program Files\Microsoft SQL Server\100\DTS\Binn). Which version is used by default is dependent on your PATH environment variable. The Microsoft documentation states that the 32-bit version will be the default after installation of SQL Server; however, in our experience we have found that the 64-bit version is the default.

To display the version (including bit width), execute dtexec without any parameters:

C:\> dtexec
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 64-bit

SQL Server Agent - SSIS Package Execution

When you configure a SQL Server Agent job with a job step type of SQL Server Integration Services Package, the job invokes the dtexec utility. The 64-bit version of dtexec.exe is used unless you select Use 32 bit runtime on the Execution options tab of the New Job Step dialog box.

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/2012 SSIS Package