SQlPackage For DacPac Deploy using .net C# System.Diagnostics.process

Question

Am trying to call SQlPackage from a windowsfroms project using System.Diagnostics.process

Below is the code snippet i am using to call Sqlpacakage with dacpac path and connection string as argument

    ProcessStartInfo procStartInfo = new ProcessStartInfo();
    procStartInfo.FileName = @"C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\sqlpackage.exe";
    procStartInfo.Arguments = @"/Action:Publish /SourceFile:""C:\Myfiles\Samples\TestDatabse\Snapshots\TestDatabse_20191201_12 - 59 - 10.dacpac"" /TargetConnectionString:""Data Source = Server; Integrated Security = False; Initial Catalog = AdventureWorksDW2014; User ID = User; Password = Password; "" /p:DropObjectsNotInSource=False /p:BlockOnPossibleDataLoss=True /dsp:""C:\sqlOutput\Deploy.sql""";;
    procStartInfo.RedirectStandardOutput = true;
    procStartInfo.UseShellExecute = false;
    procStartInfo.CreateNoWindow = true;

    using (Process process = new Process())
    {
        process.StartInfo = procStartInfo;
        process.Start();

        process.WaitForExit();

        StreamReader reader = process.StandardOutput;
        string output = reader.ReadToEnd();
      }

when i execute the snippet the process is not waiting till the Deployment of database. if i read the output am getting the First line of the output like Publishing to database 'AdventureWorksDW2014' on server ''.the process is getting exited after this and the database is not getting updated to given dACPAC.

is there any suggested way where i can wait till the deployment of given DACPAC is done and DACPAC gets published in server.

score 2 · Accepted Answer · answered Dec 02 '19 at 03:23

From what it looks like, the exe is failing to process the install/publish. I might be wrong but it would be helpful to output the errors if there is any by redirecting the error output as well. Once you have your error, you should be able to troubleshoot from there.

    procStartInfo.RedirectStandardOutput = true;
    procStartInfo.UseShellExecute = false;
    procStartInfo.CreateNoWindow = true;
    procStartInfo.RedirectStandardError = true; // <--- Add this line

    using (Process process = new Process())
    {
        process.StartInfo = procStartInfo;
        process.Start();
        process.WaitForExit();


        // ---> I would add this here...
        var result = process.StandardOutput.ReadToEnd();
        string err = process.StandardError.ReadToEnd(); // <-- Capture errors

        if (!string.IsNullOrEmpty(err))
        {
           Console.WriteLine(err); // <---- Print any errors for troubleshooting
        }
        Console.WriteLine(result);
        // ----------------        
      }

thanks @MarcusLai the problem is with the permission associated with file. process.StandardError helped to troubleshoot the issue — Aravind Goud, Dec 02 '19 at 11:56
be aware of the deadlock possibility: "A deadlock condition can result if the parent process calls p.WaitForExit before p.StandardOutput.ReadToEnd and the child process writes enough text to fill the redirected stream" - https://learn.microsoft.com/en-us/dotnet/api/system.diagnostics.process.standardoutput?view=netframework-4.8. — aderesh, Apr 18 '20 at 11:19
also "A deadlock condition results if the parent process calls p.StandardOutput.ReadToEnd followed by p.StandardError.ReadToEnd and the child process writes enough text to fill its error stream" - https://learn.microsoft.com/en-us/dotnet/api/system.diagnostics.process.standardoutput?view=netframework-4.8 — aderesh, Apr 18 '20 at 11:20

SQlPackage For DacPac Deploy using .net C# System.Diagnostics.process

1 Answers1