I’m assuming you have a Data Flow with 100,000 records and an OLEDB Command transform which executes that update statement. That results in 100,000 individual update statements running.
Instead use an OLEDB destination with fast load enabled to write to a stage table (some table which you have previously truncated in an Execute SQL task) to insert the 100,...
Package deployment model has nothing to do with SSISDB. Instead when Package deployment model deploys to SQL Server it ends up in the msdb database in the sysssispackages table. Follow these instructions to view deployed packages. Or you can query that table.
Looks like the quote (") character is considered a part of the input string.
There is two options:
1. correct you flat file source to define quotation characters for this field, so it will remove " values from the beginning and end of the string.
2. Use another replace to get rid of quotes.REPLACE(REPLACE(SSN,"\"",""),"-","")
First advice: you can speed up query by applying index to columns in where clause, i.e. ABC_Id.
Second advice: indexes slow down deletion and insertion of rows in a table, so if you abused indexes (and defined many on one table), deletion also may be slowed down by these.
Generally, there is no obvious answer: delete ... where can be thought as steps:
You will incur some expenses if you want to run ETLs (packages) from Azure. You can create Integration runtime via Azure Data factory and deploy package to the SSIS catalogue for Azure sql database.
Createing Azure SSIS Integration Runtime
If anyone else has a similar setup with SQL Server 2017 (14.0.1000) and SSIS (14.0) as of the creation of this question/answer SSMS 18.4 will not allow you to connect to SSIS. The only fix I have found is following Jacob's suggestion and downloading SSMS 17.9 which resolved the issue.