incremental loading from oracle to sql server database using ssis

Question

incremental loading from oracle to sql server database using ssis

skanth

Right there with Babe

Points: 730
More actions
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

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

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

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

skanth Right there with Babe Points: 730 More actions · Answer 2

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

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