CODEWITHSUNDEEP
sqlssis
Denis1893
Denis1893

Reputation: 65

Can't convert DT_NTEXT to NUMCERIC using SSIS

we load a CSV from the web via Power Query. Among other columns there are three money columns with the data type DT_NTEXT as output column. I have already tried to use a data conversion task as first thing to convert the columns to DT_WSTR(4000) and then convert them to numeric(17,5) in a following Derived Column Task with the following expression:

TRIM(price_conv) == "" ? NULL(DT_NUMERIC,17,5) : ((DT_NUMERIC,17,5)TRIM(price_conv))

Unfortunately, this still fails.

Error messages:

[Derived column [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived column" failed because error code 0xC0049063 occurred, and the error row disposition on "Derived column.Output[Output of the Derived column].column[price_derived]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "erived column" (2) failed with error code 0xC0209029 while processing input "Inputof the derived column" (3). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

PS: Error messages partly translated from German.

I also tried to set DT_WSTR in the advanced editor of the Power Query source, but it failed as well.

Does anyone have an idea?

Upvotes: 1

Views: 754

Answers (2)

Denis1893
Denis1893

Reputation: 65

I had to use the replace function for the separator (. and ,), then it worked.

#1 Convert to DT_WSTR using the data conversion task or in the derived column task

#2 Pay attention to the separator and replace with the replace function if necessary

Example:

TRIM(origprice_conv) == "" ? NULL(DT_NUMERIC,17,5) : ((DT_NUMERIC,17,5)REPLACE(TRIM(origprice_conv),".",","))

origprice_conv is already converted to DT_WSTR.

It's a pity that SSIS does not give a meaningful error message here.

My problem was two fold but the Data Viewer helped identify the problems. The first was that I could have blank values for origprice_conv and the default conversion did not work. I solved this by casting a null to the correct DT_NUMERIC specification. The second problem was that the source data used US style decimal places in the data. Being in Germany, the conversion expected the string to be "123,45" instead of "123.45". Internationalization note, the period is the thousands separator for Germany.

Upvotes: 1

Jayvee
Jayvee

Reputation: 10875

The problem is that there is not implicit or explicit conversion from ntext to numeric, reference: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

The trim will fail in the first place so what you may need to do, as suggested by billinkc, is a double conversion, to wstr, trim, and then to numeric :

(DT_NUMERIC,17,5)TRIM((DT_WSTR,20)(price_conv))

Upvotes: 0

Related Questions