August 4, 2010 at 9:48 am
So I created a package that basically just runs a bunch of Execute SQL Task steps in sequence that all just use EXEC [storedproc] inside a Sequence Container. There is one step before and one step after the Sequence Container that create the work table and then copy it to the remote server once all of the Sequence Container steps have finished. The entire package is set to TransactionOption 'Required' so that if any portion fails it will rollback the entire process.
The first three items in the Sequence Container run stored procs that 'insert into' a table on a linked server and join to the table that was created in the first step outside the container. All of the remaining steps are simple delete statments that run on the local server only.
So my issue is that the first three steps that run against the linked server are SLOW. If I run the process against a copy on the local machine it runs in like 30 minutes, if I run it against the linked server it takes several hours. I was thinking about trying to use a Data Flow task instead and not run through stored procs against a linked server but I get an issue where the Progress shows Validating for hours and never actually starts the task.
Any ideas or a way to speed it up or why it would be hanging on the validation step when using the Data Flow tasks? I think it has to do with the single transaction method but not sure.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
August 4, 2010 at 11:49 am
I figured out that it was hanging at the validation phase because I had the entire package set as 'Required' and Serializable. I changed it to 'Supported' and only set the 'Required' value on the Sequence Container (which for me is the only part that I really care about) and it works flawlessly with the Data Flow tasks (and much faster than using the SP's against a Linked Server).
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply