Hi,
I am trying to import data from Oracle RDB into SQL Server 2005 using SSIS. Created a ODBC data source to connect to Oracle and used DataReader Source component and ADO.net to connect to the ODBC data source.
Under the Component properties tab, the SQL Command looks something like this.
Select ID, ADDRESS, REVISED from ADDRESS
The data type for the source columns are Integer, Varchar(30) and DATE VMS.
Now when I look at the Input and Output properties window,
The External columns has the following data types.
ID - four-byte signed integer [DT_I4]
ADDRESS - Unicode string [DT_WSTR], length = 0
REVISED - database timestamp [DT_DBTIMESTAMP]
The Output columns has the following data types
ID - four-byte signed integer [DT_I4]
ADDRESS - Unicode string [DT_WSTR], length = 0
REVISED - database timestamp [DT_DBTIMESTAMP]
When I tried to change the length of the ADDRESS on the output column, I get the following error.
Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.
Error at Data Flow Task [DataReader Source [1]]: System.Runtime.InteropServices.COMException (0xC020837D)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
Is this the default length for the Unicode string type. I was not able to load the ADDRESS column as it gets truncated before I load it into destination. Even if I use Derived or Data Conversion transformation, the ADDRESS is getting truncated before it reaches this transformation.
Any thoughts.
Thanks,
SK
Did you try using a Data Conversion transformation?
Rafael Salas
|||Hi,
I did try to use the Data Conversion transformation. But when I execute the package, I get the truncation error at the Data Source Reader itself.
Error: 0xC020902A at Data Flow Task, DataReader Source [1736]: The "component "DataReader Source" (1736)" failed because truncation occurred, and the truncation row disposition on "output column "ADDRESS" (2469)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC02090F5 at Data Flow Task, DataReader Source [1736]: The component "DataReader Source" (1736) was unable to process the data.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (1736) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039
Thanks,
SK
Hi,
Does anybody have inputs on this issue?
SK
|||It sounds like the provider you are using is reporting the length of the string incorrectly as 0. I've seen that before with a provider, but I don't recall what one... Is there a different provider you can try?
Otherwise, you might try changing the select statement to cast the value explicitly to a string of length 30 and see if the DataReaderSrc correctly creates the column then.
|||Unfortunately I can't use any other providers other than ADO.net as I have to use Oracle RDB ODBC to connect to RDB database.
Strangely, now the column is showing as text instead of Unicode string and I was able to use the data conversion transformation to convert back to Unicode String. Not sure what changed on the server. The only thing that I think of is we updated SQL Server with some updated from MS website.
Thanks,
SK
We had the exact same problem trying to pull data from Oracle Rdb. I went round & round but eventually found the solution.
Basically we had to install SQL*Net for Rdb (or OCI Services for Rdb) on our VMS server to make our Rdb database look like an Oracle database. Then we had to install Oracle client software on our client machine. I've used both Oracle 9.2 client and ODAC 10.2. You then set up your client connection using the Oracle Net Manager.
After you've got client & server configured, set up an SSIS connection manager using .Net Providers for OleDb -> Oracle Provider for OLE DB.
It's kind of a hassle to set up but it seems to work just fine.
Regards, Pat
No comments:
Post a Comment