how to do incremental loading for each table

Question

Post reply

how to do incremental loading for each table

guru12

Ten Centuries

Points: 1068
More actions
July 6, 2010 at 12:50 am

#141848

Hi...........
How could I do incremental loading for each table in the database instead of writing a seperate dataflow task or package for each table in the databasee. I know I could configure the source and destination tasks from variable, but how will i do it for the lookup transformation task. Please advice
Regards..
Anil..
Regards..
guru12

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

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

guru12 Ten Centuries Points: 1068 More actions · Answer 1

Hi..

If any one answered to this qusetion previous,please provide the link that would be helpful to me a lot.

Regards..

Anil...

Regards..
guru12

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

As I read your question you want to copy data for each table from a list to a destination that has the same table name and structure. That about spell it out?

With the MS provided tools this is not possible. The column meta-data in the data-flow is set at design-time and CANNOT be changed at run-time. With that said, there is a company called CozyRoc that has a component that MAY be able to do this for you.

CEWII

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Elliot is right, you can't do it with out-of-the-box components.

However, you can achieve it with smart T-SQL scripting.

Create a transfer list of all the tables that you want to load. This list contains the table name, the schema name and the business keys of the table.

Loop over the tables that you want to load and dynamically create the following statements for each table:

* a left outer join on the business keys to check if a record is an update or not. Store the ID's from source and destination in a temp table. This allows you to quickly identify inserts or deletes based on simple integers.

* an update statement, using the 2 ID's from your temp table

* an insert statement (every row from the temp table that has destination ID null)

You can get all the columns needed from the Information_schema.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP