Sunday, April 29, 2012 2:22 AM
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.
Sunday, April 29, 2012 5:28 AM
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
Sunday, April 29, 2012 2:39 PMDoes your transaction table have date or datetime column you can use to filter the extract?
Sunday, April 29, 2012 11:59 PM
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
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))
Monday, April 30, 2012 12:02 AM
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