SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred :- Reasons and troubleshooting.


This is a very generic error and can occur due to multiple reasons. I am documenting as much as of them based on Case studies. Refer to the description and the error codes for each of the unique errors.

A:

Error:

“DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80040E4D Description: “Login failed for user ‘<User_Name>’.”

Symbolic Name: DTS_E_OLEDBERROR

The hexadecimal value for this error number = 0x80040E4D.

Cause:

When you create a package on your local machine and create a connection in it, you have the option to save the password of that connection. However by default it encrypts this password so that in can only be decrypted when you’ re running the package on the same machine, with the same account. This holds good only if the Connection Manager uses SQL Authentication or connects to a Database which does not support Windows Integrated Authentication (e.g. Oracle).

So, in the above scenario, if the package is deployed to a Remote Sql Server, it fails with the “Login failed..” error as it is not able to decrypt the password. (Note: It runs fine if deployed in the local Sql Server)

Resolution:

To correct the problem, you should opt between either of the three options:

1. Change all the connection managers in the package to use windows authentication.

Note: This is not an option while communicating with third party datasources which does not support windows authentication like Oracle.

2. Encrypt the package with “EncryptSensitiveWithPassword” or “EncryptAllWithPassword” and provide a package password each time User wants to edit/manipulate the package.

3. Create a configuration file to provide the connection information during Package runtime.

References:

How to: Add a Package Configuration: http://technet.microsoft.com/en-us/library/ms140213.aspx

Setting the Protection Level of Packages: http://msdn.microsoft.com/en-us/library/ms141747.aspx

 

B:

Error:

0xC0202009 at Package, Connection manager “<NAME>”: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: “Microsoft OLE DB Service Components”
Hresult: 0x80040154 Description: “Class not registered”.

Symbolic Name: DTS_E_OLEDBERROR

The hexadecimal value for this error number = 0x80040154.

Cause:

This is a very common error when a package is deployed in a 64-BIT environment and the package is using Microsoft Providers that is not available in 64-BIT (e.g. Microsoft JET provider and Microsoft provider for Oracle).

The Integration Services design environment is a 32-bit environment and you see only 32-bit providers while you are designing a package. Therefore, you can only configure a connection manager to use a specific 64-bit provider if the 32-bit version of the same provider is also installed. At run time, the correct version is used, and it does not matter that you specified the 32-bit version of the provider at design time. The 64-bit version of the provider can be run even if the package is run in Business Intelligence Development Studio. Both versions of the provider have the same ID. To specify whether the Integration Services runtime uses an available 64-bit version of the provider, you set the Run64BitRuntime property of the Integration Services project. If the Run64BitRuntime property is set to true, the runtime finds and uses the 64-bit provider; if Run64BitRuntime is false, the runtime finds and uses the 32-bit provider.

When the package is deployed as a job in a 64-BIT Sql Server, it would fail with the above error as it will use the 64-BIT DTExec.exe (C:\Program Files\Microsoft Sql Server\DTS\Binn\DTExec.exe) to run the job and it will not be able to recognize the 32-BIT providers.

Resolution:

1. When the package fails as a job, we can provide two resolutions:

    A) Modify the job step of the failing package, change the “Type” to Opearting System (cmdExec) and edit the command line manually to run it through the 32-BIT DTExec.exe. You need to prefix the full path of 32-BIT DTExec.exe before the command (Typically, under default installation, the path would be (C:\Program Files (x86)\Microsoft Sql Server\DTS\Binn\DTExec.exe).

   B) Change the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\Setup\DTSPath” to point to the 32-BIT DTExec.exe. This way it would invoke all the jobs through this 32-BIT DTExec.exe.

Note: The method A) applies to a specific job while method B) would apply to all the jobs deployed in Sql Server. In SQL Server 2008, there is an option in the job step properties page to use 32-bit instead of 64-bit. Under 2005, though, the only way to control it is to use a CmdExec step to call the 32-bit version of DTEXEC.

2. There would be scenarios where this error is encountered when a package is executed through a .NET program using the SSIS APIs(using LoadFromSqlServer(), Execute()). In that scenario, we need to change the Target Platform to “x86” from the Project Properties.

3. There might be scenarios where the providers used in the connection managers would be missing from the System where the package is run. In that case, the execution of the package would fail BOTH from BIDS as well as from Sql and the resolution would be install the appropriate provider.

References:

Integration Services Considerations on 64-bit Computers: http://msdn.microsoft.com/en-us/library/ms141766(SQL.90).aspx

MSDN Social Link that has a good discussion regarding this error: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/b1d5de28-b80b-4f34-a135-c68e87039c58/

 

C:

Error:

An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.The AcquireConnection method call to the connection manager “<Name>” failed with error code 0xC0202009.

Symbolic Name: DTS_E_OLEDBERROR

The hexadecimal value for this error number = 0x80004005.

Cause:

The owner of the job determines the security context in which the job is run. If the job is owned by a SQL Server login account that is not a member of the Sysadmin fixed server role, the SSIS package runs under the context of the SQL Server Agent proxy account.

If you want the SQL Server Agent proxy to run jobs that connect to an instance of SQL Server, the SQL Server Agent proxy account must have correct permissions to the instance of SQL Server.

If you want the SQL Server Agent proxy account to run a job that runs as an SSIS package, the SQL Server Agent proxy account must have the Read permission and the Write permission to the Temp directory of the SQL Server Agent Service startup account.

Resolution:

 To resolve this issue, you must change the permissions for the Temp directory of the SQL Server Agent Service startup account. Grant the Read permission and the Write permission to the SQL Server 2005 Agent proxy account for this directory.

References:

Error message when an SSIS package runs that is scheduled to run as a SQL Server Agent job: “An OLE DB error has occurred. Error code: 0x80004005”: http://support.microsoft.com/kb/933835

 

D:
Symptoms:

Consider the following scenario:

·         You run a SQL Server Integration Services (SSIS) package in SQL Server 2005 or in SQL Server 2008.

·         The SSIS package contains a data flow task.

·         You run the SSIS package on a computer that has low available memory. The low-memory condition may occur temporarily or intermittently.

·         When you run the SSIS package, data buffers are written to disk because of the low-memory condition.

In this scenario, you may experience one or more of the following symptoms:

Symptom 1

Data is damaged before the data reaches the data flow destination. You find that the data in the data flow destination becomes the following values unexpectedly.

“” : NULL, blank, or empty string values

0 : Zero value or zero value that has higher precision and scale.

0-0-0 0:0:0 : Dashed zero date value

Note You will not receive an error message when you experience this symptom.

Symptom 2

You receive one of the following data type conversion error messages:

Error message 1
DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80004005 Description: “Invalid character value for cast specification”.

Error message 2
DTS_E_COLUMNSTATUSERROR. There was an error with input column “ColumnName” (IDnumber) on input “OLE DB Destination Input” (IDNumber). The column status returned was: “Conversion failed because the data value overflowed the specified type.”

Symptom 3

You receive the following constraint violation error message:


DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80004005 Description: “The statement has been terminated.”.
An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80004005 Description: “Violation of PRIMARY KEY constraint ‘PrimaryKeyName’. Cannot insert duplicate key in object ‘TableName’.”.


Additionally, if you run the same SSIS package again when memory is available, this issue does not occur. Consider the following scenario:

Cause:
When a low-memory-resource notification is sent to the data flow engine, the data flow engine writes data buffers to disk temporarily. This issue occurs because data values are set to NULL or zero incorrectly when the data flow engine writes data buffers to disk. Therefore, when the data flow engine reads data from data buffers and then writes data to the data flow destination, the data flow destination will contain damaged data values. The damaged data values may also result in data type conversion errors or constraint violation errors.

This error is published with the different resolution methods in the following Knowledge Base Article:
http://support.microsoft.com/?id=972498

 

Author : Debarchan (MSFT) , SQL Developer Engineer , Microsoft

Reviewed by : Ambuj (MSFT) , SQL Developer Technical Lead , Microsoft

Comments (31)

  1. H. Kilpatrick says:

    Thank you so much for highlighting this on this blog.  I have had this problem intermittently for YEARS on SQL 2005, and posted on MSDS forum and others and no one answered.  I knew the error messages were "bogus" because the jobs would re-run fine.  I had given up trying to solve it, and today it happened in a rather critical job and I searched again and found your blog that led me to the KB article and the hotfix.  Hope this works!

  2. Yadav says:

    Just want to say.. Thank you so much

  3. Everett says:

    Could not bulk load because SSIS file mapping object ‘GlobalDTSQLIMPORT              ‘ could not be opened.

    Seen this one?

  4. Debarchan - MSFT says:

    Hi Everett,

    The message that you have highlighted is typically seen when you run your SSIS Packages on an OS with UAC on (Windows Vista and onwards). PLease follow the workarounds mentioned in the KB article below and let me know if it adresses your issue:

    http://support.microsoft.com/?kbid=2009672

  5. Snehadeep says:

    It might happens when Sql Server Destination has a remote Sql Server specified in

    connection.At times it may fail on a clustered Sql instance as well because the Virtual Server name might make it think that it is a Remote Server

    Try the following -:

    1) Make sure that this component connects to a local Sql instance

    2) Put OLEDB Destination in place of a SQL destination

    3) Try windows authentication if you are using SQL authentication

  6. Senthil says:

    Thank you so much, I found the resolution 1-B very useful to solve the problem B. I didnt find this resolution anywhere so far. Good job.

  7. Chris says:

    I support a stored procedure that returns multiple different record sets (queries different tables) based on input parameters.  The sproc basically takes a table name as input and builds a query string; the last line of the sproc is "EXEC(@query)".  Thus, consumers must be able to handle differently dimensioned result sets (different columns, different row counts).  

    An external user is executing the stored proc using SSIS and is getting the following error:

    Error at Data Flow Task [OLE DB Source[72]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred.  Error code: 0x80004005.

    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid column name 'GlobalName'.".

    Error at Data Flow Task [OLE DB Source[72]]: Unable to retrieve column information from the data source.  Make sure your target table in the database is available.

    Additional Information: –> Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap)

    Anything I can do to help newbies to SSIS diagnose what to me is obviously a dynamic parsing issue?

    Thanks,

    Chris

  8. Debs says:

    Hi Chris,

    I am a little confused. As you said:

    "An external user is executing the stored proc using SSIS and is getting the following error:Error at Data Flow Task [OLE DB Source[72]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005."

    If the goal is to execute a sproc then an Execute Sql Task should be good enough and I dont understand why we are getting errors related to a Data Flow Task.

    Is your package design is something like this that you run the sproc via Execute Sql task and then use the tables subsequently in the downstream Data Flow Components. If yes, could you please try to Set the package property "DelayValidation" to "True" and see if that makes any difference?

    HTH,

    ~Debs

  9. manishkumar1980@gmail.com says:

    in SSIS OLEDB COMMAND:

    I  used command

    INSERT INTO testDest(testChar,testInt)

    SELECT ?,?

    it showed me syntax error or access violation.

    While when i cahnegd it to

    INSERT INTO testDest(testChar,testInt)

    values( ?,?)

    it executed succefully.

    surprised………………….

  10. Andy says:

    Do you have idea about this error message, and how to resolve it.

    Error Message: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "ORA-28000: the account is locked."

  11. Hi Andy,

    This error means that the Oracle account which you are trying to use is locked at the Oracle side (this can happen if the account is not used for a long time or there are a repeated number of failed connection attempts typically because of an incorrect password being provided). You need to work with your Oracle DBA and unlock that user account on the Oracle side..

    HTH!

    Debs

  12. Please help me ASAP says:

    SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source:

    "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and

    no default driver specified". /nSSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. S

    ource: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and n

    o default driver specified". /nSSIS Error Code DTS_E_OLEDBERROR.

  13. Debs says:

    Data source name not found – means the connection is not able to find the DSN that you may have created. Typically, in a 64 Bit Computer, you create a 64 bit DSN which wont be recognized by 32 Bit BIDS and hence the error. Try creating a 32 Bit DSN and let me know:

    c:WindowsSysWow64odbcad32.exe – This command will launch the ODBC Data Source Administrator in 32 Bit mode.

  14. Morri Bahrami says:

    I had this error and the reason for getting this error for me was the SSIS package was imported to the File System section of the Integration Services instead of being imported to the MSDB section. When I moved the package to MSDB it worked fine.

  15. Jaclynna says:

    I can't find anyone who can interpret this error for me.  Can you help please?

    [Validation Type]: Generic Transform Error

    [Validation Code]: -1071636471

    [Validation Description]: 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: "FOR XML could not serialize the data for node 'Address1' because it contains a character (0x001A) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.".

    [Validation Type]: Generic Transform Error

    [Validation Code]: -1073450982

    [Validation Description]: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC0202009.

  16. Hi Jaclynna,

    It looks like the FOR XML sql statement is throwing this error rather than the SSIS package itself. Can you run the same query directly in Sql Management Studio and let me know what's the result?

  17. Oracle does support Windows Auth says:

    you say oracle doesn't support windows auth. It does. But its still a crappy product.

  18. Correct, will update that part.

  19. Ammad says:

    We have a job that fails every 3 weeks or so with the following error. When you re-launch the job it works which puzzles me a bit as the db and instance are both available and not under contention either. Any ideas?

    Description: 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: "Statement(s) could not be prepared.".

    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E14  Description: "Invalid object name 'dbo.VFailureLogs'.".

    End Error

  20. Dave Gugg says:

    Thanks for posting this, it helped me solve an issue related to the first example you posted.

  21. A Wright. says:

    This article was a big help in explaining some very confounding phenomena.

  22. Richard says:

    Thanks so much for the blog. C was my problem and is now sorted.

  23. Karima says:

    I am receiving the following error, but it was temporaray. A step of the job failed for just 1 day…from next day the job was running fine. The error was:

    Message

    Executed as user: dccmaudsloader. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.4321.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  8:13:56 AM  Error: 2015-04-20 08:22:26.19     Code: 0xC0202009     Source: Data Flow Task OLE DB Source [574]     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x00040EDA  Description: "Warning: Null value is eliminated by an aggregate or other SET operation.".  End Error  Error: 2015-04-20 08:22:26.19     Code: 0xC0047038     Source: Data Flow Task SSIS.Pipeline     Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "OLE DB Source" (574) returned error code 0xC0202009.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  8:13:56 AM  Finished: 9:14:06 AM  Elapsed:  3609.43 seconds.  The package execution failed.  The step failed.

  24. Vishe says:

    Excellent Very useful

  25. PrudenceM says:

    Hi there, ever encountered the following:

    Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB erorr has occured. Error Code: 0x80004005 An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description:"Unspecified Error"?

  26. Mithun Todakar says:

    Thanks for the very detailed information 🙂

  27. Johan says:

    I am placing this comment out of desperation. I am trying to import data from an SQL stored procedure in an Excel worksheet.
    The procedure, when executed from SQL Server Management studio, runs perfectly, with no indication of any errors or warnings. From Excel, however, I get the following:

    “Warning: Null value is eliminated by an aggregate or other SET operation”

    and no data is placed in the sheet.

    Google with the following phrase:
    +excel “stored procedure” “Warning: Null value is eliminated by an aggregate or other SET operation”
    results in two references. The one is not applicable and the other is this page. Without the “+” before “excel”, I get lots of links of which none appears relevant.

  28. Alok says:

    Thank you very much. It is explain very well and in a simple way. I think, For Symptom #3, we need to raise RAM on server ?

  29. Grammar Fiend says:

    FYI – you have a type-o in this thread. Under the “Resolution” section, you reference the “Opearting” System, which I would assume you meant “Operating” System.
    Cheers

  30. Kishor Jadhav says:

    Hi,
    It’s a great post.
    can you please help me to know about below error.
    Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E07.
    An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80040E07 Description: “The statement has been terminated.”.
    An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80040E07 Description: “The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.”

    I really appreciate your help and support.

    Thanks,
    Kishor Jadhav
    kishorworld1@gmail.com

Skip to main content