Installing SQL Server Integration Services on 64-bit Computers

I had tough time working with Integration Services (SSIS) on one of the 64 bit machine that will be used for a 24/7 based application. So here goes my ranting and things you need to take care when having installation of SQL Server 2005 on 64 bit platform, specially within ETL processes completion.

So at first, when you install SQL Server 2005 and select both Integration Services and Business Intelligence Development Studio, as this it to ensure to have both 32 & 64 bit type of components installed and bear in mind there are no 64 bit tools available and you can make use of available 32 bit tools against 64 bit server instance. So with above it will install all available 32-bit and 64-bit design-time and run-time Integration Services features are installed, together with support for SQL Server 2000 DTS packages. By default all the binaries for SQL Server will be installed under the Program Files directory, and for 32-bit features are installed separately under the Program Files (x86) directory.

So talking about BIDS (Development Studio) which is a 32 bit application for SSIS/SSAS/SSRS type of programs is not supported on a Itanium 64 bit platform and will not be installed altogether. So you have to install a seperate component for DTS aka IS tools that have 64-bit versions are dtexec.exe, dtutil.exe, and the SQL Server Import and Export Wizard, DTSWizard.exe from Microsoft Downloads site.

Just a bit of advice on developing or testing 32 bit application on a 64 bit computer, always remember that this 32-bit tool is running packages in 32-bit mode and in order to complete your 64 bit text you should have 64-bit mode by using the 64-bit version of dtexec.exe before you deploy or schedule them on a production server. This brings up another issue of having SQL Server Message Queue task, Books Online refers that : if you have installed 64-bit SQL Server on a 64-bit computer, you can use the Message Queue task only in packages running in 64-bit mode; you cannot use the Message Queue task in packages running in 32-bit mode. So this will leave you an option with having 32 bit support on Message task and not with 64 bit.

Still I would like to quote the reference from Updated Books Online about limitations on the packages during design & run time. As usual you will not be able to design packages within BI Development Studio on Itanium-based operating systems, as said above. This still applies to the debug of scripts using the Scripts task in SSIS on a 64 bit platform. If you have got 64-bit provider then ensure to configure the connection manager where 32 bit tools are installed, this will not affect execution of the package in 64-bit mode in the development environment. Because the 32-bit and 64-bit versions of a provider have the same ID, the runtime selects the appropriate version to use based on the value of the Run64BitRuntime project property, which by default is True.

For RunTime gotchas, by default you will not be able to execute the package that will have Script Tasks or Script components. They must be recompiled under the 64 bit platform of SQL instance as the value of the PreCompile property is True for both the Script task and the Script component. Make sure you have downloaded the 64 bit .NET framework data providers including the OLEDB providers that are specific to 64 bit only. By default again the SQL Server 2000 DTS runtime is not available within 64-bit version.

Very frustating isn't it, this is where Microsoft compels the users to go towards SSIS when you have magic of 64 bit and 32 bit platform within your enterprise. So piece of advice, do not mix the database between 32 & 64 bit platforms as ETL processes such as DTS & SSIS will not work seamless unless you take care of such compatible version of tools.