The post is very confusing.  Did you do an inplace upgrade or did you do a migration?  I am not sure what an "inplace migration" is.  I am also confused...

I think if you are stuck on SQL Server 2012, you are going to need to use a CASE statement to handle daylight savings time.

If you can upgrade to 2016,...

To add to Frederico_fonseca's response - when I hit snags like that, my first step is to take the innermost portion of the troublesome query section and break it out. ...

For something like that, you will need to use dynamic SQL.

Be careful using it though as if it is not designed carefully and with proper safeguards, it is easy to...

That is weird that running it in series causes the block as well...

even stranger that running package 1 to call 1 copy of package 2 makes things run successfully.

I would capture as much data as I can that makes sense.  A timestamp of the import is something I try to capture in the staging and historical tables because...

That is a big "it depends" question.  OLAP is used for reporting and analytics.  OLTP is used for transactional data.

If you have a bunch of data used for application purposes,...

SPID -2 is an orphaned DTC transaction:

https://www.sqlskills.com/blogs/paul/disaster-recovery-101-dealing-with-negative-spids-2-and-3/

Another good read on SPID -2:

http://www.eraofdata.com/sql-server/troubleshooting-sql-server/orphaned-msdtc-transactions-2-spids/

So something is starting a distributed transaction and SQL is uncertain on the state of that distributed transaction.

Also, how...

My opinion (others may disagree), I try not to denormalize reporting data unless some of the data is going to be used by multiple reports.  I try to reduce the...

So it isn't the client then... I am wondering if it could be the mail server.

If it was consistent it would be easier to figure out.   If it showed up...

As you have Outlook, I'm thinking you likely have Exchange as the back end mail server.  Do you have OWA (outlook web access) on the exchange server?  I have a...

My only thought with this if it is a problem on the DATABASE side is something is going goofy with some data type conversions.  Such as converting VARCHAR to NVARCHAR,...

If they know which switch specifically is having the issues (which is likely), it might be a configuration issue.  Especially if you were upgrading things.  Now, I am not a...

The official docs from Microsoft state it uses that one.  See:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addlinkedserver-transact-sql?view=sql-server-ver15

And the table towards the end states that SQL Server (default) uses "Microsoft SQL Server Native Client OLE DB Provider". ...

Hopefully fixing the network issues helps resolve the problem.  My expectation is still that it will.

Packet discards can be tricky to diagnose the problem, but it could be caused by...