1

I adding data from a nested XML tag to the same destination source. The data is loading but there is an error on the package.

I running my SSIS package locally. I'm really just learning by myself. I used Data Conversion to match the destination table but the package "failed". There isn't a green check but the data load in the table.

Check SSIS result picture:

enter image description here

I'm concerned as if I have a bigger data set I wouldn't if there are missing values. Side note, the same file loaded fine without error in SSIS2013(Visual Studio 20137). I'm using SSIS2017(Visual Studio 2017).

The XML file is in this format

<mortgages>
    <mortgage>
        <Borrowers>
            <Borrower>
            </Borrower>

            <CoBorrower>
            </CoBorrower>
        </Borrowers>
    </mortgage>
</mortgages>

I'm expecting the package to complete without error however I'm getting the error below:

[Coborrow Info to BorrowerDetailsXMLTable [51]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

[Coborrow Info to BorrowerDetailsXMLTable [51]] Error: There was an error with Coborrow Info to BorrowerDetailsXMLTable.Inputs[OLE DB Destination Input].Columns[XML_Co_YearsAtThisAdress] on Coborrow Info to BorrowerDetailsXMLTable.Inputs[OLE DB Destination Input]. The column status returned was: "The value could not be converted because of a potential loss of data.".

[Coborrow Info to BorrowerDetailsXMLTable [51]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Coborrow Info to BorrowerDetailsXMLTable.Inputs[OLE DB Destination Input]" failed because error code 0xC0209077 occurred, and the error row disposition on "Coborrow Info to BorrowerDetailsXMLTable.Inputs[OLE DB Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Coborrow Info to BorrowerDetailsXMLTable" (51) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (64). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

Hadi
  • 36,233
  • 13
  • 65
  • 124
user3505108
  • 23
  • 2
  • The main error is `The value could not be converted because of a potential loss of data` the destination column is `XML_Co_YearsAtThisAdress`. Can you provide the source and destination columns types? Also it will be good to add a screenshot of the data conversion transformation and some data samples. – Hadi May 16 '19 at 21:14
  • @Hadi thanks for the reply please see below links for a copy of the XML file and XSD generated. Also, the data conversion looks and the destination table. This is the [link](https://www.dropbox.com/s/hhfgekcmfh4cro8/Edem_Mortgage_XML.xml?dl=0) to the XML file I'm using to test with and this is the [link](https://www.dropbox.com/s/8auj919qh590o6g/Edem_Mortgage_XML.xsd?dl=0) to the XSD generated. [Destination table picture](https://www.dropbox.com/s/g8qjq6om1l21j3v/Borrower_Table.PNG?dl=0) [Data conversion picture](https://www.dropbox.com/s/0l3j5e4g69beirm/DataConversion.PNG?dl=0) – user3505108 May 17 '19 at 01:32
  • `The column status returned was: "The value could not be converted because of a potential loss of data` is pretty much a thing has nothing to do with XML. It is the receiving end - database has no capacity i.e. when the length of the incoming data exceeds the limits set on the destination. – Arthur May 17 '19 at 21:15
  • @Arthur I don't think it's a Destination Source size issue though. If you see the screenshots and attachments above, the database field for the field mentioned in the error has varchar(max) for the incoming field which has a lesser size than that. Moreover it's working without error in Visual Studio 2013. The error is just occurring in Visual Studio 2017. Maybe some additional info, I can't be size because the field complaining is actually NULL/BLANK. And yes the Destination database is set to accept null value as you might have noticed on the screenshot above. Thanks – user3505108 May 17 '19 at 21:55
  • @user3505108 size the data and you may become surprised E.g. hidden/machine chars in input can inflate it but remain invisible to some end user tools. – Arthur May 22 '19 at 21:31

0 Answers0