Use variable parameter in Execute SQL Task multiple times SSIS

Question

hey guys so i created two variables: startdate and todate and I am attempting to pass them through my SQL query in SSIS and I have added them in my Parameter Mapping but how does SSIS which variable to use after if it sees a third question mark?

for example: so how would SSIS know to use startdate when it inserts into #multileg and not use the todate variable?

e 
--Declare @StartDate date
--declare @ToDate date

--set @startdate = dateadd(dd,-10, cast(getdate() as date))
--set @ToDate =  dateadd(dd,-9,cast(getdate() as date))





---SSR Table with passenger info, both APAY and PET
create table #SSRData

([ssrfl] int, [ssrcode] char(4), [ssrsequenceid] smallint, [ssrstatuscode] 
char(2), [servicestartcitycode] varchar(5), 
[ssrstartdate] date, [databasetimestamp] datetime, [pnrlocator] char(8), 
[pnrcreatedate] date, [passengersequenceid] smallint, 
[namefirst] varchar(250), [namelast] varchar(250), [frequenttravelernumber] 
varchar(25)


  )


  insert into #ssrdata
  select distinct ssrfl,
  s.ssrcode,s.ssrsequenceid,s.ssrstatuscode,s.servicestartcitycode,
  s.ssrstartdate, s.databasetimestamp, s.pnrlocator, s.pnrcreatedate
 ,s.passengersequenceid, namefirst, namelast,frequenttravelernumber
  --into #SSRData
  from 
  (select cast(ssrflightnumber as int)ssrfl,
  ssrcode,ssrsequenceid,ssrstatuscode,servicestartcitycode,
  ssrstartdate, pnrlocator, pnrcreatedate
 ,passengersequenceid,databasetimestamp from dwjetblue2.dw.resssr 
  where SSRCode in ('APAY', 'PETC') and PNRLocator <>'purged'
   and ssrstartdate  >= ? 
  and ssrstartdate <  ?)s
   inner join dw.dw.ResPassenger p 
   on p.pnrcreatedate=s.pnrcreatedate
 and p.pnrlocator=s.pnrlocator
 and p.passengersequenceid=s.passengersequenceid
 inner join dwjetblue2.dw.ResPassengerFT ft
 on ft.pnrcreatedate=s.pnrcreatedate
 and ft.pnrlocator=s.pnrlocator
 and ft.passengersequenceid=s.passengersequenceid

 --MultiLeg
  create table #multi
   (
  [pnrlocator] char(8), [pnrcreatedate] date 
  ,[segmentnumber] tinyint, [marketingflightnumber] char(5)
  ,[servicestartcity] char(3), [serviceendcity] char(3)
  ,[servicestartdate] date

   )
    insert into #multi

   select distinct
  pnrlocator p, pnrcreatedate d ,segmentnumber s,
  marketingflightnumber fl,
  servicestartcity sc, serviceendcity ec, servicestartdate sd
  --into #multi
  from  dw2.dw.resflight
  where servicestartdate >= ?

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

Brad's answer is a great way to do it. Another way is to simply add the parameter to your Parameter Mapping a second time.

A third way is to build your SQL statement in a variable with expressions. Then in your Execute SQL Task, your SQLSourceType would be Variable, and then you select the variable that contains your query. This can be an easy way to do it so you avoid messing around with picking the right data types for your parameters.

score 1 · Answer 2 · answered Nov 09 '18 at 18:36

You wouldnt at the top of your code declare variables for the dates passed and set the values from the paramaters/? makrs to those and just use the variables in your code below

DECLARE @StartDate as Date
DECLARE @ToDate as DATE

SET @StartDate = ?
SET @ToDate = ?

-- so do it like this:
Select * from your table where MyDateColumn Between @StartDate and @ToDate

Then use those in your code below where you need them.

This lets you use the variables more than once and is cleaner and easier to see that your using parameters and how/where.

*** Updated to use your code:

DECLARE @StartDate as Date
DECLARE @ToDate as DATE

SET @StartDate = ?
SET @ToDate = ?

---SSR Table with passenger info, both APAY and PET
create table #SSRData

([ssrfl] int, [ssrcode] char(4), [ssrsequenceid] smallint, [ssrstatuscode] 
char(2), [servicestartcitycode] varchar(5), 
[ssrstartdate] date, [databasetimestamp] datetime, [pnrlocator] char(8), 
[pnrcreatedate] date, [passengersequenceid] smallint, 
[namefirst] varchar(250), [namelast] varchar(250), [frequenttravelernumber] 
varchar(25)


  )


  insert into #ssrdata
  select distinct ssrfl,
  s.ssrcode,s.ssrsequenceid,s.ssrstatuscode,s.servicestartcitycode,
  s.ssrstartdate, s.databasetimestamp, s.pnrlocator, s.pnrcreatedate
 ,s.passengersequenceid, namefirst, namelast,frequenttravelernumber
  --into #SSRData
  from 
  (select cast(ssrflightnumber as int)ssrfl,
  ssrcode,ssrsequenceid,ssrstatuscode,servicestartcitycode,
  ssrstartdate, pnrlocator, pnrcreatedate
 ,passengersequenceid,databasetimestamp from dwjetblue2.dw.resssr 
  where SSRCode in ('APAY', 'PETC') and PNRLocator <>'purged'
   and ssrstartdate  >= @StartDate --? 
  and ssrstartdate <  @ToDate)s --?
   inner join dw.dw.ResPassenger p 
   on p.pnrcreatedate=s.pnrcreatedate
 and p.pnrlocator=s.pnrlocator
 and p.passengersequenceid=s.passengersequenceid
 inner join dwjetblue2.dw.ResPassengerFT ft
 on ft.pnrcreatedate=s.pnrcreatedate
 and ft.pnrlocator=s.pnrlocator
 and ft.passengersequenceid=s.passengersequenceid

 --MultiLeg
  create table #multi
   (
  [pnrlocator] char(8), [pnrcreatedate] date 
  ,[segmentnumber] tinyint, [marketingflightnumber] char(5)
  ,[servicestartcity] char(3), [serviceendcity] char(3)
  ,[servicestartdate] date

   )
    insert into #multi

   select distinct
  pnrlocator p, pnrcreatedate d ,segmentnumber s,
  marketingflightnumber fl,
  servicestartcity sc, serviceendcity ec, servicestartdate sd
  --into #multi
  from  dw2.dw.resflight
  where servicestartdate >= @StartDate

Hey Brad this isn't working for some reason. I get the following error when I use this code: [Execute SQL Task] Error: Executing the query " Declare StartDate as date declare ToDate as ..." failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. — Kamran, Nov 09 '18 at 20:25
Try just putting the DECLARE and set variable values = ? in the script task and execute that to see if it works. If not, then check that your using/setting paramaters correctly. Or if your paramater is not in date format you may need to put quotes around the ? like SET @StartDate = '?' — Brad, Nov 09 '18 at 20:28
Make sure your paramaters are correct like in Jermey J's answer below, see his screen shot (but you would only use 2 paramaters), and if nothing is being returned make sure the result set is false — Brad, Nov 09 '18 at 20:43

Use variable parameter in Execute SQL Task multiple times SSIS

2 Answers2