MS SQL Server DTS - SSIS - DataReader Source with ODBC Oracle RDB

Author

SSIS - DataReader Source with ODBC Oracle RDB

JasonB

2006-04-05, 9:31 am

Hello,
This is a long post, but figured I'd supply as much info as possible up
front....

I'm in the process of building a Proof of concept integration between
Legacy RDB databases and Sql Server.
I'm reading data from a simple table on the RDB but am having issues with a
Char(1) field.

The error: "[DataReader Source [1072]] Error: The "component "DataReader
Source" (1072)" failed because truncation occurred, and the truncation row
disposition on "output column "TRANSACTION_TYPE" (1411)" specifies failure on
truncation. A truncation error occurred on the specified object of the
specified component. " is being returned in the Execution results.

I've found that in the Output Properties of the Advanced Editor the field in
question is being detected (Output Column) as a Unicode String (Unicode
string [DT_WSTR]) of length 0 - Which I assume is why I'm getting the
Truncation Error above.

How do I manually configure this datatype and length. Changing it via the
drop down list give me a com error as below:
Error at Data Flow Task [DataReader Source [1072]]: The data type of output
columns on the component "DataReader Source" (1072) cannot be changed.

Error at Data Flow Task [DataReader Source [1072]]:
System.Runtime.InteropServices.COMException (0xC020837D)
at
Microsoft.SqlServer.Dts.Pipeline. DataReaderSourceAdap
ter. SetOutputColumnDataT
ypeProperties(Int32
iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32
iPrecision, Int32 iScale, Int32 iCodePage)
at
Microsoft.SqlServer.Dts.Pipeline. ManagedComponentHost
. HostSetOutputColumnD
ataTypeProperties(ID
TSManagedComponentWr
apper90
wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32
iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage).

Thanks in advance for any help.

privatenews

2006-04-06, 3:27 am

Hello Jason,

From the symptom, it is most likely a odbc driver issue. The source
connection component interpretsobject's data type size as (0) and setg both
the external column size and the
output column size to (0). This cannot be edited in DataReader source.

You may want to update odbc driver or you may try to use oledb provider for
Oracle to test. Also, I noticed that you have an open incident on this
issue with PSS. It is suggsted that that you keep working with them because
it is most efficient manner to troubleshoot the issue.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

====================
====================
=============

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
>Thread-Topic: SSIS - DataReader Source with ODBC Oracle RDB
>thread-index: AcZYnkWwNHiH2fwUTiOL
blJmIU+3Ow==
>X-WBNR-Posting-Host: 194.73.97.198
>From: =?Utf-8?B?SmFzb25C?= <JasonB@community.nospam>
>Subject: SSIS - DataReader Source with ODBC Oracle RDB
>Date: Wed, 5 Apr 2006 03:47:02 -0700
>Lines: 37
>Message-ID: <AE2BF756-14BD-4445-B568- DA773E5A1DDF@microso
ft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>Newsgroups: microsoft.public.sqlserver.dts
>Path: TK2MSFTNGXA01.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.dts:64906
>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.dts
>
>Hello,
> This is a long post, but figured I'd supply as much info as possible up
>front....
>
> I'm in the process of building a Proof of concept integration between
>Legacy RDB databases and Sql Server.
> I'm reading data from a simple table on the RDB but am having issues with
a
>Char(1) field.
>
>The error: "[DataReader Source [1072]] Error: The "component "DataReader
>Source" (1072)" failed because truncation occurred, and the truncation row
>disposition on "output column "TRANSACTION_TYPE" (1411)" specifies failure
on
>truncation. A truncation error occurred on the specified object of the
>specified component. " is being returned in the Execution results.
>
>I've found that in the Output Properties of the Advanced Editor the field
in
>question is being detected (Output Column) as a Unicode String (Unicode
>string [DT_WSTR]) of length 0 - Which I assume is why I'm getting the
>Truncation Error above.
>
>How do I manually configure this datatype and length. Changing it via the
>drop down list give me a com error as below:
>Error at Data Flow Task [DataReader Source [1072]]: The data type of
output
>columns on the component "DataReader Source" (1072) cannot be changed.
>
>Error at Data Flow Task [DataReader Source [1072]]:
>System.Runtime.InteropServices.COMException (0xC020837D)
> at
>Microsoft.SqlServer.Dts.Pipeline. DataReaderSourceAdap
ter.SetOutputColumnDat
aTypeProperties(Int3
2
>iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32
>iPrecision, Int32 iScale, Int32 iCodePage)
> at
>Microsoft.SqlServer.Dts.Pipeline. ManagedComponentHost
. HostSetOutputColumnD
a
taTypeProperties(IDT
SManagedComponentWra
pper90
>wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32
>iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage).
>
>Thanks in advance for any help.
>

JasonB

2006-04-06, 3:27 am

Thanks for the respone.
I will try and get a newer version of the driver, but I'm currently using
the Oracle rdb driver, which should be sufficient.
This isnt a Bizzarre feature i'm trying to use, just read a simple data type.
I'm not sure what you mean by having an open Issue with PSS. I've not
contacted anybody else regarding this issue, and dont know who what PSS is.
Will post again with news on whether a different driver changes the results.

"JasonB" wrote:

> Hello,
> This is a long post, but figured I'd supply as much info as possible up
> front....
>
> I'm in the process of building a Proof of concept integration between
> Legacy RDB databases and Sql Server.
> I'm reading data from a simple table on the RDB but am having issues with a
> Char(1) field.
>
> The error: "[DataReader Source [1072]] Error: The "component "DataReader
> Source" (1072)" failed because truncation occurred, and the truncation row
> disposition on "output column "TRANSACTION_TYPE" (1411)" specifies failure on
> truncation. A truncation error occurred on the specified object of the
> specified component. " is being returned in the Execution results.
>
> I've found that in the Output Properties of the Advanced Editor the field in
> question is being detected (Output Column) as a Unicode String (Unicode
> string [DT_WSTR]) of length 0 - Which I assume is why I'm getting the
> Truncation Error above.
>
> How do I manually configure this datatype and length. Changing it via the
> drop down list give me a com error as below:
> Error at Data Flow Task [DataReader Source [1072]]: The data type of output
> columns on the component "DataReader Source" (1072) cannot be changed.
>
> Error at Data Flow Task [DataReader Source [1072]]:
> System.Runtime.InteropServices.COMException (0xC020837D)
> at
> Microsoft.SqlServer.Dts.Pipeline. DataReaderSourceAdap
ter. SetOutputColumnDataT
ypeProperties(Int32
> iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32
> iPrecision, Int32 iScale, Int32 iCodePage)
> at
> Microsoft.SqlServer.Dts.Pipeline. ManagedComponentHost
. HostSetOutputColumnD
ataTypeProperties(ID
TSManagedComponentWr
apper90
> wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32
> iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage).
>
> Thanks in advance for any help.

JasonB

2006-04-06, 7:33 am

Hello.
I've updated my Oracle rdb driver to the latest version (3.01.00.03) dated
3rd March 2006. This has no effect on the error in question.
Can you suggest a free OLE DB provider for Rdb that I could try, as I've not
found one yet.

""privatenews"" wrote:

> Hello Jason,
>
> From the symptom, it is most likely a odbc driver issue. The source
> connection component interpretsobject's data type size as (0) and setg both
> the external column size and the
> output column size to (0). This cannot be edited in DataReader source.
>
> You may want to update odbc driver or you may try to use oledb provider for
> Oracle to test. Also, I noticed that you have an open incident on this
> issue with PSS. It is suggsted that that you keep working with them because
> it is most efficient manner to troubleshoot the issue.
>
> Best Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
>
> ====================
====================
=============
>
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> --------------------
> a
> on
> in
> output
> aTypeProperties(Int3
2
> taTypeProperties(IDT
SManagedComponentWra
pper90
>
>

JasonB

2006-04-06, 9:31 am

On further testing I have found that varchar fields produce the exact same
error. Numerical fields seem ok.

"JasonB" wrote:
[color=darkred]
> Thanks for the respone.
> I will try and get a newer version of the driver, but I'm currently using
> the Oracle rdb driver, which should be sufficient.
> This isnt a Bizzarre feature i'm trying to use, just read a simple data type.
> I'm not sure what you mean by having an open Issue with PSS. I've not
> contacted anybody else regarding this issue, and dont know who what PSS is.
> Will post again with news on whether a different driver changes the results.
>
> "JasonB" wrote:
>

JasonB

2006-04-06, 11:30 am

I've managed to work around this issue by writing my own source component,
unsing an ODBC connection.
This isnt ideal, but will do for now until I get an OLE DB provider to test.

"JasonB" wrote:
[color=darkred]
> On further testing I have found that varchar fields produce the exact same
> error. Numerical fields seem ok.
>
> "JasonB" wrote:
>

privatenews

2006-04-07, 3:39 am

Hello Jason,

You may want to refer to the following link for some venders of OLE
provider.

http://www.sqlsummit.com/oledbVen.htm

Note: This response contains a reference to a third-party World Wide Web
site. Microsoft is providing this information as a convenience to you.
Microsoft does not control these sites and has not tested any software or
information found on these sites; therefore, Microsoft cannot make any
representations regarding the quality, safety, or suitability of any
software or information found there. There are inherent dangers in the use
of any software found on the Internet, and Microsoft cautions you to make
sure that you completely understand the risk before retrieving any software
from the Internet.

PSS is Microsoft Product Support Services. I saw a similar issue in a open
service request in PSS and there is no solution yet.

Please be advised that contacting phone support will be a charged call.
However, if you are simply requesting a hotfix be sent to you and no other
support then charges are usually refunded or waived.

To obtain the phone numbers for specific technology request please take a
look at the web site listed below.

http://support.microsoft.com/defaul...US;PHONENUMBERS

If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

====================
====================
=============

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
>Thread-Topic: SSIS - DataReader Source with ODBC Oracle RDB
>thread-index: AcZZjvijVcwz8E5dQguS
q4xvQ7NXQQ==
>X-WBNR-Posting-Host: 194.73.97.198
>From: =?Utf-8?B?SmFzb25C?= <JasonB@community.nospam>
>References: <AE2BF756-14BD-4445-B568- DA773E5A1DDF@microso
ft.com>
<BDE564F5-5A7C-46D4-B7F5- 09277EBA6FE8@microso
ft.com>
<DAF89788-F6F2-4BD4-8EEA- E6DB98AF3134@microso
ft.com>
>Subject: RE: SSIS - DataReader Source with ODBC Oracle RDB
>Date: Thu, 6 Apr 2006 08:30:01 -0700
>Lines: 58
>Message-ID: <F7CC92E6-35EF-4139-BA14- 245D50E87DB2@microso
ft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>Newsgroups: microsoft.public.sqlserver.dts
>Path: TK2MSFTNGXA01.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.dts:64956
>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.dts
>
>I've managed to work around this issue by writing my own source component,
>unsing an ODBC connection.
>This isnt ideal, but will do for now until I get an OLE DB provider to
test.
>
>"JasonB" wrote:
>
same[color=darkred]
using[color=darkred]

data type.[color=darkred]
PSS is.[color=darkred]
results.[color=darkred]
possible up[color=darkred]
between[color=darkre
d]
issues with a[color=darkred]
" DataReader[color=dar
kred]
truncation row[color=darkred]
failure on[color=darkred]
the[color=darkred]
field in[color=darkred]
(Unicode[color=darkr
ed]
the[color=darkred]
via the[color=darkred]
output[color=darkred
]
changed.[color=darkred]
Microsoft.SqlServer.Dts.Pipeline. DataReaderSourceAdap
ter.SetOutputColumnData
TypeProperties(Int32
[color=darkred]
Int32[color=darkred]

Microsoft.SqlServer.Dts.Pipeline. ManagedComponentHost
. HostSetOutputColumnD
at
aTypeProperties(IDTS
ManagedComponentWrap
per90[color=darkred]

Int32[color=darkred]

>

Sponsored Links