9

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.

The input parameters for SP

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

Passing the query directly

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

Error while calling the SP

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.

vineela
  • 91
  • 1
  • 4
  • 1) `sp_help sp1` Edit your ticket with information about the parameters the procedure takes. 2) On the OLE DB Source, grab a screen print of the Set Query Parameters window (available when you click Parameters...) 3) Grab a screen print of your Variables window. 4) Can you create a dummy proc, sp2, on the same database as sp1? Let it have the same signature as sp1 but do not query any tables and certainly don't hit the linked server. This will help identify whether it's a linked server issue, your query or your package. – billinkc Nov 08 '11 at 02:50
  • Hey..I dont really understand the neccessity of the steps you suggested above.But coming to the last point, that is identifying the problem, I am pretty sure that the issue is not with the query or package. I also dont think the issue is with Linked server coz when I try to paste the linked server query which is inside the sp1 directly it works just fine.The query is also fine coz it worked both in SSIS package and SSMS and the same with the package.I believe its something to do with calling SP and the Linked servers together in SSIS package.I never worked with this combination before. – vineela Nov 08 '11 at 15:30
  • You may know that, but we the people attempting to provide possible answers to your problem do not know that. It *sounds* like a data type mismatch is taking place therefore, I asked for details about the interface between the two systems. Having concrete evidence of the metadata of the procedure, the parameter mapping and SSIS types would allow people who want to help you see the details of the problem. We may discover it's not a mismatch but ends up being a permissions issue but I'd rather eliminate the probable causes before I hunt down something obscure. – billinkc Nov 08 '11 at 15:42
  • Gotcha!!! But when I am trying to attach a screen shot as image, it giving me the following error : Oops! Your edit couldn't be submitted because: We're sorry, but as a spam prevention mechanism, new users aren't allowed to post images. Earn more than 10 reputation to post images.Please let me know if there is any other way taht I could perform this. – vineela Nov 08 '11 at 17:26
  • You should have sufficient reputation shortly, asked some people to upvote the question. – billinkc Nov 08 '11 at 17:39
  • Try wrapping the question marks with single-quotes and report how that went. – gonsalu Nov 12 '11 at 13:05

1 Answers1

2

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?

Nonym
  • 6,199
  • 1
  • 25
  • 21