October 28, 2011 at 11:27 am
Hello -
I‘m currently building an SSIS package to pull data from one db table and insert it into another db table on a different server. Then our users can use excel to connect and pull that data down to pivot how they like.
My current script looks like this.
SELECT *
FROM hgvBacklogLabor_Changepoint where Week between dateadd(dd,0,datediff(dd,0,(dateadd(dd,(datepart(dw, getdate())*-1 ),getdate())))) and dateadd(ww, 62, dateadd(dd,0,datediff(dd,0,(dateadd(dd,(datepart(dw, getdate())*-1 ),getdate())))))
What I need help with how can I keep historical data for example last week’s data and insert new data every time with a timestamp.
Thanks in advance for any insight into this.
David
October 28, 2011 at 11:30 am
In the data flow task, add a derived column, and put the date in that. That goes between the source and the destination.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 28, 2011 at 2:39 pm
Thank you for the fast reply. I added the Derived Column. I attached a screenshot of it. Do I take the date info from my script in insert it there? Or do I leave the GetDate()? Sorry I'm very new to SSIS. And will this then only update new data when the query is run to pull info in from the source?
thanks
October 29, 2011 at 12:34 am
If all that you are trying to do is to populate a DateCreated column in your table, why not just create a new datetime column with a default of GetDate() and let SQL Server do the work for you?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply