Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting yesterday's data using SSIS


Getting yesterday's data using SSIS

Author
Message
hepsi_P
hepsi_P
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
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.
Alvin Ramard
Alvin Ramard
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2676 Visits: 11590
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.

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
hepsi_P
hepsi_P
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
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.
deepan.j
deepan.j
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
hepsi_P
hepsi_P
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 56
Can you please explain me giving some code?
Thanks.
Tim Wilson-Brown
Tim Wilson-Brown
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 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.
hepsi_P
hepsi_P
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
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.
Tim Wilson-Brown
Tim Wilson-Brown
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 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 Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search