I was toying around with the new SQL Server 2016 CTP release and I wanted to run a few SSIS packages to test out the new logging levels. However, everytime I ran a package in the SSIS catalog, it ended with the dreaded “unexpected termination” message. In such a case, nothing is logged to the catalog, so you can start digging for the cause that makes your SSIS packages go dead before they start running.
There are a lot of reasons why you can get the unexpected termination message (and for some of those there is a fix for SQL Server 2012 and 2014). But looking at the event log I could quickly track down the root cause:
Cannot find the symmetric key ‘xxx’, because it does not exist or you do not have permission.
Since I’m the admin on the machine, I certainly have permission so it means the key doesn’t exist. Then I realized I had upgraded SQL Server a few times: from SQL Server 2016 CTP 2.0 to 2.1 to 2.2 to 2.3. Apparently somewhere in the upgrade process, the symmetric key of the SSIS catalog was lost. Since this is a simple virtual machine for playing around with SQL Server 2016, the solution was simple: delete the SSIS catalog and create it again. Bingo, all packages ran again without issues. However, if you’re on a production machine, you might want to take precautions for this issue when you upgrade your SQL Server instance.
I recently read the book Agile Data Warehouse Design - Collaborative Dimensional Modeling, from Whiteboard…
You can find the slides for the session Building the €100 data warehouse with the…
I was asked to do a review of the book Microsoft Power BI Performance Best…
This is a quick blog post, mainly so I have the code available if I…
Praise whatever deity you believe in, because it's finally here, a tenant switcher for Microsoft…
This book was making its rounds on social media, and the concept seems interesting enough…
View Comments
bonjour comment je peux supprimer le catalogue SSIS et créez-le à nouveau.
https://andyleonard.blog/2016/09/managing-the-ssis-catalog/