locked
Error deploying SSIS 2012 to Catalog DB RRS feed

  • Question

  • Hi,

    Am having an intermittent issue that happens when deploying to an SSIS Catalog (in the "Deploying project" step). basically sometimes when deploying an SSIS project to a Catalog that I administer, I receive an error telling me to lookup a particular operation error message in the [catalog].[operation_messages] VIEW in the SSIS Catalog DB (and gives me an operation ID), the error in the view above is:

    The project or operation records do not exist or you have not been granted the appropriate permissions to view them.

    Now I am sure there is no rights or permission issues, and if I try deploying the project again (without changing any deployment parameters) there is no issue (albiet it might take a couple of tries). There is no SSIS packages running during the time I am deploying and all server resources seems to be fine.

    Am not entirely sure what's going on here.

    Wednesday, July 25, 2012 3:44 PM

All replies

  • It seems you are getting operation ID from [catalog].[operation_messages] VIEW in the SSIS Catalog DB  after giving some where clause conditions. There may be a reason that no opearation ID present at time of error. As view is getting data from tables & may be data is table is updating.

    Try :-

    1) Run SQL server profiler & try to deploy package, if erro comes check the details captured in profiler

    2) Check SQL server error log for error time. 


    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    Wednesday, July 25, 2012 6:05 PM
  • How big is your catalog?  Right-click on the SSISDB node under Integration Services Catalogs and report back with the operational and project size values.

    If you do the profiler capture, make sure that you capture Deadlock Graphs as the RTM code is prone to deadlocks.


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

    Wednesday, July 25, 2012 6:47 PM
  • Hi Phil,Rohit

    The total Catalog size is: 78714.00 MB

    The total space available is: 3589.95 MB

    This splits up to:

    PRIMARY: 14202.00 MB (3589.75 MB (25%) available)

    LOG: 64512.00 MB (64303.39 MB (99%))

    The reason the log file reached this size is because we noticed we had stray SSIS packages executing for days but not actually doing anything, which was holding up log file truncation, also we noticed that sometimes the log file cannot grow (timesout while trying to grow), which causes all packages to move to a "Cancelled" status (you need to check the EventVewier to realise the log expansion operation was timing out).

    I feel like error propagation in this new SSIS environment isn't as upto scratch as it can be. a SSIS package shouldn't report a status of "Cancelled" on any environment related error (basically errors with Catalog DB, resources, server, etc.), this is still an error that should be reported as "Failed" while also capturing the propagated error message (instead of trying to identify the error in one of the 10s of logs on the server.

    I'll try a profiler capture to and see if there are any deadlocks, I'll also check the SQL log file and report back.

    Thanks


    • Edited by ToO_sIK Thursday, July 26, 2012 11:01 AM clarification
    Thursday, July 26, 2012 10:48 AM
  • Hi,

    I have ran the profiler while the deployment issue was happening, and there was no deadlocking at all, in-fact all I can see while the deployment process is running (and leading up to a deployment failure) is the following:

    • Initially there was a bunch of SET calls to the SPID the deployment process is running under, which are:
    -- network protocol: LPC
    set quoted_identifier on
    set arithabort off
    set numeric_roundabort off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set cursor_close_on_commit off
    set implicit_transactions off
    set language us_english
    set dateformat mdy
    set datefirst 7
    set transaction isolation level read committed
    • Then a dynamic SQL query with parameters are called to select the operation_id for the deployment process under status 2 (which is the "Running" status) in the [catalog].[operations] table, this operation text looks like this:
    exec sp_executesql N'SELECT [operation_id] FROM [catalog].[operations] WHERE [operation_id] = @operation_id AND [status] = @status',N'@operation_id bigint,@status int',@operation_id=28120,@status=2
    • When this operation did not find the relevant operation_id against status 2, (proc above) it appended a failure message against that operation, using the proc below:
    exec [internal].[append_operation_message] @operation_id=28120,@message_type=120,@message_time='2012-07-31 10:59:33.3041311 +01:00',@message=N'The project or operation records do not exist or you have not been granted the appropriate permissions to view them.',@message_source=20
    • A change of status on the operation occurred, the operation status changed to 4 which is "Failure"
    exec [internal].[update_operation_status] @process_id=15984,@status=4,@end_time='2012-07-31 10:59:34.0189291 +01:00',@operation_id=28120
    • The "SSIS ISServerExec Crash Handler" application name then took over and called the function: [internal].[update_project_deployment_status] with status 4 (which is Failure):
    exec [internal].[update_project_deployment_status] @status=4,@end_time='2012-07-31 10:59:34.0853311 +01:00',@operation_id=28120,@project_version_lsn=183,@description=N''

    If the operation is success, step 1 will find a Status 2 (Running) for the deployment operation ID, and deployment procedures will be run to update each package and the parameters for that package.

    Essentially it looks like there is no deadlocks, instead, there is something that is not placing a deployment operation with status 2 (Running) in the [catalog].[operations] table before the first procedure executed, causing this procedure not to find any result, and then escalating a deployment failure... Why is this happening I have no idea!

    I have the detailed profiler trace for both, a successful run and a failed run, which I can post here if that will help.

    Cheers

    Tuesday, July 31, 2012 10:34 AM
  • I too am having the same issue.  Did you ever find a solution?

    Wednesday, January 21, 2015 2:37 PM
  • I'm having this issue too. Here's to hoping this gets a fix. Currently, the workaround seems to be to try to deploy repeatedly until it success but my success rate with this approach is very low. :(
    Monday, February 22, 2016 10:52 PM