2

I am trying to deploy my 2012 SSIS project to the Integration Services catalog on the SSIS server (also 2012). I am getting the error below. I checked that there is plenty of space in SSISDB and msdb databases as well as on the disk. Any ideas what might be causing this?

enter image description here

Improve this question
5
  • What does the ssisdb.catalog.operation_messages have to say about the deployment operation? What if you try a different deployment mechanism, do you get any more detailed error messages? This is a tsql deployment
    – billinkc
    Commented Aug 21, 2014 at 21:49
  • Thank you for the script! I tried it and got a similar error. It did have a name of the offending stored procedure: Msg 3930, Level 16, State 1, Procedure prepare_deploy, Line 298 The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. The catalog.operation_messages is completely empty.
    – SQL_Guy
    Commented Aug 23, 2014 at 1:22
  • I even tried to step through the involved MS procedures, which get called by "deploy_project", however the debugging functionality in SSMS is still very flaky and it crashed on me every time I tried :( It seems that "prepare_deploy" stored procedure performs a lot of security checks and this is where things break.
    – SQL_Guy
    Commented Aug 23, 2014 at 1:22
  • I tracked down the issue to a series of statements in "internal.prepare_deploy" stored procedure, which deal with various aspects of security keys. The procedure builds a dynamic sql string similar to this: CREATE CERTIFICATE MS_Cert_Proj_20 WITH SUBJECT = 'ISServerCertificate' and then tries to run it via sp_executesql. That's when it crashes. I copied the string and ran it manually in a different window without issues. I tried to "cheat" by commenting out these lines and executing them myself, but this gets very complicated due to various internal calls as well as procedure scope.
    – SQL_Guy
    Commented Aug 23, 2014 at 2:05
  • Here are examples of these lines: SET @sqlString = 'CREATE CERTIFICATE ' + @certificate_name + ' WITH SUBJECT = ''ISServerCertificate''' IF NOT EXISTS (SELECT [name] FROM [sys].[certificates] WHERE [name] = @certificate_name) EXECUTE sp_executesql @sqlString SET @sqlString = 'CREATE SYMMETRIC KEY ' + @key_name +' WITH ALGORITHM = ' + @encryption_algorithm + ' ENCRYPTION BY CERTIFICATE ' + @certificate_name
    – SQL_Guy
    Commented Aug 23, 2014 at 2:10

1 Answer 1

Reset to default
3

I experienced this problem, and was able to resolve it by disabling a DDL trigger on the SSISDB database for DDL_DATABASE_LEVEL_EVENTS. The trigger was trying to write info into another Database.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.