SSIS schema switching deadlocks

I have an SSIS package which takes data that has changed in the last 1/2 hour and transfers it from a DB2 database into a SQL server. This data is loaded into an empty import table (import.tablename) then inserted into a staging table (newlive.tablename). The staging table is then schema switched with the live (dbo) table within a transaction. FYI, the dbo tables are the backend to a visualization tool (Looker)

My problem is that the schema switching is now creating deadlocks. Everytime I run the package, it affects different tables. I've been using this process with larger tables before (also backend to Looker) and have not had this problem before.

I read in another post that the user was having a similar problem because of indexes but all the data has been written to the destination tables.

Any ideas or suggestion of where to look would be much appreciated

The schema switching code is within a Execute SQL Task in the SSIS Package with:

BEGIN TRAN ALTER SCHEMA LAST_LIVE TRANSFER DBO.TABLENAME ALTER SCHEMA DBO   TRANSFER NEW_LIVE.TABLENAME GRANT SELECT ON DBO.TABLENAME TO LOOKER_LOOKUP   COMMIT TRAN  

https://stackoverflow.com/questions/67411125/ssis-schema-switching-deadlocks May 06, 2021 at 10:38AM