May 5, 2011 at 12:42 am
Hi all.
I have set up a connection that pull data from Oracle 10.2.0.
It is no problem to transfer numeric values like int, tinyint, decimal and so on, but string seems to be a problem.
After some google search I found that if I change the Oracle table to hold varchar2 100 byte instead of varchar2 50 char it works.
I can now read all string data.
Still, this is not a good solution. There are several tables that I need to read, but I can not alter the string type in these.
Anyone got an idea on how to get around this problem?
Dan
May 6, 2011 at 7:17 am
Dan-Ketil Jakobsen (5/5/2011)
I have set up a connection that pull data from Oracle 10.2.0.It is no problem to transfer numeric values like int, tinyint, decimal and so on, but string seems to be a problem.
After some google search I found that if I change the Oracle table to hold varchar2 100 byte instead of varchar2 50 char it works.
I can now read all string data.
Still, this is not a good solution. There are several tables that I need to read, but I can not alter the string type in these.
Anyone got an idea on how to get around this problem?
Oracle support may say this is not a problem but a feature, let me explain.
varchar2(100 byte) means the column stores single-byte characters to a max of 100 bytes.
varchar2(100 char) means the column stores a max of 100 characters using the database character set; this means that if the database was set to UTF8 (or any other multibyte character set) it will still store 100 characters no matter how many bytes are needed to do so.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 10, 2011 at 4:38 am
Oracle support may say this is not a problem but a feature, let me explain.
varchar2(100 byte) means the column stores single-byte characters to a max of 100 bytes.
varchar2(100 char) means the column stores a max of 100 characters using the database character set; this means that if the database was set to UTF8 (or any other multibyte character set) it will still store 100 characters no matter how many bytes are needed to do so.
Hope this helps.
Hi Pablo
Still I do not understand why I have to change it on Oracle side in order for my SSIS package to write to MS SQL 2005 database table.
I have tried to alter my mssql table to recieve data but no matter what I do on my MSSQL side it fails.
I found an article saying that I coud do the following:
USE [Staging]
GO
drop table [dbo].[DMDUNIT_75]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DMDUNIT_75](
[DMDUNIT] varchar2(200) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
But I have not been able to get that to work, nor can I find the varchar2 as a datatype so I kind of guesssed that this would be a long shot.
Then again, that is about all I have left to try it seems.
As mentioned, I get to transfer some tables, but I am not allowed to change the datatype on all tables I need for my extract. Some tables are needed in other sources, and they stop working if i change the datatype.
Dan
May 18, 2011 at 7:05 am
Dan-Ketil Jakobsen (5/10/2011)
As mentioned, I get to transfer some tables, but I am not allowed to change the datatype on all tables I need for my extract.
Understood.
How are you moving the data from Oracle to SQL Server?
What driver is facilitating connectivity?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 18, 2011 at 7:29 am
Hi
We use Oracle odbc OraClient 10_gHome based on InstantClient_11_1
Dan
May 18, 2011 at 7:39 am
Have you tried using the .Net Native Client for Oracle to make your connection in SSIS?
May 18, 2011 at 7:42 am
No. I have not used that one. Our app does ot support .Net I think.
But ca you forward me to a site where I can find more info ?
Dan
May 18, 2011 at 8:10 am
Dan,
First off, my bad. I also do .Net development and am used to seeing the Native Client available. There is, however, an OLEDB Connection Manager available that you can specify the Oracle client in. I believe that you need to have the Oracle Client tools installed on the machine that is executing the package. Sorry that I can't give you more specific info right now, but I will try to dig up something later today.
Source
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply