GunjanDixit
Programmer
- May 11, 2015
- 1
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 –
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 –