SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurr
|
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) 原因： 当在本地计算机创建一个SSIS包并在包中创建数据库连接时，你可以保存这个连接的密码。但密码会默认被加密，只有当在相同的计算机上用相同的用户执行包时密码才会被解密..... 在上面的出错场景中，如果SSIS包被部署在一个远程SQLSERVER服务器上，将会因为“登录失败”而执行失败..错误原因是无法解密密码。（PS: 部署在本地SQLSERVER的SSIS包运行无误） 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. 解决方法： 为解决这个问题，你需要从下面三个选项中选择一个来执行： 1. 把SSIS包中的所有连接改为windows认证。（PS：某些数据库如Oracle不支持windows认证） 2. 保存SSIS包时选择“使用密码加密敏感数据”或者“使用密码加密所有数据”，在每次打开或使用SSIS包的时候输入密码。 3. 在执行SSIS包时，使用配置文件来提供连接所需信息。（PS:在VS2008的包配置菜单中可以导出连接配置文件） 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: 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”? The hexadecimal value for this error number = 0x80040154. 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 FilesMicrosoft Sql ServerDTSBinnDTExec.exe) to run the job and it will not be able to recognize the 32-BIT providers. 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 ServerDTSBinnDTExec.exe). ?? B) Change the registry key HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTSSetupDTSPath” 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. 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. 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. ?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. 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:? ·?????????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? Error message 2? Symptom 3 You receive the following constraint violation error message:
Cause:? This error is published with the different resolution methods in the following Knowledge Base Article:? （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |