CODEWITHSUNDEEP
sql-servertriggerssql-server-2012ssis
Net Dev
Net Dev

Reputation: 416

SSIS Package Errors using OLE DB Command transformation

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.

Upvotes: 1

Views: 6291

Answers (2)

Jithin Shaji
Jithin Shaji

Reputation: 6071

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.

Upvotes: 0

Net Dev
Net Dev

Reputation: 416

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.

Upvotes: 1

Related Questions