Problem with execute a procedure in SSIS with execute SQL task

Question

I write in three variables values from a DataBase using an Execute SQL task and if I check if values was correctly written with the breakpoint, everything seems to be correct. Now I want use the variables that were written before in an other execute SQL task to execute a procedure. The problem is that the value of the variables aren't correctly mapped (I think). I thought that the variables aren't global (in fact when I check for the variables the values are always zero while during the debug in the task in which I write the variables aren't zero). How can I solve this issue?

This is how I call the procedure:

exec FillCalendar ?, ?, ?, ?

(I have 4 variables).

This is the parameter mapping:

They are in the order of question marks in the query. They are all of INT type.

Are your parameters mapped correctly? Can you share the screenshot of your parameter Mapping window in Execute SQL Task? — Faisal Mehboob
– Faisal Mehboob, Commented Jul 9, 2019 at 16:45
Can you show us how those variables are getting set in the upstream Execute SQL Task? Is any of this business logic inside a loop? Is any of the business logic split between being inside a loop and outside a loop? — digital.aaron
– digital.aaron, Commented Jul 9, 2019 at 21:33
When I put a breakpoing in the sql task that will do the procedure, they are correctly set. What you want to see? The way in which I stored the variables from the DataBase or the general settings of the sql task in which there is the procedure? — MrCaptain Alex
– MrCaptain Alex, Commented Jul 9, 2019 at 22:19

Hadi · Accepted Answer · 2019-07-09 20:33:07Z

0

I've run into problems before where SSIS doesn't recognize the input parameters correctly in the Execute SQL Task.

My "best practice" is to now declare placeholder variables for all input parameters, and then work with the placeholders. For example, I might change your stored proc call to this:

DECLARE @Param1 VARCHAR(100)
DECLARE @Param2 INT
DECLARE @Param3 BIT
DECLARE @Param4 DATE

SET @Param1 = ?
SET @Param2 = ?
SET @Param3 = ?
SET @Param4 = ?

EXEC FillCalendar @ProcInput1 = @Param1, @ProcInput2 = @Param2, @ProcInput3 = @Param3, @ProcInput4 = @Param4

Where @ProcInput1, @ProcInput2, @ProcInput3, and @ProcInput4 are the stored proc's input parameter names. Obviously you'd declare your placeholders with the correct data type.

Additional Information

You can refer to the following links for more information about passing variables from and into SSIS tasks.

edited Jul 9, 2019 at 20:33

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

answered Jul 9, 2019 at 15:15

digital.aaron

5,7152 gold badges27 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MrCaptain Alex Over a year ago

Same problem as before. The SQL task goes on but the procedure didn't do anything.

MrCaptain Alex Over a year ago

I think it's a problem of variables because are written in a previously SQL task and I think after are lost.

Hadi Over a year ago

@MrCaptainAlex i added two links to digital's answer, check them in order to now if you are reading variables correctly

MrCaptain Alex Over a year ago

I followed exactly the guide of the second link. I can't understand what the problem is. I load the values from a table in the database through a sql task and with another sql task I execute the procedure. I don't understand what the mistake could be.

Collectives™ on Stack Overflow

Problem with execute a procedure in SSIS with execute SQL task

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related