MohitPandit
asked on
Import data through flat files to database, need suggestion
Hello,
I need to transfer data from one database to another database using SSIS on daily basis.
So, I am doing below steps:
1. Extract data in different flat files from source end.
2. Now, those flat files need to import into destination end.
While importing, I need to check either record is new/update based on IDENTITY value at destination end and also need to execute complex T-SQL for update one table after join with other tables.
So, I am planning for Import as below:
1. First, I will import data in staging tables (i.e. temp physical tables);
2. After that I can execute a stored procedure to making JOINs with staging tables and actual tables and can perform INSERT/UPDATE operation accordingly.
BUT, using aforesaid approach, I need to create staging tables (temp physical tables) for each tables respectively. For e.g., I need to import 50 tables on daily basis through flat files then I need to create 50 staging tables. SO, it will increase tables at destination end.
Of Course, before daily import, I'll TRUNCATE data from staging tables first then INSERT.
FYI, I need to import average 2 millions records for all tables on daily basis.
ALTERNATE:
1. Can I use Global Temporary tables for those?
--> If I will use ## tables then it need not to create physical temp tables on database.
QUESTIONS:
1. Can you please give advantages and disadvantages for both approach i.e. Physical temp tables VS. Global temp tables (i.e. ##tmp1)?
2. Do you have other alternate?
Best Regards,
MohitPandit
I need to transfer data from one database to another database using SSIS on daily basis.
So, I am doing below steps:
1. Extract data in different flat files from source end.
2. Now, those flat files need to import into destination end.
While importing, I need to check either record is new/update based on IDENTITY value at destination end and also need to execute complex T-SQL for update one table after join with other tables.
So, I am planning for Import as below:
1. First, I will import data in staging tables (i.e. temp physical tables);
2. After that I can execute a stored procedure to making JOINs with staging tables and actual tables and can perform INSERT/UPDATE operation accordingly.
BUT, using aforesaid approach, I need to create staging tables (temp physical tables) for each tables respectively. For e.g., I need to import 50 tables on daily basis through flat files then I need to create 50 staging tables. SO, it will increase tables at destination end.
Of Course, before daily import, I'll TRUNCATE data from staging tables first then INSERT.
FYI, I need to import average 2 millions records for all tables on daily basis.
ALTERNATE:
1. Can I use Global Temporary tables for those?
--> If I will use ## tables then it need not to create physical temp tables on database.
QUESTIONS:
1. Can you please give advantages and disadvantages for both approach i.e. Physical temp tables VS. Global temp tables (i.e. ##tmp1)?
2. Do you have other alternate?
Best Regards,
MohitPandit
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No, it can be on different network. This is the main reason; we followed flat files.
ASKER
Thanks, I used physical staging tables for that.
ASKER
Right?
Regarding linked servers, we need to follow flat files export/import option only using SSIS.
Can you please bifurcate advantage and disadvantages between GTT VS. physical temp tables? it would be great.