Execute SQL Task locking up table

Question

Post reply

Execute SQL Task locking up table

GBeezy

SSCarpal Tunnel

Points: 4723
More actions
December 20, 2010 at 11:47 am

#388473

I have a SSIS pacakge, that does the following:
An Execute SQL Task to TRUNCATE a Staging table
An Execute SQL Task to INSERT new rows into the Staging table
And a Dataflow task that utilizes the Staging Table by joining to other tables to get the needed info for the package.
My two SQL Tasks with complete with no problem, but the Data Flow never does anything; turns yellow and that is it. When I look at the progress of my DFT, it is not even completing OLE DB Source tasks (which contain the queries to the staging table). I am pretty sure I know what is going on, I am just not certain on how to correct it.
When the SQL tasks are running, they are creating a lock on my staging table and not letting the DFT task read that data. I have profiled the SSIS package and also have seen the lock from within Activity Monitor. I have wrapped my SQL in BEGIN/COMMIT TRANSACTIONS, played with ISOLATION LEVEL, and TRANSACTION OPTION. I can not get anything to work for me at all.
If anyone can push me in the right direction, I would greatly appreciate it.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Can you provide the DDL, DML and execution plan for the SQL in the DFT?

That will help immensely in troubleshooting your problem.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

GBeezy SSCarpal Tunnel Points: 4723 More actions · Answer 2

Hey Jason,

Thanks for the reply, I am a little know to this site and SQL server as a whole. Are you asking me for my exact SQL queries? How would I copy and the paste the execution plan into this forum?

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Here is an article from Gail Shaw on how to post that information.

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

yes, please provide the queries as well as the "staging" table structure. It would also be nice to see the structure of the tables that provide the source data.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

GBeezy SSCarpal Tunnel Points: 4723 More actions · Answer 4

Jason, I will get this information for you, but in the meantime...what does it mean when I look at the Activity Monitor at the time of the lock for my SSIS package and in the Blocked By column it has a -2?

Thanks