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_ProcessStagingXML] 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.
