SSIS OLEDB Command parameters error

Question

I cant call stored proc from OLEDB Command. Preview gets error:

[OLE DB Command [420]] Error: 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 10.0" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".

This sqlcommand works:

exec [dbo].[Update_PRODBOM_93Days] '1',5,'se1'.

but not this:

exec [dbo].[Update_PRODBOM_93Days] ?,?,?

My connectionstring is:

Data Source=tcp:sld51cxbyz.database.windows.net,1433;User ID=bleetmaa@sld51cxbyz;Initial Catalog=MigrationFromAx;Provider=SQLNCLI10.1;Persist Security Info=True;Packet Size=0;Application Name=SSIS-DaxPRODtoOEMDW-{2DA081B5-9B6B-403F-B354-F759BEAD952D}DestinationConnectionOLEDB;database=MigrationFromAX;encrypt=True;trustservercertificate=False;connection timeout=30;

if I change it to:

Data Source=sld51cxbyz.database.windows.net;User ID=bleetmaa@sld51cxbyz;Initial Catalog=MigrationFromAX;Provider=SQLNCLI11.1;Auto Translate=False;

it works.

Does not SQLCLI10 support oledb command?

My database is an Azure database.

My storedprocedure is:

CREATE PROCEDURE [dbo].[Update_PRODBOM_93Days]
    @PRODID nvarchar(20) 
    ,@PRODSTATUS int
    ,@DATAAREAID nvarchar(4)

AS
BEGIN

    update dbo.PRODBOM_93Days
       SET prodstatus = @PRODSTATUS
    where PRODID = @PRODID and DATAAREAID=@DATAAREAID
END

Use profiler to see what SQL is being sent by SSIS when you run the SQLCommand that doesn't work. — Tab Alleman

billinkc billinkc · Accepted Answer · 2015-01-21T15:37:24

The ? syntax is for an OLE DB driver. Since, this is Azure, you're using an ADO.NET driver which uses named parameters. Thus

exec [dbo].[Update_PRODBOM_93Days] @PRODID, @PRODSTATUS, @DATAAREAID;

And then map accordingly except this is an OLE DB Command which only uses an OLE DB Connection Manager...

SSIS OLEDB Command parameters error

1 Answers