Getting blocked by sp_getschemalock

Question

 

I am simply moving 27 table's data from one SQL Server database on one machine, to another SQL Server machine using SSIS.  There are no transformations taking place, this is merely taking data from the source OLE DB and moving to destination OLE DB.  Tables, indexes, etc are the same on both source and destination and have been set up ahead of time.  The package simply consists of a couple Execute SQL tasks with a Data Flow in the middle that uses stored procedures with simple SELECT * type of commands with limited WHERE clauses.

 

I admit that I'm a novice at this and am frustrated that the sp_getschemalock will show up only as a problem with a couple tables of all the 27... and nothing I seem to change or do will change this.  I've tried dropping and recreating tables, starting over from scratch on my Package design, along with a myriad of combinations of changing the settings.

 

There are about 2 million rows per table being moved.

Not sure what you need to know about my settings, but here are a few I've set:

 

In Data Flow

DelayValidation = TRUE

DefaultBufferMaxRows = 5000000

DefaultBufferSize = 20971520

EngineThread = 5

RunInOptimizedMode = FALSE

IsolationLevel = Serializable

TransactionOption = Supported

 

Destination OLE db

ValidateExternalMetadata = FALSE (I've tried TRUE and FALSE in many scenarios)

Access Mode = OpenRowset using Fastload

FastLoadKeepIdentity = TRUE

FastLoadMaxInsertCommitSize = 0

FastLoadOptions = TABLOCK

 

Package

MaxConcurrentExecutables = -1

PackageType = DTSDesigner90

IsolationLevel = Serializable

TransactionOption = Supported

 

The Destination Connection String:

Data Source=MYDESTSERVER;Initial Catalog=MYDESTDATABASE;Provider=SQLOLEDB.1;Integrated Security=SSPI;Auto Translate=False;

DelayValidation = TRUE

RetainSameConnection = False

 

Any Help Is Appreciated!!!!!

Thanks!!!

Answer 1

 

The stored procedures used to generate the data at the source were using local tables and outer joins to the destination database.  Basically, the idea is only bring the data missing at the Destination over using the SSIS package.  Which, I believe, these outer joins were causing some of the sp_getschemalock issues.

 

So, I put NOLOCK's on the outer joined table and it cleared most of the problem up.  However, a couple stubborn tables are still getting blocked.  At first, it looked like the outer join worked 100%, but when I ran the full package, only a couple tables of 27 were still getting the schemalock problem.  And, yes, I double-checked them and the NOLOCK was there in the query.

 

Any other ideas???  Anyone??

Answer 2

I Think your problem is that the KeepIdentity option has to alter the schema to do a set identity insert on which will hold a schema lock.  At the same time you are trying to access that table from the source SP. 

 

Usually this type of load of only new data is accomplished in a couple of ways:

• If you can easily identify new rows in your source (for example if your tables have an insert data), you would store the date of you last run in a table, select only rows that are newer using that date, update the date when done
• If you can't identify new rows easily then you have a couple more options
  • Select all data from the source table, do a lookup task on your destination, load only rows that come out the error pipeline from the lookup (Those not present)  This may be very slow for very large tables though
  • Run your current package, but, change the destination to staging tables (look just like you r real destination tables, but, no constraints, identity properties, etc.  The, another data flow takes the new data from the staging table into your destination table