Call dynamic sql storedprocedure from SSIS execute sql task

Question

Call dynamic sql storedprocedure from SSIS execute sql task

komal145

SSCrazy Eights

Points: 9918
More actions
December 3, 2024 at 3:13 pm

#4492691

hi,
I have a table called Rules
Create table Rules
(
Id int ,
Rules Statement Nvarcahr(max)
)
values
Id RulesStatement
1 Sp_execute_rules @job_id , @run_id,@createid

So , i will be passing the parameters from my ssis package and calling the storedprocedure.
this is how i am calling in execute sql task
Declare @sql nvarchar(max) ,@job_id int, @run_id int @createid int
SET @sql = REPALCE(REPLACE(Replace (RulesStatement,@job_id ,Cast(@job_id AS INT))
, @run_id ,Cast(@run_id AS INT))
,@createid ,Cast(@createidAS INT))
FROM Rules

Execute sp_executesql @sql

But this is not working from execute sql task. It runs when i manually set the paramaters and test in ssms , it works fine.
Please help me what am i doing wrong?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246970 More actions · Answer 1

Build your dynamic SQL statement as an SSIS variable and then, within your ExecuteSQL task, set the SQLSourceType property to 'Variable' and designate your derived SQL variable as the 'SourceVariable' for the task.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

komal145 SSCrazy Eights Points: 9918 More actions · Answer 2

I copied the above sql ( as it is )....in a ssis variable and called it from execute sql task....it is still not working 🙁

Phil Parkin SSC Guru Points: 246970 More actions · Answer 3

komal145 wrote:

I copied the above sql ( as it is )....in a ssis variable and called it from execute sql task....it is still not working 🙁

You misunderstood my suggestion.

Create an SSIS variable and make it so that its result evaluates to whatever is in your @sql variable.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kaj SSCommitted Points: 1605 More actions · Answer 4

Phil Parkin wrote:

komal145 wrote:
I copied the above sql ( as it is )....in a ssis variable and called it from execute sql task....it is still not working 🙁
You misunderstood my suggestion.
Create an SSIS variable and make it so that its result evaluates to whatever is in your @sql variable.

There are loads of tutorials on this topic on youtube.com (I think - could have misunderstood the problem). For example this one:

https://www.youtube.com/watch?v=ul-TTqrhZjA