powershelldeploymenttimeoutdacpacsqlpackage

DACPAC Deployment through powershell script is failing with timeout error


We have created powershell script to deploy our dacpac packages. This script uses Microsoft.SqlServer.Dac.dll library. When I am deploying my dacpac using this powershell script, I am getting following Bug:

.Net SqlClient Data Provider: Msg -2, Level 11, State 0, Line 0 Execution Timeout 
 Expired.  The timeout period elapsed prior to completion of the operation or the server 
 is not responding. An error occurred while the batch was being executed.

I checked the deployment options for above dll in msdn documentation and added following:

$deployoptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
$deployoptions.CommandTimeout =60000
$deployoptions.LongRunningCommandTimeout=0

CommandTimeout deployment option has default value as 0 and i added 60000, but still no effect. whereas LongRunningCommandTimeout deployment option is not getting recognized as valid option and getting following error:

The property 'LongRunningCommandTimeout' cannot be found on this object. Verify that the 
property exists and can be set.

My other findings:

  1. In the latest version of sqlpackage.exe (i.e. 162.0.52.1) I did not get the timeout error while deploying the same dacpac package, but I got the error when I used lower versions (lower than 162.0.52.1) of SQLPackage.exe.
  2. Using powershell script to deploy the dacpac package, I am getting timeout error for all versions of Microsoft.SqlServer.Dac.dll, which was disheartening for me. Because I was expecting that the latest version (i.e. 162.0.52.1) of this dll should have the fix, just like SQLPackage.exe.

Please suggest the solution of this timeout error, if anyone has found it.


Solution

  • Microsoft.SqlServer.Dac.dll with version 16.0.52.1 has deployment option LongRunningCommandTimeout which can be used to resolve timeout error for dacpac deployemnt.

    However, when I was trying to utilize this dll (with version 16.0.52.1), I did witness weird behavior of powershell:

    Problem with powershell: Open powershell command window and Execute below code to list out all deployment options provided by Microsoft.SqlServer.Dac.dll :

    #The version of Microsoft.SqlServer.Dac.dll is 15.0.4384.2 
    Add-Type -path "C:\Program Files\Microsoft SQL Server\150\DAC\bin\Microsoft.SqlServer.Dac.dll"               
    $deployoptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
    $deployoptions
    
    Output: it does not contain LongRunningCommandTimeout    and 
    DatabaseLockTimeout deployment options
    
    
    
    
    #The version of Microsoft.SqlServer.Dac.dll is 16.0.52.1
    Add-Type -path "C:\Program Files\Microsoft SQL Server\160\DAC\bin\Microsoft.SqlServer.Dac.dll"               
    $deployoptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
    $deployoptions
    
    Output: it still does NOT contain LongRunningCommandTimeout    and 
    DatabaseLockTimeout deployment options, even if I imported 
    16.0.52.1 version of dll.
    

    So, PowerShell keep showing same deployment options of the first imported dll and if we import another version of it thereafter, we do not get refreshed set of deployment options. So, if we pretend to get updated deployment options by importing different versions of this dll, then its a mistake.

    Solution: We can refresh the powershell session using command powershell and thats how we get correct deployment option of imported Microsoft.SqlServer.Dac.dll

    Add-Type -path "C:\Program Files\Microsoft SQL Server\150\DAC\bin\Microsoft.SqlServer.Dac.dll"               
    $deployoptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
    $deployoptions
    
    Output: it does not contain LongRunningCommandTimeout    and 
    DatabaseLockTimeout deployment options
    
    
    
    Powershell
     
    Output: Powershell session is refreshed.
    
    
    
    
    Add-Type -path "C:\Program Files\Microsoft SQL Server\160\DAC\bin\Microsoft.SqlServer.Dac.dll"               
    $deployoptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
    $deployoptions
     
    Output: Now it contains LongRunningCommandTimeout    and 
    DatabaseLockTimeout deployment options
    

    So the conclusion is:

    1. We need to use version 160.0.52.1 of Microsoft.SqlServer.Dac.dll to get additional deployment option LongRunningCommandTimeout.
    2. We need to refresh powershell session and then import Microsoft.SqlServer.Dac.dll to get actual deployment options related to imported dll.