I would try Replication for small amounts of data

Are you the author of the SSIS package. If you open it in SSDT you can add logging to a table, file or Windows Event Log.

You can log logins

If you have permissions to create a stored procedure you could use WITH RECOMPILE

Add an entry to the hosts file to override the dns name on each web and ssis server. Next start changing your environment creating database specific dns entries for your...

I think temporal (system versioned) tables are better for a number of reasons if you really want to audit DML changes, but it is a feature of 2016+.

Yes, using xp_cmdshell or CLR. You could also create a SQL Agent Job to copy or move a file using OS command (.bat, .ps1) or an SSIS package.

.

Try adding the suggested index.

Try:
DECLARE @pick table (
 Member_id  int   NOT NULL
, ApplicationID varchar(30) NOT NULL
, Product   varchar(5) NOT NULL
, EffDate   date   ...

gjoelson,
It would behoove you to read the Microsoft...

I think you are over complicating the issue. 
I would write a procedure to truncate my staging table, insert into the staging table using select distinct from an OpenRowset from...

Select *
FROM Table_A A
JOIN Table _B B
On B.Prod_ID LIKE A.AUTO_Value+'%'

Try converting your procedure(s) to an inline table valued function(s).

Here is you query written as "insert into select"
USE [SSSUTIL]
GO
INSERT INTO [dbo].[CustomerOrders]
([co_num]
,[cust_po]
,[co_line]
)
SELECT 38322, 'B123', 2;
GO

It doesn't matter...