locked
Changing a datatype for a column in an odata datasource RRS feed

  • 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


    Wednesday, September 4, 2019 1:53 PM

All replies

  • Hi Peter,

    How to reproduce your issue?


    Arthur

    MyBlog


    Twitter

    Wednesday, September 4, 2019 3:28 PM
  • 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 MSDNFSF@microsoft.com

    Thursday, September 5, 2019 7:12 AM
  • 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.

    Friday, September 6, 2019 8:16 AM
  • 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? 

    Friday, September 6, 2019 8:21 AM
  • 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 MSDNFSF@microsoft.com

    Monday, September 9, 2019 9:21 AM
  • Thanks for your reply, Mona.

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

    Regards,

    Peter

    Monday, September 9, 2019 11:26 AM
  • 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 MSDNFSF@microsoft.com

    Tuesday, September 10, 2019 9:53 AM
  • Hi Mona,

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

    Regards,

    Peter

    Thursday, September 19, 2019 10:03 AM
  • Hi Peter,

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


    Arthur

    MyBlog


    Twitter

    Thursday, September 19, 2019 1:30 PM
  • 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

    Thursday, January 30, 2020 11:50 PM
  • I am also getting the error... Not found any resolution. Can't change the precision of numeric in advanced option
    Friday, August 21, 2020 11:42 AM