SSIS Packages With Excel Source Failing After Windows Updates

SSIS packages with Excel Sources may fail after Windows updates (specifically KB4041681) with the following error:

SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR.  The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered

The resolution is to install the 2007 Office System Driver: Data Connectivity Components from here:

https://www.microsoft.com/en-us/download/details.aspx?id=23734

Note - SSIS packages must have the "Use 32 bit runtime" box checked if being scheduled via a SQL Agent Job

Finding out when updates / service packs were applied to SQL Server

It is possible to find out when updates were applied to SQL Server by checking in the following location:

c:\Program Files\Microsoft SQL Server\XXX\Setup Bootstrap\Log

See more here: https://stackoverflow.com/questions/2950911/how-to-determine-date-when-service-pack-was-applied-to-sql-server-instance

SSRS Subscriptions - What Goes On Under The Hood

Excellent post detailing what happens "under the hood" of SQL Server Reporting Services

https://blogs.msdn.microsoft.com/sql_pfe_blog/2016/06/20/ssrs-subscriptions-what-goes-on-under-the-hood-3/

SQL Server Dedicated Admin Connection (DAC)

The Dedicated Admin Connection (or DAC for short - not to be confused with DAC packages) is exactly that - a dedicated connection that's available to SQL Server sysadmins, for use when a SQL Server might have become unresponsive. This backdoor for the sysadmins has reserved resources at all times, and allows a means for connecting to SQL Server when other ways of connecting may not be working due to performance (or other) issues.

By default, connection to SQL Server via the DAC is only available locally from the server - i.e. you'd need to remote onto the server then use SQLCMD or SSMS to use the DAC. If the host is unresponsive due to performance issues, this could prove problematic. The solution is to enable remote admin connections - this allows connecting via the DAC remotely, and can be switched on by using the following SQL:

EXEC sp_configure 'remote admin connections', 1;

GO

RECONFIGURE

GO

Connecting to the DAC remotely via SSMS is then as simple as prefixing your server\instance name with "admin:"

Kendra Little has an excellent blog post and video explaining more, including how to tell who is using the DAC (if you're unable to use it), how to enable on clusters, and more:

https://www.brentozar.com/archive/2011/08/dedicated-admin-connection-why-want-when-need-how-tell-whos-using/