| Author |
Topic |
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
 Posted - 2009-03-10 : 13:34:05
|
| I have a package that does incremental Loading. Let's say it fails in the middle of inserting 50K records. However 20K records is inserted. TableA has many million records and many fields. How could I get get the missing 30K records to tableA. I could use a temp table and manually re-run the package to insert all the 50K records into it. Then use a join to insert the missing 30k records to tableA. However I will have to write a new SSIS package to do this and map the fields again. So much hassle. TableA has constraints and keys. What is a good approach to retrieve those missing 30k records? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-10 : 13:53:33
|
| the best approach is to put all tasks associated with insertion within a sequence container and make transaction property of container as required. this will launch a new transaction each time the tasks within sequence container executes and if any error happens in between all changes get rolled back and next time you can start from beginning itself. |
 |
|
|
tmitch
Yak Posting Veteran
60 Posts |
Posted - 2009-03-10 : 13:54:56
|
| You could use the Error Output of your destination to write out the errored rows to a separate table rather than fail the package. You could then address only those rows in the Error table rather than re-running your entire set of data.The alternative is to do a lookup on the target table, assuming there is a distinct value you can check, to verify that the record doesn't already exist. However, doing a lookup on a 1m row table could get expensive.hth,Tim---------------------Tim Mitchellwww.BucketOfBits.com |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
Posted - 2009-03-10 : 14:46:49
|
| visakh16,we won't use transaction as they look up the tables. That link wont work with over 10M records. It would be really slow. |
|
|
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
Posted - 2009-03-10 : 14:49:10
|
quote:
Originally posted by tmitch
You could use the Error Output of your destination to write out the errored rows to a separate table rather than fail the package. You could then address only those rows in the Error table rather than re-running your entire set of data.
It says cannot be set to redirect....then there is a red dot with a white X on the OLEDB destination |
|
|
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
Posted - 2009-03-11 : 13:35:32
|
| tmitch,I was using fast table load. Changed it back to "table or view" and it works now. Thanks man |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-11 : 14:17:29
|
quote:
Originally posted by basicconfiguration
visakh16,we won't use transaction as they look up the tables. That link wont work with over 10M records. It would be really slow.
sorry didnt get that. what's the problem with using transaction?also did you try using left join method in link? |
|
|
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
Posted - 2009-03-11 : 14:27:36
|
| I don't want to use Transaction is because Transaction locks up the table while other users want to read this table. Yeah I can use left join too. I have a unique field new_ID() type in the stage and final table. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-12 : 13:50:10
|
| ok.. |
|
|
|
|
incremental load. SSIS