1

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:

enter image description here

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

MrCaptain Alex
  • 169
  • 1
  • 13
  • Are your parameters mapped correctly? Can you share the screenshot of your parameter Mapping window in Execute SQL Task? – Faisal Mehboob Jul 09 '19 at 16:45
  • @FaisalMehboob I shared it. – MrCaptain Alex Jul 09 '19 at 20:49
  • 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 Jul 09 '19 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 Jul 09 '19 at 22:19

1 Answers1

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
digital.aaron
  • 5,435
  • 2
  • 24
  • 43
  • Same problem as before. The SQL task goes on but the procedure didn't do anything. – MrCaptain Alex Jul 09 '19 at 15:42
  • I think it's a problem of variables because are written in a previously SQL task and I think after are lost. – MrCaptain Alex Jul 09 '19 at 15:45
  • 1
    @MrCaptainAlex i added two links to digital's answer, check them in order to now if you are reading variables correctly – Hadi Jul 09 '19 at 20:33
  • 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. – MrCaptain Alex Jul 09 '19 at 20:39