INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Pull data from multiple databases into one using SSIS package using one staging table per MAIN table

Pull data from multiple databases into one using SSIS package using one staging table per MAIN table

Pull data from multiple databases into one using SSIS package using one staging table per MAIN table

(OP)
In my current requirement, We have one DataMart database in which we pull data from many other databases.

The schema for The table may not be same across all the databases.

Question 1: is it possible to have one common SSIS package to pull data from all the source databases though schema may be different across the databases.

My understanding is as schema is not same and thus transformation will be different for each database. Hence I am considering separate SSIS package per source database.

SSIS package current design : truncate staging table -> pull data from source into staging table + transformation + add source column value [I need to keep track of datasource as well as a column i.e if source is DB1, DB2..] -> merge in main table with above design (truncate step as first step) I am forced to have one staging table per source database. (i.e if I have total 5 table and total 3 source databases then I need to have 15 staging tables)

Question 2 : is it possible to manage with one staging table for all source databases (i.e (i.e if I have total 5 table and total 3 source databases; still I can manage with just 5 staging tables/ common staging table for all source databases)

NOTE: SSIS packages may run simultaneously for DB1,DB2..

My idea for common staging table is define new column in staging table : "isRecordProcessed". SSIS package design :: step 1 - delete all records with isRecordProcessed true. step 2 - pull data from source into staging table + transformation + add source column value. Step 3 merge in main table step 4 mark this record as processed i.e isRecordProcessed=true.

But problem I see with solution is lets say while SSIS pkg1 is in execution SSIS pkg2 is also fired. Now as step 1 this will remove all the processed records of SSIS Pkg1. which may invalidate ongoing SSIS pkg1::forloop counter [MERGE step] and it may result in skipping some records as part of MERGE step –

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close