none
[SQL Server Destination [10]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.

    Question

  • Hi,

    At this time I am attempting to load data from XML files into a SQL Server database using Visual Studio 2008 and SQL Server 2008.  It runs fine for loading the first seven tables in our database, but when I get to the eighth table and perform a test run execution of the SSIS package, I receive the following errors:

     [SQL Server Destination [10]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E14  Description: "The bulk load failed. Unexpected NULL value in data file row 139, column 6. The destination column (UnitType) is defined as NOT NULL.".
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E14  Description: "The bulk load failed. Unexpected NULL value in data file row 137, column 6. The destination column (UnitType) is defined as NOT NULL.".
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E14  Description: "The bulk load failed. Unexpected NULL value in data file row 136, column 6. The destination column (UnitType) is defined as NOT NULL.".


    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "SQL Server Destination" (10) failed with error code 0xC02092B5 while processing input "SQL Server Destination Input" (26). 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.

    Does anyone have any suggestions?  I can certainly provide anymore information you may require.  The strangest part about this is that the above errors are complaining about a bulk load failing, but my SSIS command is actually a "Data Flow Task" not a "Bulk Insert Task."

    TIA.
    Monday, August 10, 2009 10:34 PM

Answers

  • It's not complaining about a Bulk Insert Task, it's complaining about a "bulk load" which is what the SQL Server Destination does.

    Your problem is that some of your Data Flow records contain NULL values in "column 6" (the sixth column) either directly from the source, or introduced somehow by one of the transformations in your Data Flow.  Your destination table definition in SQL Server has declared that NULL values are illegal, and therefore the Task is failing because it's attempt to insert NULLs is being rejected.

    Place a Data Viewer (or more than one) in your Data Flow by right-clicking on the flow arrows and selecting Data Viewers, then press Add, and OK, OK.  Run the task, and you'll see your NULL values.

    You need to:
    * Exclude those rows from your source query, or
    * Filter out those rows using a Conditional Split component in your Data Flow, or
    * Change those NULL values to non-NULL values in your Data Flow (using a Derived Column component), or
    * Change your destination table's definition to allow NULL values.
    Todd McDermid's Blog
    Monday, August 10, 2009 10:48 PM
    Moderator