The dreaded table lock can occur and cause your SSIS packages to fail. A popular request I receive asks “How can I get rid of these table locks?” This blog will show you how to build a package that will kill any SPID’s that are running on your system that could be locking a table.

Note: Be careful using this technique, you could kill a critical process.

In this package you will have five variables.

objSpids = Holds the data from sp_Who2

strDatabase = Name of the database to look in Spids

strSpid = Current Spid in the for each loop

strSQLKill = Expression: “Kill ” + @[User::strSpid]

strSQLSPWho = Expression

“CREATE TABLE #sp_who2

(SPID INT,

Status VARCHAR(1000) NULL,

Login SYSNAME NULL,

HostName SYSNAME NULL,

BlkBy SYSNAME NULL,

DBName SYSNAME NULL,

Command VARCHAR(1000) NULL,

CPUTime INT NULL,

DiskIO INT NULL,

LastBatch VARCHAR(1000) NULL,

ProgramName VARCHAR(1000) NULL,

SPID2 INT,

REQUESTID int

)

INSERT INTO #sp_who2

EXEC sp_who2

SELECT cast(spid as varchar(10)) as spid

FROM #sp_who2

WHERE DBName = ‘”+ @[User::strDataBase] +”‘

and HostName is not null

and Status <> ‘BACKGROUND’

group by spid

DROP TABLE #sp_who2″

Notice the strSQLSPWho variable holds the query to create the table and put all of the SP_Who data into it. The database name comes from the strDatabase variable.

The first thing you will need to do is get the information from SP_who. This is done with an Execute SQL task. Set the SQL source type to variable and choose the strSQLSPWho variable as the source variable. Set the Results set to Full Results Set. In the Results set pane add a result set and set the name to 0 and the variable to objSpids.

Now you will need to loop through each row in the object variable with the Spids. The For Each Loop will do this. The Enumerator needs to be set to For Each ADO. Select the objSpids variable. Under variable mappings set the variable to strSpid and the index to 0.

Now drop an Execute SQL task in the For Each Loop. Set the SQL source type to variable and choose the strSQLKill variable as the source variable. Leave the Results set to None.

That is it for building the package. The next step is to test the package. Place a breakpoint on the For Each loop. Set this breakpoint to “Break at the beginning of every iteration of the loop”.

Start debugging the package and check the watch window or the locals window to for the value of the variables. To get these windows click on debug >Windows> Locals or Watch1.

Here is the watch window:

If the package is picking up Spids you don’t want you will need to adjust the where clause in strSPWho variable.