SSIS handle and access detailed OLEDB connection error records?

• Rob McCauley

  SSC Veteran

  Points: 205

  More actions

  September 12, 2011 at 2:20 pm

  #244638

  Hi all,

  When handling a connection error, I am trying to get my hands on the "available OLEDB error records".

  I have a package that verifies each of several OLEDB connections within a ForEach Loop.

  For each loop, it sets the Connection Manager's ConnectionString via Expression. It then attempts to run a simple Execute SQL (select) task.

  When a connection fails, I trap it in an Error Handler script task set on my Execute SQL task.

  Now, the Script task can access the error description via :

  Dts.Variables["System::ErrorDescription"].Value

  I would like to dig deeper to get the detailed OLE DB error messages. How can I retrieve the additional OLE DB records that are "available" with the error?

  For example, the package Output shows the following (line numbers added by me) :

  1. Error: 0xC0202009 at MyPackageName, Connection manager "sourceDB": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

  2. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Login failed for user 'MYHOST\rmccauley'.".

  3. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Cannot open database "MyTargetDB" requested by the login. The login failed.".

  4. Error: 0xC00291EC at MyTaskName, Execute SQL Task: Failed to acquire connection "sourceDB". Connection may not be configured correctly or you may not have the right permissions on this connection.

  My Error Event Handler is only showing the error text for line 4. Line 1 (and 2, 3) seem to be firing at the top-most package level, based on the "MyPackageName" error location.

  Lines 2 and 3 are far more descriptive than the error in line 4. Any ideas how to trap and use these? I would like to be able to handle them at the Execute task level, so I can allow the ForEach loop to continue processing. Is there an array of OLEDB error messages somewhere?

  I am setting the Propagate variable to False and ForceExecutionResult is set to False. Using SQL Server 2008 R2 on Windows Server 2008 R2.

  Thanks in advance!

  Rob McCauley

  Autonomy, Inc

  Southborough, MA