how to install oracle 32bit and 64bit into 64bit windows

I have to install both 32 and 64bit oracle client to a 64bit windows as SSIS needs both to run

First installed 64bit without a glitch.

Then when I try to install 32bit, the setup app disappeared completely, after a few days struggling, I found the cure which is below.

Follow exactly what it says and 32bit was installed

 

I had the same issue and this answer may be a bit late now but this solved the issue.

Try creating the following registry entry:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE]

"inst_loc"="C:\\Program Files (x86)\\Oracle\\Inventory"

Encoding format of flatfile in SSIS

when you are using SSIS to load flatfiles , make sure you know the encoding format as it may cause very weird progme.

 

The flat file connection manager uses Unicode as the default and if the flatfile encoding format is not unicode, it can not be loaded.

Sometimes, it is really hard to tell why two .csv file, one can be loaded but the other fails all the time.

 

How to check the encoding format?

In VS, open the flat file and try to save it with Encoding, the encoding format info will show up there.

 

Happy debugging…

test post

hello world

find out which process is blocking

a few days ago, I was working on a very simple ETL task . What I did is delete a table
and reinsert. However, odd things happen after deletion as it takes forever to count the number
in this table... very odd

After while, I figured it out by using the script below to find out which process is blocking the table
and kill the process. After that，everything is back to normal

-- find out which process is blocking
select distinct object_name(a.rsc_objid), a.req_spid, b.loginame
from master.dbo.syslockinfo a (nolock) join
master.dbo.sysprocesses b (nolock) on a.req_spid=b.spid
where object_name(a.rsc_objid) is not null

Link  from Todd McDermid 

http://toddmcdermid.blogspot.ca/2009/10/quick-reference-ssis-in-32-and-64-bits.html

Quick Reference: SSIS in 32- and 64-bits

There are quite a few misconceptions flying about out there regarding SQL Server Integration Services and 64 bitness.  I've had to set more than a few people on the right path in the forums - mostly on one particular setting inside the SSIS packages that is getting misinterpreted.  Of course, this information only applies to 64-bit architectures - if you are running a Windows 32-bit OS, you have no choice - your packages will always run in 32-bit mode. 
Why would you want to run in 32-bit mode if you have a 64-bit system?  Drivers, mostly.  I'm not referring to hardware drivers, but data providers.  As an example, Excel and some versions of Oracle don't have 64-bit providers.  So for the ever-increasing base of 64-bit users, here's the skinny on executing Integration Services packages in your choice of 64-bit or 32-bit mode.
Background
I Can Design Fine, Why Won't It Run?
A little background to start.  Business Intelligence Design Studio (BIDS) is a 32-bit application.  When you're designing your package, you're using 32-bit facilities - and have no choice in the matter.  When you execute your package using DTExec, you have the option of 32-bit or 64-bit operation - but the default on a 64-bit installation is to use 64-bit mode (obviously).  However, some commonly used objects in SSIS don't have 64-bit counterparts, and will therefore cause your packages to fail.
Unfortunately, it usually doesn't say anywhere in these messages that the fault lies with 32 vs 64 bits.  It's usually something like:

• 0x80040154 ... Class Not Registered
• The AcquireConnection method call to the connection manager XXX failed with error code 0xC0202009
• 0xC00F9304 ... SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED
• The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered

(I include those sample errors here in the hope that those searching the web may find this article!)
Why Do I Want 32-bit Mode?
The most common reason to want 32-bit mode in an executing SSIS package is the Excel Provider.  It's currently not available for 64 bits, and will cause your package to crash.  (Office 14 (2010) is reported to have 64-bit support - even though it's not supported side-by-side with 32-bit.)  This applies to the other Office providers as well - Access, specifically - and to several other third party drivers and providers (like Oracle).  They simply will not work in a 64-bit environment (pre-2010).  You may also wish to run Execute DTS 2000 Package Tasks - and those can only run in 32-bit mode as well.
It Depends How You're Executing Your Package
There are many ways to execute an SSIS package - and this is the primary determiner of whether you're running it in 64-bit or 32-bit mode.  So pick your execution environment from the list below, and read up on how to force the bitness you desire.
Choosing Bitness Inside Business Intelligence Development Studio (BIDS)
If you're running your package inside BIDS, the setup is simple unless you're using the Execute Package Task or Execute Process Task to run child packages.
The package you currently have open will (by default) run in 64 bit mode.  The setting that controls this is a property on the project called Run64BitRuntime.  To access this property, right-click on the Integration Services project in your solution explorer and select Properties.  Then select the Debugging node in the editor.  The default here is "true", which means all the packages in this project will run in 64-bit mode.  If you change this to "false", all the packages will be run in 32-bit mode.
Special Note: Execute Package Task
Any child packages executed via the Execute Package Task will run in the same mode as the parent, regardless of the Run64BitRuntime setting of the project that the child package belongs to, regardless of the setting of ExecuteOutOfProcess.  This means that even if your child package has Run64BitRuntime set to false in the project you designed it in, it will be executed in 64-bit mode within BIDS if your parent package's Run64BitRuntime property istrue.
Special Note: Execute Process Task
The Execute Process Task can allow you to choose 32-bit mode independently of the settings in the parent package, at the expense of running the child package in another process.  As with the SQL Agent methods described later, you can specifically identify the 32-bit DTExec to run SSIS child packages in 32-bit mode (see below).
Choosing Bitness With SQL Agent
Instructing SQL Agent what environment you want your packages to run in is simple in Integration Services 2008.  SSIS 2005 makes you jump through a few more hoops.
Integration Services 2008
In the Agent Job Step Properties, you'll be using the SQL Server Integration Services Package type of step.  If you go to the Execution Options tab, you'll see an option to "Use 32 bit runtime" down at the bottom.
Integration Services 2005
With SQL 2005, you can not use the Integration Services Package type of job step to run an SSIS package in 32-bit mode.  Your recourse is to use the Operating System type of job step, and refer to the 32-bit version of DTExec specifically in the command line that you use, and manually specify arguments to DTExec.
Hurdle #1 - Finding the 32-bit DTExec
Finding the executable shouldn't be difficult.  In a standard 64-bit installation, the 32-bit DTExec.EXE should be located in the "\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn" folder.  It's plainly called "DTExec.EXE", and doesn't identify itself in any way as a 32-bit app - you'll have to "know" it is by it being located in the 32-bit folder.  (Or you could try to execute it and watch Task Manager.)  If you've installed SQL to a non-standard location, you may have to hunt a little.  If you can't find it at all, you may not have installed the 32-bit components on your 64-bit machine.  During the install of SQL Server, if you only selected "Integration Services" and didn't install "Business Intelligence Development Studio" OR "Management Tools - Complete", then you won't have the 32-bit DTExec installed.  You'll have to run SQL Setup, and install one of those options.
Hurdle #2 - Determining the Command Line Arguments
Next, you need to determine the command line parameters you need to operate DTExec from the command line.  You could read through the documentation and attempt to determine the arguments and settings by yourself, but I would recommend you use the power of the included GUI tools.  Both the IS Job Step in SQL Agent, and the DTExecUI tool provide a GUI to configure an SSIS package run.  On the last page of the GUI, it very helpfully places the exact command line arguments needed to run DTExec, based on all of the configuration options you've chosen on the ten or so other tabs of the GUI.  Leverage that!  Set up your package execution using the GUI, then copy the arguments off that last page.
Precompiled Scripts
This is only an issue in Integration Services 2005 - the dev team completely fixed this issue in SSIS 2008.  There is a "Precompile" option on Script Tasks, which is set to "true" by default.  If this has somehow been set to "false", your packages may not execute in a 64-bit environment.
32-bit ODBC Drivers
There is also one other oddity with using 32-bit ODBC drivers in Windows - at least in Server 2003, 2008, Vista, and Windows 7 64-bit OSes.  The first step to using those drivers is to set up a DSN to handle storing the server name and other particulars.  In my experience, the natural first place to start is by opening the "Data Sources" applet in the Control Panel.  That's mistake #1 - because that applet only manages 64-bit drivers.  You won't see yours listed at all.  The next step is to poke around and realize that there's a "Data Sources (32-bit)" applet there in the Control Panel as well.  That's mistake #2 - but not your mistake.  In my experience, this icon leads to some odd hybrid 32/64 bit management utility.  It definitely did NOT manage my 32-bit sources.  If you fire it up, then look at the processes tab in Task Manager, you'll see a process labeled "odbcad32.exe"... but you'll notice that it doesn't have the "*32" after it denoting that it's a 32-bit app.  Whatever it's attempting to manage, it's not the 32-bit ODBC drivers.  What you need to do is navigate to another odbcad32.exe that's sitting in your SYSWOW64 folder.  That ODBC data source administrator truly does manage 32-bit drivers, in a 32-bit process.
References/Resources
Most (if not all) of this information is also distilled in an MSDN article: 64-bit Considerations for Integration Services.  If you find other useful articles, pointers, or mistakes in the above, please post a comment.
Some other very useful articles:
How To: Run a Package, MSDN
64-bit references within an SSIS Script Component by Michael Entin, Microsoft SSIS Dev.
64-bit Considerations for SQL Server Integration Services by Douglas Laudenschlager, Microsoft SSIS Dev.
Where is my app.config for SSIS? by Darren Green, SQL Server MVP.
Oracle Driver Configuration in a 64-bit environment by Rob Kerr
Importing data from 64-bit Excel in SSIS by Hrvoje Piasevoli
32- and 64-Bit Connectivity from the Same Machine by SQLCAT

Posted 17th October 2009 by Todd McDermid

Using SSIS 2012 to Connect to Oracle 12C

Because BIDS ( SSDT) is a 32bit application and

http://sqlblog.com/blogs/jorg_klein/archive/2011/06/09/ssis-connect-to-oracle-on-a-64-bit-machine.aspx

We recently had a few customers where a connection to Oracle on a 64 bit machine was necessary. A quick search on the internet showed that this could be a big problem. I found all kind of blog and forum posts of developers complaining about this. A lot of developers will recognize the following error message:
Test connection failed because of an error in initializing provider. Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.
Provider is unable to function until these components are installed.
After a lot of searching, trying and debugging I think I found the right way to do it!

Problems

Because BIDS is a 32 bit application, as well on 32 as on 64 bit machines, it cannot see the 64 bit driver for Oracle. Because of this, connecting to Oracle from BIDS on a 64 bit machine will never work when you install the 64 bit Oracle client.
Another problem is the "Microsoft Provider for Oracle", this driver only exists in a 32 bit version and Microsoft has no plans to create a 64 bit one in the near future.
The last problem I know of is in the Oracle client itself, it seems that a connection will never work with the instant client, so always use the full client.
There are also a lot of problems with the 10G client, one of it is the fact that this driver can't handle the "(x86)" in the path of SQL Server. So using the 10G client is no option!

Solution

• Download the Oracle 11G full client.
  
• Install the 32 AND the 64 bit version of the 11G full client (Installation Type: Administrator) and reboot the server afterwards. The 32 bit version is needed for development from BIDS with is 32 bit, the 64 bit version is needed for production with the SQLAgent, which is 64 bit.
  
• Configure the Oracle clients (both 32 and 64 bits) by editing  the files tnsnames.ora and sqlnet.ora. Try to do this with an Oracle DBA or, even better, let him/her do this.
  
• Use the "Oracle provider for OLE DB" from SSIS, don't use the "Microsoft Provider for Oracle" because a 64 bit version of it does not exist.
  
• Schedule your packages with the SQLAgent.

Background information

• Visual Studio (BI Dev Studio)is a 32bit application.
  
• SQL Server Management Studio is a 32bit application.
  
• dtexecui.exe is a 32bit application.
  
• dtexec.exe has both 32bit and 64bit versions.
  
• There are x64 and x86 versions of the Oracle provider available.
  
• SQLAgent is a 64bit process.

My advice to BI consultants is to get an Oracle DBA or professional for the installation and configuration of the 2 full clients (32 and 64 bit). Tell the DBA to download the biggest client available, this way you are sure that they pick the right one ;-)
Testing if the clients have been installed and configured in the right way can be done with Windows ODBC Data Source Administrator:
Start...
Programs...
Administrative tools...
Data Sources (ODBC)

ADITIONAL STEPS FOR SSIS 2008 R2


It seems that, unfortunately, some additional steps are necessary for SQL Server 2008 R2 installations:
1. Open REGEDIT (Start… Run… REGEDIT) on the server and search for the following entry (for the 32 bits driver): HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC\MTxOCI
Make sure the following values are entered:

2. Next, search for (for the 64 bits driver):HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\MSDTC\MTxOCI Make sure the same values as above are entered.

3. Reboot your server.

When SSIS package 2012 pacakge variables is not getting populated from environment varaible

when you run Ssis package through BIDS or SSDT, package variables does get populated from environment variablabe. When it was running uder sql job agent, it failed to read from Env. Variables.

 

Solution:

1.   Restart SQL SERVER AGENT for that instance and Intergration Service Engine

2. Restart SSMS