none
Cleansing of Mainframe data - Decimal Formatting RRS feed

  • Question

  • Hi, I am new to SSIS and I have this requirement of importing Mainframe Data from text files(without delimiters). I figured out that Ragged Right should go well, but I have problems with certain fields. For example, there are columns with data like +01000000, +0145000.... and so on in every file. Here in the case of +01000000, I have to do the cleansing and load it into SQL server so that the data looks like +0100.0000. (this is just an example and there are many fields like this). Any lead on how to go about this?

    Thank you !

    Friday, December 13, 2019 2:34 PM

All replies

  • Have a derived column transformation task after your Flat file source task. Use expressions to transform column data.

    https://www.tutorialgateway.org/derived-column-transformation-in-ssis/


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Friday, December 13, 2019 3:23 PM
  • Hi SriHarsha66,

    We can use SUBSTRING (SSIS Expression) in Derived Column Transformation to achieve the function you need.

    Expression:

    SUBSTRING([Column 0],1,5) + "." + SUBSTRING([Column 0],6,4)

    Please refer to the following pictures:

    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, December 16, 2019 7:54 AM
  • Hi,

    May I know if you have anything to update?

    Please remember to click "Mark as Answer" the responses that resolved your issue. 

    This can be beneficial to other community members reading this thread.

    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, December 19, 2019 9:51 AM