| Author |
 Topic  |
|
|
dayve
Forum Moderator
USA
5820 Posts |
 Posted - 01 August 2005 : 18:28:45
|
I've been working with Oracle for too long and can't remember how to do the simple stuff or can not recall if it is possible.
During a DTS Import using MS SQL, is there a way to IGNORE errors and proceed with importing all the data it can? I've noticed with a DTS Import that as soon as it encounters an error, the process stops and no data is imported (seems rather silly to me). I would like to set it up so that it just ignores that bad row and proceeds with the data import.
Thanks.
Dayve |
 |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 01 August 2005 : 18:38:37
|
| it would be much better to work out what the error is and fix it, rolling back the transaction on errors is not "silly" just safe. |
 |
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 01 August 2005 : 18:48:02
|
Unfortunately, the database I am importing the data from allows crap data from time to time that kills the import. Since I have no control over the data being allowed in the source table, I don't have the ability to correct the bad records, nor the time since there is over a million records being imported. It is just a snapshot of data that is being used for a web application that can not access the data directly.
The error is on a date field. I found the problem this time and was able to correct it and allow the import to proceed. But I'd like to prevent the error from happening in the future and just ignore the row that has the bad data. If I do a conversion of all fields to varchar the problem does not exist, and I can format the data as needed in my code, but I'd rather find out if I can just ignore the row instead using a DTS import parameter/variable. |
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 01 August 2005 : 19:27:09
|
| When you specify a transformation task, you can specify the max error count. This value is the number of errors that need to occur before DTS aborts the whole thing. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 01 August 2005 : 19:51:54
|
quote:
Originally posted by ruirib
When you specify a transformation task, you can specify the max error count. This value is the number of errors that need to occur before DTS aborts the whole thing.
is that done in the DTS Package Designer or during the DTS Import Assistant?
found it, will play with it a bit. thanks. |
|
Edited by - dayve on 01 August 2005 19:53:59 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 01 August 2005 : 19:59:16
|
| I also found a "Always Commit Final Batch" option which seems as if it might also help. I'll find out tomorrow. |
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 02 August 2005 : 06:49:24
|
quote:
Originally posted by dayve
I also found a "Always Commit Final Batch" option which seems as if it might also help. I'll find out tomorrow.
This one will set DTS to commit all successfull rows before an error occurred, instead of rolling them back. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 02 August 2005 : 14:40:51
|
quote:
Originally posted by ruirib
quote:
Originally posted by dayve
I also found a "Always Commit Final Batch" option which seems as if it might also help. I'll find out tomorrow.
This one will set DTS to commit all successfull rows before an error occurred, instead of rolling them back.
did my fiddling this morning... works pretty well, although I wish there was a complete ignore instead of having to use an absolute number. however, setting the value to 9999 worked fine for this project. |
|
|
|
| |
Topic |
|
Topic Locked
