BI-cloner


Current situation:

Our data source is Oracle and there is a particular column, called number_of_units, with datatype numeric(28, 0) that we will extract into SQL Server data destination.

However, in our SQL Server data destination this column has a datatype numeric(34, 14). This is because we used this column to do some basic calculation (e.g. number_of_units/60) and we need to keep the decimals in tact.

In our current SSIS, we do a select query with CAST from the data source:

SELECT CAST(number_of_units AS NUMERIC(34, 14)) AS number_of_units FROM TABLE_A;

Will casting up from numeric(28,0) to (34,14) cause bigger number of bytes to be transferred across from the data source I am talking about millions of records over here.

Are there other better ways of doing it

1) Don't do a CAST in the select query (i.e. SELECT number_of_units FROM TABLE_A).

2) Add a derived/calculated column with numeric(34,14) in SSIS to fill in the calculation.

3) Lastly, load the calculation into SQL Server data destination.




Re: CAST in SELECT or Data Conversion in SSIS

Phil Brammer


I would use this as my source query (you won't be able to bring over a NUMERIC(28,0) because SSIS croaks on the scale of zero:

SELECT CAST(number_of_units AS NUMERIC(29,1)) AS number_of_units FROM TABLE_A;

Then, inside SSIS, you can use a derived column in the data flow to issue another cast with something similar to:

(DT_NUMERIC,34,14)([number_of_units] / 60.00)








Re: CAST in SELECT or Data Conversion in SSIS

BI-cloner

Phil, thanks for your reply.

Do you have any examples on SSIS croaks on the scale of zero It is a known bug or as per designed






Re: CAST in SELECT or Data Conversion in SSIS

Phil Brammer

BI-cloner wrote:

Phil, thanks for your reply.

Do you have any examples on SSIS croaks on the scale of zero It is a known bug or as per designed



Depends on who you ask. As far as I'm concerned, it's a bug.

You can search this forum for threads that talk about this.





Re: CAST in SELECT or Data Conversion in SSIS

Rafael Salas

Independently of any possible bug; I prefer to cast in the query as the Data conversion task will add an extra column; which in most of the cases is not desired.