December 3, 2024 at 3:13 pm
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?
December 3, 2024 at 3:48 pm
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
December 3, 2024 at 4:02 pm
I copied the above sql ( as it is )....in a ssis variable and called it from execute sql task....it is still not working 🙁
December 3, 2024 at 4:07 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply