SSIS - OleDBCommand issue with CLR Sproc

I'm writing an SSIS package to move data from a staging environment to our production server.

I have a CLR stored procedure esp_ProcessStagingXML which has 4 parameters, 2 of them output parameters..

I'm trying to call this CLR sproc once per item in the dataflow of my SSIS package.

I've created the OLEDBCommand, with the following SqlCommand property:

exec [esp_ProcessStagingXML] ?, ?, ? output, ? output

however I'm getting the following error no matter what I've tried to do:

Error at Move Documents to XXX [Insert Into XXX[16]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occured.  Error code: 0x80004005.
An OLE DB record is availabl.  Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005
Description: "Syntax error, permission violation, or other nonspecific error"..

I've tried running this under the sa account to verify there are no security issues involved. I am unable to map columns to parameters because of htis, and it fails every time I click Refresh. I don't have any issues w/ explicit select statents or sprocs which are not CLR sprocs. Has encountered / found solutions to this?

USE [XXXX]
GO

/****** Object:  StoredProcedure [dbo].[esp_ProcessStagin开发者_开发知识库gXML]    Script Date: 02/15/2011 15:31:35 ******/
ALTER PROCEDURE [dbo].[esp_ProcessStagingXML]
    @param1 [nvarchar](max),
    @param2 [bigint],
    @param3 [bit] OUTPUT,
    @param4[nvarchar](max) OUTPUT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [PullFromStaging].[StoredProcedures].[esp_ProcessStagingXML]
GO

This is on Microsoft Sql Server 2008 R2.

I resolved this by wrapping this sproc in another sproc, which called the CLR sproc.

hacky but seems to work.

Take a look at CLR Integration Code Access Security.

I suspect that when you created the Assembly in SQL Server you did not grant a sufficient PERMISSION SET.