Getting yesterday's data using SSIS

Question

Getting yesterday's data using SSIS

hepsi_P

Say Hey Kid

Points: 663
More actions
January 21, 2009 at 5:14 am

#130308

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

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[/url]

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.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

hepsi_P Say Hey Kid Points: 663 More actions · Answer 2

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.

deepan.j SSC Rookie Points: 41 More actions · Answer 3

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.

hepsi_P Say Hey Kid Points: 663 More actions · Answer 4

Can you please explain me giving some code?

Thanks.

Tim Wilson-Brown Ten Centuries Points: 1395 More actions · Answer 5

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.

hepsi_P Say Hey Kid Points: 663 More actions · Answer 6

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.

Tim Wilson-Brown Ten Centuries Points: 1395 More actions · Answer 7

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 🙂