none
On Error Redirect Rows ( Failing Rows Redirect and XML) RRS feed

  • Question

  • Hi All,

    In My current project, all SSIS Package flow is that, in case of failure It will redirect raw to error table.Error table is having same structure  of original table but only diff between Error and Destination table is that Error table is have datatype Nvarchar for all column with size 4000.

    For Example:
    -----------

    Table:Emp

    Empid Int
    EmpName Varchar(50)
    Salary Decima(25,8)


    Table : ErrorEmp

    Empid NVarchar(4000)
    EmpName NVarchar(4000)
    Salary NVarchar(4000)


    Now my database table count increase day by day.So, I want to maintain some common structure like for every failing rows It will insert data into XML column.

    I want to insert  separate Raw for each failing rows  and also that XML structure will be dynamic (not for specific table).This component can be used for any package as a part of my framework.

    Table: ErrorMaster

    Id bigint
    TableName Varchar(100)
    Errorstring XML


    Can someone help me with that to achieve my goal ?

    • Edited by Aminesh Thursday, October 3, 2019 6:33 AM
    Tuesday, October 1, 2019 5:13 AM

All replies

  • Hi Aminesh,

    Please refer to Error Whilst Inserting Data Into XML Column Using SSIS.

    Check if this is helpful.

    Regards,

    Zoe


    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, October 1, 2019 7:01 AM
  • Hi Zoe,

    My requirement is that , I want to insert only Redirected Failure rows from OLE DB destination.

    Above provided script, I don't understand where can I apply ?

    Tuesday, October 1, 2019 7:16 AM
  • Hi Aminesh,

    1st of all, the data wrapped into XML only inflates the space consumption even more, makes it very hard to retrieve and leads to a developer wasting time processing it.

    But if you insist, you should approach like this:

    use the existing table and extract the error rows as XML out of it to a new table (structure proposed). Delete the rows in the old table (it becomes an interim storage).


    Arthur

    MyBlog


    Twitter

    Tuesday, October 1, 2019 1:11 PM
    Moderator
  • Hi ALL,

    Let me just clarify my requirement again.

    I want to insert  separate Raw for each failing rows  and also that XML structure will be dynamic not for specific table.This component can be used for any package  as a part of my framework.

    I believe that it can be achievable only through Script component only but not sure how.

    Thanks in advance.





    • Edited by Aminesh Thursday, October 3, 2019 6:34 AM
    Thursday, October 3, 2019 6:32 AM
  • Hi Aminesh,

    Check if these links are helpful.

    Regards,

    Zoe


    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

    Friday, October 4, 2019 7:00 AM