SSIS Package Errors using OLE DB Command transformation

Question

I recently begun upgrading a MS SQL Server 2005 instance to MS SQL 2012. I have created a VM and installed MS SQL Server and used backups to create the DBs on the new server.

In Visual Studio I was switching my old connections to the new connections and when I switched the connection of an OLE DB Command Transformation that contains the command "UPDATE JOB SET Active = 0 WHERE Job.JobId = ?" I received the following errors.

Error 1 Validation error.

 Sync Jobs: Sync Jobs: 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 11.0"  Hresult: 0x80004005  Description: "The
 metadata could not be determined because statement 'REVERT       
 --Check if SSB is enabled in this database' in procedure 'sp_send_dbmail' 
 does not support metadata discovery.".  JobPack.dtsx 0 0

Error 2 Validation error.

 Sync Jobs: Sync Jobs: Unable to retrieve
 destination column descriptions from the parameters of the SQL
 command.   JobPack.dtsx 0 0

I checked to make sure this "SSB" was on in the new DB (it wasn't) but even after enabling it I still cannot resolve this error. I switch back to the old server and it works fine...

UPDATE: I was wondering where the email procedure came into play and I found that the DB has a trigger that emails when updated. Disabling this trigger resolves the SSIS package issue. I still have no idea why BIDS shows an error in the editor when this trigger is enabled.

Net Dev · Accepted Answer · 2013-03-04 17:23:11Z

1

I was able to resolve this issue simply by switching from the Native SQL Driver to the MS SQL Driver on the transformation. I still have no idea why that works and why this caused a compatibility problem.

answered Mar 4, 2013 at 17:23

Net Dev

4164 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Josh Crozier · Accepted Answer · 2015-12-11 01:31:27Z

0

I came across a similar error.

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "The metadata could not be determined because statement '....' uses a temp table.".

Work around.

If SP uses a #table or ##table and it is used in the SP, then we need to specify the #table structure along with the EXEC.

The SP should be given along with the structure.

EXEC SP_TestTemp 1,2

it should be given like

EXEC SP_TestTemp 1,2 WITH RESULT SETS
(
(
id      int,
Marks   int
)
)

Note: the 'retain same connection = true' and 'validate external metadata' = false did not help/work here.

edited Dec 11, 2015 at 1:31

Josh Crozier

242k56 gold badges401 silver badges316 bronze badges

answered Oct 23, 2014 at 13:39

Jithin Shaji

6,1015 gold badges27 silver badges47 bronze badges

Collectives™ on Stack Overflow

SSIS Package Errors using OLE DB Command transformation

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related