SSIS Variable

Question

Hi,

I am very new to SSIS. I have a source table that contains transaction information. I need to pull data from the source table into my staging table on daily basis. How can I design my package so that when it runs it tries to pull data ( for the previous day) and loads it into the staging table. ( The staging table needs to have data only for the current load). 

Do we need to use a SSIS variable to achieve this ? 

I guess when we want to have a special run we might need to use variable. In case if this can be achieved using a variable please suggest the appropriate steps to achieve this. 

Thank you,

eva

---

EVA05

Answer 1

Hi,

I think you can use variable and pass it as parameter for your query (the variable will contains yesterday date). then the result must be inserted into your staging table. I think it is better to truncate the staging table each time your package execute this will ensure your table has yesterday data only.

I hope this is helpful.

---

Please Mark it as Answered if it answered your question
OR mark it as Helpful if it help you to solve your problem
Elmozamil Elamir Hamid

MyBlog

Answer 2

Does your transaction table have date or datetime column you can use to filter the extract?

Answer 3

Hi There

I don’t think that you need to have a variable for that. Please see my query this is will help you out.

Please add dateadd built in function for transact SQL

I am putting screenshot for your help

Many thanks

Syed Qazafi

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

SELECT [RejectQty]
      ,[RejectBy]
      ,[RejDate]
  FROM [TEST_DWH].[dbo].[Reject]
  order by 
  [RejDate]desc
GO
SELECT [RejectQty]
      ,[RejectBy]
      ,[RejDate]
  FROM [TEST_DWH].[dbo].[Reject]
  where
  convert(date,[RejDate],103)=dateadd("d",-1,convert(date,getdate(),103))

Answer 4

Hi,

I dont think you need a SSIS variable.

If your source table has some date column like insert datetime then you can directly use that in sql command .

declare @date datetime

set @date = getdate()

select * from sourcetable where datecolumn = @date-1

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you