Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available

Well here’s a crackingly helpful error message if there ever was one:

[<Component Name>] 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 Native Client”  Hresult: 0x80040E21  Description: “Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.”.

What does it all mean? Fortunately the answer is simple – the OLE DB component is trying to deliver a column to the target table and the target database won’t accept the input data type. The component has allowed the mapping because it should / might work but when it tries to execute – blecch! Tortellini!

In my case this was because I tried to push a Text Stream SSIS data type flow into a Varchar SQL Server column. Mapped fine, but failed when trying to run. Apparently a common problem when running data into a different database type to the source, e.g. Teradata to SQL Server.

8 thoughts on “Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available

  1. OK, thanks for the explanation. Now what is the solution? What do we need to do to get data from, in my case, an excel spreadsheet column into a SQL Server field (nvarchar(15), null)? Do I need to cast it? If so how? I tried doing this but it didn’t work: CAST([MIPS] AS nvarchar(15)) AS [MIPS]

  2. I faced this Issue when i was defining Parameter variables in SSIS SQL Task that calls a stored procudure like below
    EXEC sp_Test ?,?,? OUT
    and in the parameters list, I had

    Parameter Name as 1,2,3 which should be 0,1,2.

    That solved.

  3. Hi everyone,

    I faced the same issue, but in my case the problem was the paremeter binding. I tried to execute a batch of Statements separated by a “GO” and I got the error.
    In the documentation I found this rule for multiple statements:
    “If the task uses parameter binding, all the queries in the batch must have the same number and types of parameters.”
    Therefor I splitted the query in two Execute SQL Taks and that solved my particular problem.

    Reference: http://technet.microsoft.com/en-us/library/ms141003(v=sql.105).aspx

    Hope that helps.

    Kind Regards,

    Paul

  4. A mi me arroga el mismo error que comentan
    mi situacion es la siguiente :

    Tomo de mi base de datos una tabla la cual contiene campos de tipo text y al utilizar la conversion de datos del ssis los convierto en unicode text stream para depositarlos en una hoja de calculo.

    pero no logro migrar los datos podrian ayudarme..
    gracias!!

  5. Thanks PAUL HERNANDEZ… your post solve my problem
    “GO” should be removed and replaced by “;”
    Regards..

  6. in the destination –> from the options –> change the access mode to be (Open Rowset) instead of Open Rowset Using Fastload.

    Regards,
    Ahmad Elayyan

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>