0

I am running an SSIS package that contains many (7) reads from a single flat file uploaded from an external source. There is consistently a deadlock in every environment(Test, Pre-Production, and Production) on one of the data flows that uses a Slowly Changing Dimension to update an existing SQL table with both new and changed rows. I have three groups coming off the SCDSSIS Data Flow:

-Inferred Member Updates Output goes directly to an OLE DB Update command.

-Historical Attribute goes to a derived column boxed that sets a delete date and then goes to an update OLE DB command, then goes to a union box where it unions with the last group New Output.

-New Output goes into a union box along with the Historical output then to a derived column box that adds an update/create date, then inserts the values into the same SQL table as the Inferred Member Output DB Command.

The only error I am getting in my log looks like this:

"Transaction (Process ID 170) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

I could put the (NOLOCK) statement into the OLE db commands, but I have read that this isn't the way to go.

I am using SQL Server 2012 Data Tools to investigate and edit the Package, but I am unsure where to go from here to find the issue.

I want to get out there that i am a novice in terms of SSIS programming... with that out of the way... Any help would be greatly appreciated, even if it is just pointing me to a place I haven't looked for help.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
tCoe
  • 401
  • 1
  • 5
  • 24
  • Please post your SSIS package, default isolation level. Do you use transaction in SSIS? – Lukasz Szozda Sep 26 '17 at 14:44
  • Anyway Slow Changing Dimension is incredibly slow for bigger datasets. Consider using set based approach. – Lukasz Szozda Sep 26 '17 at 14:48
  • @lad2025 I am not allowed to post the package as it contains the connection strings and passwords... being a novice, i will try to recreate the data flow object that is having the issue. – tCoe Sep 26 '17 at 15:16
  • I meant printscreen of control/data flow. – Lukasz Szozda Sep 26 '17 at 15:17
  • @lad2025 I added the snip screen of the data flow, for the second comment, I did not create this and therefore cannot change it without an exact plan of why and how. Right now, i am unsure why it is deadlocking. Speed isn't the issue, because it is really only effecting one table and about 150-200 records a day. – tCoe Sep 26 '17 at 15:23
  • @tCoe the key thing (if you want to recreate the issue) is data model and data itself. Please put your source model and target table structure (including indexes) and all 7 rows from source. What type of SCD is it? Have you tried to use SCD Merge Wizard rather than SSIS? – Kamil Nowinski Jan 09 '18 at 23:40

1 Answers1

0

Adding index on the WHERE condition column may resolve your issue. After adding index on the column, transactions will executes in faster way which reduce the chances of deadlock.

Ajit Medhekar
  • 1,018
  • 1
  • 10
  • 39