sql - 在多次执行SQL任务SSIS中使用可变参数

Question

大家好，所以我创建了两个变量:startdate 和 todate，我试图通过我在 SSIS 中的 SQL 查询传递它们，我已将它们添加到我的参数映射中，但是 SSIS 如何在看到第三个变量后使用哪个变量问号？

例如:SSIS 如何知道在插入 #multileg 时使用 startdate 而不是使用 todate 变量？

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 >= ?

Answer 1

Brad 的回答是一个很好的方法。另一种方法是再次将参数添加到您的参数映射。

第三种方法是在带有表达式的变量中构建 SQL 语句。然后在您的执行 SQL 任务中，您的 SQLSourceType 将是变量，然后您选择包含您的查询的变量。这是一种简单的方法，因此您可以避免为参数选择正确的数据类型。