Link to home
Start Free TrialLog in
Avatar of MohitPandit
MohitPanditFlag for India

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
ASKER CERTIFIED SOLUTION
Avatar of sameer2010
sameer2010
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MohitPandit

ASKER

It means physical temp tables are better as compare to global temp tables as they will eat up lot of temp memory.
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.
SOLUTION
Avatar of DrewKjell
DrewKjell
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
No, it can be on different network. This is the main reason; we followed flat files.
Thanks, I used physical staging tables for that.