32 bit application issues on 64 bit platform, vice-versa and SQL Server is no excuse?

There is much more sayings on the blogs about 64 bit platform issues when you have 32 bit applications, so it bit noise to create there about the  common problem that I have seen (virtually anywhere) that SSIS packages will suffer a lot between these platform differences, such as when you got to run a package in 64-bit mode, you might not be able to connect to as many data sources as you can when you run a package in 32-bit mode.

Just to go back on platform compatibility, there are only two 64 bit architectures supported by SQL Server (supported 64-bit architectures for Windows 2003 & 2008) and one of them is IA-64 (or you see as Itanium) which is based on the EPIC (Explicitly Parallel Instruction Computing) instruction set, having first of its kind in Intel's chip range and further to say that there is 32-bit equivalent in IA64 as the design/development started in 64 bit only. And the other  64 bit architecture is x64 (or what you see as x86-64) which is essentially the x86 architecture with extensions to include 64 bit functionality. Within this we can both X64 and AMD64 processors which were introduced by AMD's Athlon 64 range.

Ok enough said that, you must have installed few .NET Framework Data Providers and native OLE DB providers to execute the package successfuly and still out might not be available in 64-bit versions. ON similar occasions the only way it will run under 64-bit platform is from within a 32-bit application process. So essentially your application would have to be a 32-bit application which runs under the WOW64 subsystem and you cannot develop a 64-bit application that uses Jet. Now a days such service packs are available through the Windows Update and within Enterprise Network it may not be available and only way is to watch for any fixes.

So coming back to SSIS issues, as you can see that the 32-bit SSIS Designer displays only 32-bit providers that are installed on the local computer. To configure a connection manager to use a specific 64-bit provider, you must install the 32-bit version of the provider on the development computer for use at design time. Even though the 32-bit version of the provider is installed, you can still run the package in 64-bit mode, both in the development environment and after deployment. FYI you may find it amusing that both of the 32-bit and 64-bit versions of a provider will have the same ID, this is automatically managed by SSIS process while the runtime will select the appropriate version of the provider to use. As per the example in BOL if you run the package in the development environment on a 64-bit computer then it will run in 64 bit mode, as the provider because the default value of the Run64BitRuntime project property is True. Lastly by default, we cannot use the "Execute DTS 2000 Package" task in a package that is running in 64-bit mode due to the limitations of SQL Server 2000 DTS runtime which is not available in a 64-bit version.

Going further I have seen many problems when these packages are scheduled to run as a job, so in this 32 bit or 64 bit package execution in SQL agen job 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. However, the version of the dtexec utility that the job invokes depends on what versions of SQL Server and SQL Server Agent have been installed and are running on the 64-bit computer. Because the SQL Server & Agent from DB engine which  have been installed and are running on the computer (32or 64). Then, a job step type of SQL Server Integration Services Package invokes the 64-bit version of the dtexec utility, and the package runs in 64-bit mode.

Then coming back to original issue that to get a 32-bit process to run in a 64-bit environment the system has to do a memory address translation for everything it does. I would say the scenario is always dependant and executing such a 32 bit verion of IIS on an x-64 server is painfully slow compared to what it could be. It all depends on what your needs of the application are and you will have many advantages of x64 like application development as the memory is always matter for scalability. Still there are disadvantages also like bigger applications due to double pointer size, which makes caches and memory bandwidth less efficient. As referred it depends on what your application does, whether it accesses memory a lot or not. By default the processor does have to do address translation a lot, but this probably won't hurt the performance of your application too much. The underlying hardware instructions are mostly the same between x86 and amd64 and there is not too much inefficiency there assuming the CLR authors knew what they were doing.

Many times I have seen the users stating performance issues on X64 bit platform, I would say have you been able to pin point the actual issue on the platform.  I would say not to worry about the performance issues unless you have a performance-sensitive application. Then only worry about performance issues once you understand the nature of your application, a bit of reference on How to switch between the 32-bit versions of ASP.NET 1.1 and the 64-bit version of ASP.NET 2.0 on a 64-bit version of Windows and few other DTS/SSIS references on CodePlex: DTLoggedExec, BIDSHelper  & ssisUnit - unit testing framework this is more important when you need to version your package before deploying on the production environment. If you are a kind of book bug then here are few references for SSIS books Microsoft SQL Server 2008 Integration Services Unleashed and Professional Microsoft SQL Server 2008 Integration Services.