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

  • Pergunta

  • 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


    quarta-feira, 4 de setembro de 2019 13:53

Todas as Respostas

  • Hi Peter,

    How to reproduce your issue?


    Arthur

    MyBlog


    Twitter

    quarta-feira, 4 de setembro de 2019 15:28
  • 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

    quinta-feira, 5 de setembro de 2019 07:12
  • 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.

    sexta-feira, 6 de setembro de 2019 08:16
  • 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? 

    sexta-feira, 6 de setembro de 2019 08:21
  • 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

    segunda-feira, 9 de setembro de 2019 09:21
  • Thanks for your reply, Mona.

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

    Regards,

    Peter

    segunda-feira, 9 de setembro de 2019 11:26
  • 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

    terça-feira, 10 de setembro de 2019 09:53
  • Hi Mona,

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

    Regards,

    Peter

    quinta-feira, 19 de setembro de 2019 10:03
  • Hi Peter,

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


    Arthur

    MyBlog


    Twitter

    quinta-feira, 19 de setembro de 2019 13:30
  • 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

    quinta-feira, 30 de janeiro de 2020 23:50
  • I am also getting the error... Not found any resolution. Can't change the precision of numeric in advanced option
    sexta-feira, 21 de agosto de 2020 11:42