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.
------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
bonjour comment je peux supprimer le catalogue SSIS et créez-le à nouveau.
https://andyleonard.blog/2016/09/managing-the-ssis-catalog/