CONVERT DATETIME INTO DATE IN SSIS

Question

Post reply

CONVERT DATETIME INTO DATE IN SSIS

BI_NewBie

SSCrazy

Points: 2261
More actions
December 7, 2014 at 11:19 pm

#397745

Hello,
Need small help.
I have define two variable of Datetime type @Sdate and @Edate.
1. @Sdate = DATEADD ("DD", -5, GETDATE())
2. @Edate = GETDATE()
3. Using Forloopcontainer for pulling the data into batches
( @sdate = dateadd ( "HH" , 1, @sdate))
Now since i am using getdate() to define @Sdate. my variable gets data as ( 2014/12/08 11:43:00AM)
Can anyone help me in converting GETDATE( 2014/12/08 11:43:00AM) to only date
2014/12/08 00:00:00AM.
I tired using DT_WSTR which works fine (converting datetime int String).
Problem occurs when i am going through For Loop container . Since i have used assignment of @Sdate is says its not allowed.
Can anyone help how to do this.

Viewing 10 posts - 1 through 9 (of 9 total)

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

BI_NewBie SSCrazy Points: 2261 More actions · Answer 1

BI_NewBie

SSCrazy

Points: 2261

December 8, 2014 at 12:26 am

#1763196

Please reply somebody

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

i think its the extended version of your previous thread Dynamically hourly data extraction in SSIS 2012. I think you should be posting that on the same thread so that others also know the activity instead of opening a new thread.

if you would have been using script task, you wouldn't be having this trouble thou. you can do this in t-sql like this:

declare @Sdate datetime, @Edate datetime

select @Edate = cast(convert(nvarchar,GETDATE(), 101) as date), @Sdate = DATEADD ("DD", -5,@Edate)

select @Sdate, @Edate

but my main question is, if you are using ssis why you explicitly want to use the forloop logic, because whether there is a one million rows or 100 million its fetch the data in chunks itself, using its buffers.

have you tried just using the following:

1) source task select your table.

2) in destination task select your table with FastLoad.

and click run because as i am looking at it, you just need to copy the data from one table to other table or there is something else in this?

BI_NewBie SSCrazy Points: 2261 More actions · Answer 3

Thanks for your reply!

I will tell u what exactly i am doing.

I am pulling data from one server (using Stored procedure )and inserting the data into table. when i am doing for whole day it takes lot of time and create locking on my prod server whereas when i do it hourly it works fine.

Let me know if i am doing anything wrong. please suggest.

Regards

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

whats is average day data volume i means rows count? and whats is your hourly data count. I believe your SP is eating to much resources of your production server when you execute it and i have seen it as well.

is it complex like you are fetching from multiple data to generate a specific result set involving some major tables ?

BI_NewBie SSCrazy Points: 2261 More actions · Answer 5

Average row count = 12lakhs

hourly := 2-2.5 lakhs (include newly inserted and modified one)

Yes the SP is joining big transactions table (4 tables) and then getting the data. May be the join is creating an issue. Thats the reason i divided the data into hourly.

I tried checking the SP performance by Estimated and actual execution plan to check whether any index missing or something else.

Regards.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 6

1.2 millions isn't a huge chunk and your should check if your joins are creating huge scans which they might in this case because scans on huge table kill the performance. Better check the SP logic and identify the issue because 1.2 million is not that much of a chunk really.

2ndly, do you thought about fetching records on the end of the business which are called maintenance hours because at that time you there would be no activity (insert/update/delete) on the tables and 1.2 would be processed in no time?

BI_NewBie SSCrazy Points: 2261 More actions · Answer 7

we have to publish the data near real time thats the reason we are fetching the data hourly.

actually the production server resources also sucks

below is the config :

Processor : 2.9 GHZ (2 processor)

RAM : 32GB

Type : 64bit

MMartin1 One Orange Chip Points: 27795 More actions · Answer 8

I am still confused on the existence of the looping task. If you want to pull data hourly you can simply schedule the job to run hourly. Are you setting the values of your variables with an expression, so as to attain the proper start and end times? Do you need to do an initial pull of the data? If so consider the suggestion of doing that initial pull during off hours.

----------------------------------------------------

MMartin1 One Orange Chip Points: 27795 More actions · Answer 9

Another thing to consider... in your four tables, if you only pull records that are time stamped within that last hour that you want to fetch and you dont need all the columns returned by the procedure you call... try bringing in the data from the four tables into your staging area locally and create your own procedure locally to process the data in the way you want it. Or better yet, you can emulate this process all within SSIS thus bypassing staging tables. I just wonder if you are using some heavy procedure that previously existed for "off -label" use because you dont want to write a procedure for your particular situation (maybe you dont fully understand the data behind it), and this one just happens to contain the columns you need (but gets them in a brutal way). This may or may not work in your situation. Again, food for thought.

----------------------------------------------------