0

all.

I am using stored procedures from linked server. I want to use the procedure in ole db source.

I wrote the query, which works in SSIS.

select ID,

LAST_NAME_ENG,

LAST_NAME_G,

FST_NAME_ENG,

FST_NAME_G,

BIRTHDATE

from openquery (linkedserver, 
'exec [linkedserver].get_records @SESSION_ID = 12 , @SYSTEM = ''oCRM'', @ENTITY_NAME = ''CLIENT''

 WITH RESULT SETS (([ID] [int] NOT NULL,

[LAST_NAME_ENG] [varchar](50) NOT NULL,

[LAST_NAME_G] [varchar](50) NOT NULL,

[FST_NAME_ENG] [varchar](50) NOT NULL,

[FST_NAME_G] [varchar](50) NOT NULL,

[BIRTHDATE] [date] NOT NULL))');

I can use it in SSIS ole db source and successfully get required data. But in the next step there is the problem:

I need to pass the parameter to the @SESSION_ID from SSIS instead of '12'. And I cannot find the right way to do it.

There are a lot of advices to use dynamics sql and construct full query string with required parameter values and then exec it, but if I will do it - the SSIS couldnt get columns data from the dynamics query.

Are there ways to solve it? Any ideas will be helpfull.

Thank you.

With regards, Yuriy.

Yuriy D
  • 95
  • 1
  • 9

1 Answers1

1

Create a string variable, say, SQL_Query. In variable definition - set EvaluateAsExpression and define expression as "your SQL statement ... @SESSION_ID = " + [User::Session_ID_Variable] + " rest of SQL statement" where Session_ID_Variable contains your conditional value. If Session_ID_Variable is not string - you have to cast it to string with (DT_WSTR, length). Result in SQL_Query will be your target SQL expression.
Then in OLE DB Source - specify variable as SQL command source and select [User::SQL_Query].
Stored procedure have to return resultset of the same format in all cases. SP returning no resultset will fail DataSource.

Ferdipux
  • 5,116
  • 1
  • 19
  • 33
  • Hi, Ferdipux. Thank you very much for your answer. It works in common. But I can successfully validate and save the Ole DB Source block only when I am passing in the variable @SESSION_ID the identifier of the existing session. If such session does not exist - I am getting an error https://i.gyazo.com/5bbaf08a3884b8600439b6ea31212da1.png – Yuriy D Jun 09 '16 at 12:31
  • That is normal. In package design phase you need to validate all components; therefore, OLE DB source has to return some rows to determine metadata. At runtime null results will be ok. – Ferdipux Jun 09 '16 at 18:34
  • https://i.gyazo.com/7e076b41384c2e8003637618fa13c0b6.png Unfortunately it doesnt work( – Yuriy D Jun 10 '16 at 08:47
  • 1
    It was solved by updating calling stored procedure to return result set at any case. With or without records. – Yuriy D Jun 10 '16 at 12:05
  • Yes, you are right - SP should always return result set with the same structure. – Ferdipux Jun 10 '16 at 17:06