Post reply

incremental loading from oracle to sql server database using ssis

  • October 20, 2011 at 11:19 am

    #249799

    my scenario is that i have oracle source and i have to implement incremental load using ssis packages

    the requirement is :

    i have to filter the data in oracle it self based up on last_update_key which is coming from sql server destination to decrease load time .

    how to implement these type of packages ? plase guide me

    thank you

    srikanth

  • October 20, 2011 at 11:29 am

    #1397562

    Typically you would have a step collect the max dates from the tables being loaded and use those as inputs into the source in a data-flow.

    CEWII

  • October 20, 2011 at 11:41 am

    #1397577

    hi i am new to ssis

    can you eloborate it..

    oracle source(oledbSource)----oledb destination(sqlserver)

    i will collect it from sql server table (destination).......some max(updatedate) ...

    but "how to pass it this max(updatedate) (flter key ) to oledb source query" is my question

    if any one have ssis packages related to incremental load from oracle please send those packages

    my mail id is "yeskanth@live.com"

    thanks

  • October 20, 2011 at 11:52 am

    #1397588

    I would create a variable, probably at the package scope to hold the date/time.

    I would use an EXEC SQL task to do a MAX of the field in question. I would configure the results set parameter to single row, on the result set tab I would add an entry, the name would be "0" (the number zero) and the variable would be the one just created.

    At this point we have the maximum date to look at in a variable in the package.

    I would use a data-flow task

    Within the dataflow task

    I would add an OLEDB source, I would select the connection to the source database. I would use SQL Command for the Data access method. I would use a command like SELECT Field1, Field2, FieldEtc FROM dbo.TheTableIWantDataDFrom WHERE FieldContainingDateTime > ?

    Yes, that is a ? mark in the query, its important. I would then click on Parameters and map the Parameter0 to the variable we created and filled above.

    I would then add an OLEDB destination. Connect it to the Source. Then ddouble click on the destination. Select the destination connection. Select Table or view - Fast Load for the Data access mode, and then select the table. Click on mappings to the left of the editor, it will probably map everything it can, otherwise map the source to the destination fields.

    Click Ok, save the package and this is basically it..

    CEWII

Viewing 4 posts - 1 through 3 (of 3 total)

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