March 20, 2004 at 1:27 pm
I'm trying to use dts to move a set of about 200 tables into SQL 2000
I've created a dts package using the wizard. The "fast load" option has been enabled. In order to speed up the task, I would like to turn off constraint checking on all the transform tasks and also increase the maximum error count. (I have another program which will check for/correct/ and insert any records that failed in the dts)..
The only way I can see to do this is via the DTS designer by going to the property sheet of each transformation task..Is there a way of setting these properties quickly for ALL the transformation tasks?
Ilmar
March 22, 2004 at 6:57 am
I use a job with the following steps:
Disable foreign key script:
/* This Procedure creates sql statements to disable all foreign keys */
set nocount on
declare #disable_fk cursor for
select distinct table_name
from information_schema.table_constraints
where constraint_type = 'foreign key'
declare @tbname as varchar(70)
declare @statement as varchar(1000)
declare @status as int
select @status= 0, @statement = ' '
open #disable_fk
fetch #disable_fk into @tbname
while @status <> -1
begin
/*######################!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#####################################################*/
/* CHANGE THE EXECUTE TO SELECT FOR DEBUGGING SO THAT THE QUERIES SHOW IN THE OUTPUT INSTEAD OF RUNNING */
/*######################!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!##### */
select @statement = 'alter table ' + rtrim(@tbname) + ' nocheck constraint all;'
execute(@statement)
-- select (@statement)
fetch #disable_fk into @tbname
if @@fetch_status = -1
begin
select @status = -1
end
end
close #disable_fk
deallocate #disable_fk
Enable foreign key script:
/* This Procedure creates sql statements to enable all foreign keys */
set nocount on
declare #enable_fk cursor for
select distinct table_name
from information_schema.table_constraints
where constraint_type = 'foreign key'
declare @tbname as varchar(70)
declare @statement as varchar(1000)
declare @status as int
select @status= 0, @statement = ' '
open #enable_fk
fetch #enable_fk into @tbname
while @status <> -1
begin
/*######################!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#####################################################*/
/* CHANGE THE EXECUTE TO SELECT FOR DEBUGGING SO THAT THE QUERIES SHOW IN THE OUTPUT INSTEAD OF RUNNING */
/*######################!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!##### */
/* select @statement */
select @statement = 'alter table ' + rtrim(@tbname) + ' check constraint all;'
execute(@statement)
-- select (@statement)
fetch #enable_fk into @tbname
if @@fetch_status = -1
begin
select @status = -1
end
end
close #enable_fk
deallocate #enable_fk
dbcc checkconstraints
March 22, 2004 at 8:41 am
Thanks.. I'm now using something similar to turn constraints off and then on again.. but I still have the issue that sometimes one record with bad data will stop the loading of the whole table. I would like to load the good records and then go back and clean up the ones that didn't succeed.. I'm assuming that increasing the maximum error count would allow me to do that ( the wizard cranks out tasks with the error count set to 0).. It's just that I haven't found a way to do that for all the transformations without visiting them each individually in the designer.
I'm now looking to see if I can use the object model to write a short program that would do that.. but hopefully someone has already done something like that ... or has found a more elegant way to do it..
March 22, 2004 at 11:51 am
I don't know if this is more elegant but it's quicker than double-clicking on each transformation task and going to the options tab.
Start Disconnected Edit in DTS designer, expand Tasks, click on each DTSDataPumpTask, and change the MaximumErrorCount property to whatever value you want. When you've changed all of them, close Disconnected Edit and you're done.
Greg
Greg
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply