SSIS and 64 bit platform- Test connection failed because of an error in initializing provider.

SSIS and 64 bit platform- Test connection failed because of an error in initializing provider. ORA-06413: Connection not open?

The above error message might look familiar for the DBAs who manage 64 bit platforms, it is a simple connection error when SSIS connection (on 64 bit) attempted to Oracle database. In the past I have seen this error when it attempts to connect 32 bit driver of Oracle provider. Recently this was one of the daunting moments to resolve on a SQL 64bit platform where the complete work force from Developer to End user has been involved.
As per the Oracle's reference this might occur when attempting to connect to Oracle database from a Windows platform using the interfaces such as: ODBC, OLEDB, ODP.NET and as usual only 32 bit Oracle client software is available for download and using from a 64-bit Windows Operating system will cause the issue to generate error message.
You may know for SQL Server 2000 packages to execute you can use 'Execute DTS 2000 Package task' from SSIS that runs SQL Server 2000 packages, but otherwise its features are similar to those of the Execute Package task. Only in rare ocassions such as when reusing parts of SQL 2000 packages and if you program other packages can reuse parts of a package workflow, it is better to use this task from SSIS. Within SQL 2005 SSIS and BIDS tools you can useDTS package designer that works with the SQL Server 2008 tools, but the designer's features have not been updated for SQL Server 2008.
Further to the above problem you can try using short name version of directory paths, such as for DTS Wizard in SQL Server 2005, run it using this command prompt: C:\PROGRA~2\Microsoft SQL Server\90\DTS\Binn\dtswizard.exe. As we talked about DTS wizard, SSIS designer and BIDS tools the issue arises basically due to the client components (management studio) are installed into a folder with (x86) in the folder name. As you may feel that during the client installation or even in a Enterprise network scenario such installations are carried out in automated way whereby there is no control over this path name at install time because the SQL Server install routine is doing it.As there are no 64-bit based Client tools for SQL Server (SSMS) all of them are compiled and installed as 32-bit application as the are installed on X86 folder within Program Files or any user-specified location.
But the basic error of connection is originated from the provider (ORacle side) due to the fashion of the data import wizard (DTSWizard) is passing a string to the Oracle provider with (x86) in the string, and to workaround this issue you can perform the installation to choose default location by SQLInstaller or choose your own folders with their names. You will end up with your name as follows Drive:\DirName\ClientTools (x86) and in general SQL default installation also creates another folder with the same name ClientTools where 64 bit binaries are installed. So what you can do is to copy the files under ClientTools (x86) files to this system defined ClientTools directory such as folder similar to D:\ClientToolsx86\90\DTS\Binn and then try to execute the DTSWizard.exe and you should be able to successfully connect to Oracle and import data. Also I have seen similar issues when it can connect and import data, you can't set it up as an SSIS package or a job under the SQL Agent because there are a ton of registry keys that point to (x86) as the default location for the management studio.
Most of the forums I see that registry hacking is a way out, to my knowledge & experience I will never vouch to take that measure unless it is suggested by Microsoft CSS or you are entirely worked out on getting back the platform if it created another catastrophe. Recently Oracle does provide an x64 Client and the provider does connect, but it cannot import data because there is a data Precision issue where it cannot convert to Float, Numeric with the proper precision and scale. There is a bug submitted for a patch but it has not been released yet, me thinks and as a whole to connect to Oracle using the x64 Oracle client provider you need to execute the x64 DTSWizard which is not in the management studio, it is located in the D:\ClientTools\90\DTS\Binn folder, this is where the 64 Bit compiled applications are.
This is just a beginning of issue to resolve but many more such packages needs to be addressed on this platform and me thinking to drive the developer force to re-write all of those packages in SSIS rather than simple DTS migration wizard, you never know what is round the corner!