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.
You can find the slidedeck for my Techorama session "Microsoft Fabric for Dummies" on github.
On Wednesday May 15th 2024 I will give a free webinar on MSSQLTips.com about Microsoft…
I loaded some built-in sample data from Wide World Importers into a Fabric warehouse. You…
It was great being at dataMinds Saturday 2024 this past weekend. A great crowd of…
Today I was having a nice discussion with some colleagues about Fabric and pricing/licensing came…
I recently purchased and read the book Deciphering Data Architectures - Choosing Between a Modern…
View Comments
bonjour comment je peux supprimer le catalogue SSIS et créez-le à nouveau.
https://andyleonard.blog/2016/09/managing-the-ssis-catalog/