Output column has a precision that is not valid (loading from Oracle using OraOLEDB.Oracle.1)

Hi!

I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error:

TITLE: Microsoft Visual Studio

Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:

1) Union

I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"

Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.

2) SQL command from variable

The select basically looks like this:

"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"

Again, even if I cast all columns (like in the union), I still get the same error.

Any help would be greatly appreciated. Thanks!

Try casting the oracle fields to NUMBER(p,s) with a scale of at least one (1). Even though you look to be working with integers inside Oracle, casting it to a value of nnn.0 won't change that fact. I think then you'll be able to bring it into SSIS and then cast to integer there.|||I've tried to cast as DECIMAL(x, y) and NUMBER(x, y), and it works for "normal" selects, but not when the select contains UNION or is a SQL command variable.|||I wonder if putting the UNION or parameters in a subquery and specifying the cast in the outer query would help.

SELECT CAST... FROM (SELECT ... UNION ALL.. ) AS x
|||

JayH wrote:

I wonder if putting the UNION or parameters in a subquery and specifying the cast in the outer query would help.

SELECT CAST... FROM (SELECT ... UNION ALL.. ) AS x

For the UNION this seems to work, but I've removed all errors and warnings by casting to char (TO_CHAR(x) as myColumn)). Not pretty, but it works for both the UNION and the SQL command variables. For the UNION I'll change to your suggestion. For the SQL command variable I'll have to stick with TO_CHAR until something better pops up.

Note:

Precision is not valid error is displayed when some function is executed on the numeric column (like SUM, MIN, MAX, AVG, NVL etc) when using the OraOLEDB.Oracle.1. Seems like SSIS can't determine the numeric type when such a function is applied.

Thanks all!

|||Hmm.. Now I got the CAST(x AS NUMBER(w, z)) to work in the SQL command from variable. The meta data is not updated in the source component if you just change the SQL script in the variable (which was causing the problem). You have to change to a table or something else than the SQL command variable. Press OK. Edit the source component to use the SQL command variable again, and then the meta data is updated and it works. Previously I did get that "do you want to update meta data..."-message and pressed OK, but it seems like it was not updated correctly. By changing to a table and then back to the SQL command variable, everything is updated correctly. So now I don't use TO_CHAR anymore.|||

I found everything you said is correct, but I needed to go a bit further. The query against Oracle in the SQL Server 2000 DTS package was adding two numeric fields together, such as "SELECT ..... f1 + f2 AS BALANCE FROM.....". Even though the 2 source fields are the same datatype, decimal(15,2), for some reason SSIS still tripped over the precision and scale of the external and output columns. The work-around was to return both source columns separately, and then add them via a derived column task.