Dear All,
Need your help in below issue.
I have SSIS package which pull data from Oracle to SQL Server.
Currently I have used Oracle.Oledb provider for the same, but it is giving me improper result.
I check on oracle end count for that table was 26000 and when I run the same query through SSIS it shows 0 rows.
The preview of the query is showing 0 as output.
but when I tried the same using Microsoft OLEDB provider for Oracle it giving me proper out-put
Oracle.Oledb provider returning improper out-put
March 25th, 2015 1:01pm
Microsoft OLEDB Provider for Oracle will only work if the Oracle database is on a Windows Server.
Have you got oracle Client software installed on the Server from where you are running and have you configure tnsnames.ora?
Free Windows Admin Tool Kit Click here and download it now
March 25th, 2015 1:05pm
Hi Susant,
the following article has the solution
http://www.sqlchick.com/entries/2012/9/2/resolving-missing-records-in-ssis-from-oracle-source.html
In essence, revise the connection string to append
;UseSessionFormat=True
March 25th, 2015 1:14pm
Hi Arthur,
Can you please tell me from where to set UseSessionFormat=True.
I Have SQL Server 64 Bit and Oracle client 32 bit on server
Free Windows Admin Tool Kit Click here and download it now
March 26th, 2015 4:53am
Hi Sushant,
UseSessionFormat - specifies whether to use the default NLS session formats or let OraOLEDB override some of these formats for the duration of the session. Valid values are 0 (FALSE) and 1 (TRUE). The default is FALSE which lets OraOLEDB override some of the default NLS session formats. If the value is TRUE, OraOLEDB uses the default NLS session formats.
Setting UseSessionFormat=true in the connection string will result in the provider using the session format specified by the client install, which should resolve this issue. The following connection string for the connection like below:
Data Source=DataSourceNameHere;User ID=UserIDHere;Password=PasswordHere;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;UseSessionFormat=True;
Thanks,
Katherine Xiong
March 27th, 2015 3:25am
This topic is archived. No further replies will be accepted.
Other recent topics
