This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(86.4, 56.00000000000001%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP%3C/text%3E%3C/svg%3E)
A blocking/ locking situation within SSIS for columnstore indexes only. We are talking about 15K rows insertion so very small. Even 1000 rows can cause the issue
1.
The below inserts into TableTR and never completes. This is a simple SSIS Dataflow task.
Select * from Table1 a
inner join dbo.TableTR tr with (nolock) on a.[Transaction Reference Number] = tr.[Transaction Reference Number]
and a.[Submitting Entity ID] = tr.[Submitting Entity ID]
and a.[Executing Entity ID] = tr.[Executing Entity ID]
where tr.[Current_Flag] = 1
2.
Now if I do insert into TableTR_copy
Select * from Table1 a
inner join dbo.TableTR tr with (nolock) on a.[Transaction Reference Number] = tr.[Transaction Reference Number]
and a.[Submitting Entity ID] = tr.[Submitting Entity ID]
and a.[Executing Entity ID] = tr.[Executing Entity ID]
where tr.[Current_Flag] = 1
Then insert into TableTR
Select * from TableTR_copy that works fine with the columnstore.
No 2 works absolutely fine and is very quick.
No 1 only works if I drop the column store. So appears to be a block on the select caused by the insertion. I have managed to fix the issue by unclicking this flag below. This works fine if there is no columnstore index on the table. I can see the block spid on sp_who2. Very bizarre and can't see why a columnstore should cause this.
This works fine by un-ticking this or by creating an aggregation before the insertion into the columnstore table. Could be other possible workarounds. Any ideas why a columnstore index is creating this? There is another index on the table no issues.
What does "SELECT @@version" report?
As I understand your post, sp_who and sp__who2 indicates blocking. Have you investigated what the blocking process is doing?
Since you are requesting a table lock, this mean any query running against the table will block you.
Hi Erland, sorry for delay, i did not follow the topic so did not have notification of the answer. The issue is that if a columnstore index exists on the table then the blocking happens. If i drop the column store index and create a non clustered index then the SSIS process works fine. This blocking issue is specific to SSIS, if i use SQL to achieve the same result all is fine, so something weird with SSIS and columnstore. I just had to accept that the SSIS has to have the box unitcked as per above and that solves my issue. and my version
Microsoft SQL Server 2019 (RTM-CU19) (KB5023049) - 15.0.4298.1 (X64) Jan 27 2023 16:44:09 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)
Thank you.
I don't know SSIS myself, so I can't test this myself. But SSIS must submit something which is incompatible with columnstore. You could use Profiler to spy on it.
You are on the next-to-most-recent CU of SQL 2019, so that is fine.
Yeah if I could only have profiler access in my big bank organisation 😀 I may try to test this at home though see if any different.
Hi @panlondon1,
Table lock
Specify whether the table is locked during the load. The default value of this property is true.
The table lock will block other processes using the table, including the other OLEDB Destinations within the SSIS package. Please have a double check.
Regards，
Zoe Hui
If the answer is helpful, please click "Accept Answer" and upvote it.