Post reply

Dynamically hourly data extraction in SSIS 2012

  • December 1, 2014 at 2:07 am

    #301571

    Hi Folks,

    Please help me in below query.

    I am using SSIS package for pulling the data(last 2 months data).

    Since the data size is huge, i have to split the data into hourly basis and pull the data.

    Can somebody help me how i can make this dynamic? Right now i am changing the hours manually after package execution.

    Please help!!

    Thanks!

  • December 1, 2014 at 2:11 am

    #1761618

    Use a query as the source for your data (and not just a table).

    select ...

    from ...

    where DateModified > dateadd(hour,-1,getdate())

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 1, 2014 at 2:15 am

    #1761619

    Hi,

    Thanks for the reply.

    May be i have not cleared my question properly.

    My transaction system has huge data and i have asked to pull the data of last 2 months in my database. For that i have designed an ETL.

    below is the way i am pulling the data

    Select * from table

    where date >= '2014-10-01 11:00:00' and date < '2014-10-01 16:00:00'

    Now my problem is i dont want to hardcode date range since the data is of 2 months. i want to make dynamic package which automatically pick the date range of every 4 hours starts from Oct to till date.

    Thanks a lot !!

  • December 1, 2014 at 2:35 am

    #1761625

    Ah, so you want to do the load in 4-hour batches – in a loop?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 1, 2014 at 2:39 am

    #1761626

    If so, you could use a FOR loop with an increment of 4 and use an expression to set your source SQL which references the loop variable in its WHERE condition.

    Here is one example that should get you started.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 1, 2014 at 2:43 am

    #1761627

    Yes Exactly!!

    want to process in batches.

    Can u please help

  • December 1, 2014 at 5:13 am

    #1761661

    Thanks for the link!! Really appreciate!

    However i am not able to figure out how to do this with Date column and how to pass to SQL command :crying:

  • December 1, 2014 at 11:35 pm

    #1761943

    please reply somebody!

  • December 2, 2014 at 12:08 am

    #1761945

    what exactly the issue you are facing right now?

  • December 2, 2014 at 12:11 am

    #1761946

    HI!

    Thanks for the reply!

    I have to create batch of data column (Hourly batch) . Not able to understand

    how to write ETL for processing data into batch.

    Please help!

  • December 2, 2014 at 12:16 am

    #1761948

    i actually meant that were actually you are facing problem in For Loop in ssis ?

  • December 2, 2014 at 12:26 am

    #1761950

    OK!!

    Below is the way i am following :

    1 Created three variable Query, Sdate, Edate

    a. Query = SELECT * FROM Table where date<= sdate and date>=edate

    b. Sdate = '2014-01-01 04:00'

    c. Edate = '2014-01-01 06:00'

    2. Data Flow task : Source = SQL Command from variable (Query)

    Destination = Mapped to destination table

    Here, i have hard coded the variable values. Now i want to understand how i can auto increment date and pass to the variable into batches.

  • December 2, 2014 at 12:35 am

    #1761951

    BI_NewBie (12/2/2014)

    OK!!

    Below is the way i am following :

    1 Created three variable Query, Sdate, Edate

    a. Query = SELECT * FROM Table where date<= sdate and date>=edate

    b. Sdate = '2014-01-01 04:00'

    c. Edate = '2014-01-01 06:00'

    2. Data Flow task : Source = SQL Command from variable (Query)

    Destination = Mapped to destination table

    Here, i have hard coded the variable values. Now i want to understand how i can auto increment date and pass to the variable into batches.

    Try changing your query to an Expression which uses the variable from the FOR loop in its WHERE clause to select the data in batches.

    The type of FOR loop I have in mind is exemplified here as a 'Counter loop'.

    Make your OLEDB source dynamic in a similar way to this. Obviously, this expression needs to reference the loop variable from your FOR loop.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 2, 2014 at 12:42 am

    #1761953

    Thanks a lot Phil!!

  • December 5, 2014 at 6:52 pm

    #1763100

    Are you doing a lot of transformations to this data? SSIS is effiecient in its resource use when you have non blocking data movements. (IE.. no aggregating, sorting....). How is the performance when you pull all the data as opposed to doing it in chunks? I am curious what your setup looks like and why you need to break up the ETL into iterations.

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

Viewing 15 posts - 1 through 15 (of 15 total)

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