Changing a datatype for a column in an odata datasource

Question

Hi,

I'm trying to import an odata datastream via an OData Source component. The datastream contains a column with decimals in it (like 0.42). The OData Source component defines this column as a DT_NUMERIC with precision 38 and scale 0. 

For some reason the decimal part of the number is cut off during the import. I have tried a derived column with a type cast to decimal(38,2) and a data conversion.

I also tried changing the data type at the most logical place for me: right at the source in the OData Source Component with the external columns but SSIS displays an error "Property value is not valid" and then starts telling me "

Error at Retrieve Werkorders [TDO Werkorders [246]]: The data type of output columns on the TDO Werkorders cannot be changed, except for DT_WSTR and DT_NTEXT columns.

Error at Retrieve Werkorders [TDO Werkorders [246]]: System.Runtime.InteropServices.COMException (0xC020837D)
   at Microsoft.SqlServer.IntegrationServices.OData.ODataSource.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper100 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)"

I have been looking at this for over a day now. Does anyone have a clue?

Thanks in advance. Regards,

Peter de Hoog

Answer 1

Hi Peter,

How to reproduce your issue?

---

Arthur

MyBlog

Twitter

Answer 2

Hi Peter,

We could use Data Conversion Transformation to set the scale as 2.

Best Regards,

Mona

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail]

Answer 3

Hi Arthur,

Thanks for your reply. Unfortunately I can't give you the link to the datastream as this is company information. The data stream looks like this :

{

"@odata.context": "x",

"value": [

{

"Id": 275,

"UrenGeboekt": null

},

{

"Id": 25306,

"UrenGeboekt": 0.42

}

The odata stream is valid. The column UrenGeboekt is cut off at the decimal point. I use a Odata Source component and the preview shows proper data.

Answer 4

Thanks for your reply, Mona. I already tried that. The problem is the column definition in the source. I believe that one to cut off the decimals and if the source is without decimals, it won't matter if the datatype is changed later on in the process.

Or am I wrong here? 

Answer 5

Hi Peter,

May I know if you can change the data type in the source?

Best Regards,

Mona

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail]

Answer 6

Thanks for your reply, Mona.

Unfortunately I'm not able to change the data type in the datasource.

Regards,

Peter

Answer 7

Hi Peter,

Please try to change the data type in raw odata datasource.

Best Regards,

Mona

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail]

Answer 8

Hi Mona,

Can you please tell me how to do that? I'm not quite sure to be honest.

Regards,

Peter

Answer 9

Hi Peter,

the data you use is unstructured. This is why you have troubles. The data needs pre-processing.

---

Arthur

MyBlog

Twitter

Answer 10

Hi Peter,

Did you resolve this issue?  I have the same problem.

Restated, for the benefit of others' here, I have numbered data, that has decimals (float), that derives from an oData source, which is imported to and arrives at SSIS's oData object, correctly, with decimals.  But its (External) output removes the decimals.

Attempts to change its datatype through the Advanced Editor returns the same error already given above, where the datatype cannot be changed for numeric data. I need the decimals to come through.

Can anyone please help?

Thanks, Ed

Answer 11

I am also getting the error... Not found any resolution. Can't change the precision of numeric in advanced option