Trying to call a T-SQL Stored Procedure which selects data from linked server in SSIS package

Question

I have a scenario where I am trying to select some data in a table t1 & t2 from a remote Server (on which I just have read permissions) S1 in DB db1 from another remote Server(on which I am DBO but dont really have all the permissions so that I can do whatever I want) S2 in DB db2 into table t1 through a SSIS package.

S1 and S2 both are linked servers. I linked to S1 from S2 through object server in SSMS.

Now, I created a stored procedure sp1 in S2.db2 which has some select statements from two different tables with a join for a range of dates which are passed as parameters to the sp1.

for example as below :

SELECT * from s1.db1.schema1.t1 LEFT JOIN s1.db1.schema1.t2 ON [CONDITION] WHERE [CONDITIONS] Now my SSIS package has a Dataflow task which has OLE DB source and Destination with a connection string to s2.db2

In the source I am calling the above mentioned query in the SQL Command directly and populating into the destination table which is S2.db2.t1 and it is working fine

But it is throwing an error when I am trying to do the following

1. Create 2 package level DATETIME variables as v1, v2 and pass default values - No Problem
2. In the OLE DB SOURCE->CONNECTION STRING -> SQL COMMAND -> EXEC sp1 ?, ? - No Problem
3. click on PARAMETERS tab and select both the user parameters for Parameter0 and parameter1 - No problem
4. Now when I say ok for the SQL COMMAND Window its giving me the error as follows

The Error is :

TITLE: Microsoft Visual Studio

Error at FII54_CBI_TM51 [FII54_CBI 1]: 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: "Operand type clash: int is incompatible with date".

Error at FII54_CBI_TM51 [FII54_CBI 1]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

---

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap)

---

BUTTONS:

OK

I understand what the error mean but I dont understand why is it throwing this error at all.

I appreciate if someone can help me solve this issue? This is pretty urgent for me.

Answer 1

When using a stored procedure as an OLEDB source, you should make sure that inside the stored procedure you have:

SET NOCOUNT ON;

and then before you execute the procedure, add: SET FMTONLY OFF

SET FMTONLY OFF;
EXEC CBI_MASTER_PID ?, ?

Can you try this one out?