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.