11

There was a communication link error while I was using SQL Server Native Client 10 in an SSIS Data Flow component.

Error: 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: "Communication link failure".An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "TCP Provider: The specified network name is no longer available.

So I changed from SQL Server Native Client 10 to Microsoft OLEDB Provider for SQL Server, hoping that it would run fine. This time got an error as shown below:

[Transacn_Tbl1[737]] Error: 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 SQL Server" Hresult: 0x80004005 Description: "Unspecified error occurred on SQL Server. Connection may have been terminated by the server.".

[Transacn_Tbl1[737]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (750)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (750)" 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.

What is the difference between these 2 providers? Which one should I use?

AHiggins
  • 6,719
  • 6
  • 33
  • 49
user1254579
  • 3,546
  • 17
  • 58
  • 95

2 Answers2

3

I ran into this issue after releasing a new SSIS project to the production environment.

At first, I was able to validate the package with success, but when I tried to execute the package, I got the DTS_E_OLEDBERROR. Error code: 0x80004005. message.

I tried to validate the package with the servername and password variables set to sensitive, but in this case the validation failed as well.

After setting the environment variables back to non-sensitive again and inserting the environment variable values, I was able to validate and execute the package.

To me it seems like in some cases the metadata in the SSISDB gets corrupted, but unfortunately I've not been able to point down the source of this issue.

I hope this will help someone. I've went through a lot of debug scenarios before I ended up with this (rather unsatisfying) solution.

Jouby
  • 1,192
  • 1
  • 17
  • 26
Mark
  • 31
  • 3
1

It looks like you have 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 Agent proxy account for this directory.

http://support.microsoft.com/kb/933835

Maverick
  • 1,131
  • 1
  • 8
  • 15
  • Hi ,I was not using the sql server agent – user1254579 Feb 06 '14 at 14:52
  • SQL SERVER Configuration Manager > SQL Server Network Configuration > Protocols for Protocols for > Click >TCP/IP>Double click TCP/IP> IPAddress > all Enable to “YES” > APPLY> OK – Maverick Feb 06 '14 at 14:57
  • 2
    Sometimes what you have to do is save the project. Close the BIDS/SSDT and then open and try to see if there are errors. If you don't see error try to run, it works perfectly fine. – Maverick Feb 06 '14 at 15:00
  • 2
    Thanks..done al these..similar question been asked by me 4months before..but nobody answered it.. – user1254579 Feb 06 '14 at 15:01
  • Ok.. Let's troubleshoot it quick.. First delete the connection to destination, add dataviewer and see that is coming from the source. Second add the destination and add the datview and see the data is right. Before transferring the data to destination truncate the table and run the package. it should work fine – Maverick Feb 06 '14 at 15:11
  • Please understand this is not a data issue..i have 1 year data to load,so i just splited it into 4 groups of 3 months data..then it works – user1254579 Feb 06 '14 at 15:20
  • Turn this function : Connection Manager properties > RetainSameConnection = True It should help. – Maverick Feb 06 '14 at 15:27
  • 2
    Anyone have any idea exactly HOW to do this? – Christine Nov 17 '16 at 00:27