asked
To transfer data from Source table to destination table in SSIS Package on updation/insertion
![alt text][1] [1]I have 2 tables named SourceTable(DATABASE_1) & DestinationTable(DATABASE_2) on 2 different Server SERVER_A and SERVER_B respectively. [2]I want to transfer all data of SourceTable to DestinationTable when SSIS PAckage Executes. [3]If any insertion is performed in SourceTable then next time when SSIS PAckage Executes it should insert only that inserted row at DestinationTable which is done at Conditional split by ISNULL(
Lookup.ID). [4]If any updation is performed in SourceTable then next time when SSIS Package Executes it should update that updated record at DestinationTable which is done at OLE DB COMMAND UPDATE DestinationTable SET Name = ?,Age = ?,Dated = ? where ID = ?; Every thing works fine {Both insertion & updation from SorceTable to DestinationTable} if i have less number of record (
ie.in Hundreds). Problem occurs when number of record are in LAKHS. I have 4,78,125 Records first time when i execute SSIS package for first time, it is performed well & inserts 4,78,125 records from SourceTable to DestinationTable. [5] Now i updated all record of Name column of SourceTable {just to test its working or not} by update [SourceTable_L] set name = name +'TEST' in SSMS NOW WHEN I EXECUTES SSIS PACKAGE FOR THIS TIME IT KEEPS ON PROCESSING BEING YELLOW IN COLOR & NEVER ENDS WITH NO ERROR(IT SEEMS LIKE IT GOT HANG) ![alt text][2] [1]: /storage/temp/1375-problem.png [2]: /storage/temp/1377-p2.png
When you say it "never ends", how long did you wait? Does any of the numbers change? Have you looked with for example SQL Profiler if anything is happening in the target database? In your case with the update, you will have one update command executed for each row in the source database. I would probably have done it some other way. It's just not effective to make that many lookups and updates. It doesn't make use of what SQL Server does best: Handling **sets** of data. An alternative way would be to fill a staging table in the Target database, and then issue a MERGE-command at the end of the job, to UPDATE and INSERT the appropriate rows from the staging table to the target table.
15 People are following this question.
