Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting yesterday's data using SSIS Expand / Collapse
Author
Message
Posted Wednesday, January 21, 2009 5:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 26, 2009 5:13 AM
Points: 29, Visits: 56
Hi Friends,

I have a table,
DateTime, Value as columns.

I have to fetch yesterday's value where I have to compare datetime.
I have used conditional split to get yesterday's value.
But, I have a problem.
I need to get datetime based on 15 minutes timeslot.
ie. consider today's date is 10/1/2009 00:00:00
then, I should retrieve values for yesterday's time, like,
9/1/2009 00:00 value
9/1/2009 00:15 value
9/1/2009 00:30 value
9/1/2009 00:45 value.
To get only yesterday's data i have used conditional split.
How to get these timestamps??? using SSIS.
Any idea.
Please help
Thanks all.




Post #640582
Posted Wednesday, January 21, 2009 10:14 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:01 PM
Points: 3,110, Visits: 7,844

I'm not sure I understand exactly what you're trying to do, but I'd like to offer an alternative solution.

Can you write a query that returns what you want? If you can, use that as your data source. You may find this to be more efficient than the conditional split.




Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #640932
Posted Wednesday, January 21, 2009 7:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 26, 2009 5:13 AM
Points: 29, Visits: 56
Hi all,
I would like to explain it more clearly.
I have a table. tbl_Readings.
The table structure is as shown below,
TimeStamp, Meter1, Meter2, Meter3, .... Meter n.
1/22/2009 00:00:00 200 300 400 ..........1000
1/21/2009 00:00:00 300 400 500 ........... 700
1/21/2009 00:10:00 100 100 100 ........... 100
1/21/2009 00:15:00 100 200 300 ........... 600
1/21/2009 00:30:00 900 400 100 ........... 200

I have the values like this. What I have to do is. .. I have to fetch yesterday's data , and store it in a new table. The condition here is I have to fetch yesterday's data, with 15 minutes gap in between. i.e, First row to be fetched is 1/21/2009 00:00:00, next row to be fetched is 1/21/2009 00:15:00, next row should be 1/21/2009 00:30:00. I have to do this using SSIS.
How to do this?
Please help me.
Thanks.
Post #641324
Posted Wednesday, January 21, 2009 8:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 11, 2013 11:40 PM
Points: 1, Visits: 16
U can try using a VB script task, after to get yesterday's date and increment it by dateadd() function for 15 min till start of present day.
Post #641346
Posted Wednesday, January 21, 2009 8:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 26, 2009 5:13 AM
Points: 29, Visits: 56
Can you please explain me giving some code?
Thanks.
Post #641347
Posted Wednesday, January 21, 2009 9:52 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 5:40 PM
Points: 209, Visits: 302
The suggested VB solution could be very slow because it involves generating each of the times separately.

Here are some other options:

Filter On Yesterday's Date/15 minutes by using a SQL Query in a SSIS Data Flow Source:

SELECT *
FROM tbl_Readings
WHERE TimeStamp = [Yesterday]
AND DATEDIFF(minute,[Yesterday],TimeStamp) % 15 = 0

Filter On 15 minutes by using a SSIS Conditional Split Expression
(which you sound most familiar with):

DATEDIFF( "Minute", [Yesterday], TimeStamp ) % 15 == 0


These give you times that are an exact multiple of 15 minutes from [Yesterday].
They may need to be modified to suit your situation.
Post #641363
Posted Wednesday, January 21, 2009 10:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 26, 2009 5:13 AM
Points: 29, Visits: 56
Hi,

In the first conditional split, I gave the expression as:

((DT_DBDATE)timestamp == (DT_DBDATE)DATEADD("dd",-1,GETDATE()))

which returns only yesterday's i.e, previous day's rows.

In the second conditional split, I gave the expression as:

((DT_DBDATE)timestamp == (DT_DBDATE)(DATEDIFF("mi",timestamp,timestamp)% 15 == 0))

I'm getting errors for the second condition. Can u please tell where I'm going wrong.
Thanks.
Post #641368
Posted Wednesday, January 21, 2009 10:37 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 5:40 PM
Points: 209, Visits: 302
You need to use the second expression I gave you, and substitute your expression for yesterday where I put [Yesterday].

Then you need to check that you actually get the results you're expecting :)
Post #641372
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse