Post reply

CONVERT DATETIME INTO DATE IN SSIS

  • 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.

  • December 8, 2014 at 12:26 am

    #1763196

    Please reply somebody

  • December 8, 2014 at 12:36 am

    #1763197

    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?

  • December 8, 2014 at 1:26 am

    #1763203

    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

  • December 8, 2014 at 2:37 am

    #1763215

    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 ?

  • December 8, 2014 at 3:06 am

    #1763218

    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.

  • December 8, 2014 at 3:20 am

    #1763219

    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?

  • December 8, 2014 at 3:26 am

    #1763220

    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

  • December 9, 2014 at 1:47 pm

    #1763644

    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.

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

  • December 9, 2014 at 1:58 pm

    #1763647

    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.

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

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

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