log sqlpackage.exe error in file

Question

I am using SQlPackage.exe to deploy / publish the database project. i want to log the publishing activity like creating database / table or any modification in a separate log file. but it seems that there is no option in sqlpackage.exe to log this information

Secondly if somehow i stop the sqlpackage deployment in between (since i am using a bat file and from there i am calling the sqlpackage.exe command) then it does not rollback all the changes.

Note i already have enabled the option include transnational script. by enabling this the post deployment script are not in the transaction block . in other words if there is an error in post transaction script but not in the schema therefore the schema part would get deployed properly and this will threw an error in post deployment script. therefore my database is in-consistent state. my point of view if there is any error either it should rollback everything.

For the first part, you can pipe the output to a text file without too much trouble. Use either "|" or ">" to a text file to capture that. For the second, I have no good advice. That's the way SSDT behaves. Coding your scripts to check for changes you expect might be one way to work around that and wrap those in an IF EXISTS type block to run or not run. — Peter Schott
@PeterSchott Thanks peter for your help. i did try to use the > text file option but this does not output the information like creating table ... or error information which we generally see at the time of publishing from Visual Studio. — user842232
Do you need specific details or would knowing that you're changing something be enough? There's a "deploy report" option that generates an XML file of everything that will happen, but not a detailed file. There's also a "generate script" option. We use those in a test run phase to review what will change before we pull the trigger. Use SQLPackage w/ the "/a:DeployReport" option and "/op:" to give it a file to write. (e.g., /op:.\myfile.xml) — Peter Schott

David Barrows David Barrows · Accepted Answer · 2016-03-25T07:36:43

You can also call SqlPackage.exe from C# and wrap it in a ProcessStartInfo (i.e. execute a shell command from within C#. I got this code from somewhere else on stack overflow and modified it to do a Console.ReadLine() if an error occurs and we're in debug mode; imagine the command you are passing in is SqlPackage.exe; then you can change the error message to red and pause the console window:

    public void ExecuteCommandSync(object command, string message)
    {
        Console.WriteLine(message);
        Console.WriteLine("------------------------------------------------------------------- ");
        Console.WriteLine(" ");

        Console.WriteLine("Executing command: " + command);

        Console.WriteLine(" ");
        Console.WriteLine("------------------------------------------------------------------- ");
        Console.WriteLine(" ");

        // create the ProcessStartInfo using "cmd" as the program to be run,
        // and "/c " as the parameters.
        // Incidentally, /c tells cmd that we want it to execute the command that follows,
        // and then exit.
        var procStartInfo = new ProcessStartInfo("cmd", "/c " + command)
        {
            RedirectStandardOutput = true,
            UseShellExecute = false,
            CreateNoWindow = true
        };

        // The following commands are needed to redirect the standard output.
        // This means that it will be redirected to the Process.StandardOutput StreamReader.
        // Do not create the black window.
        // Now we create a process, assign its ProcessStartInfo and start it
        var proc = new Process { StartInfo = procStartInfo };
        proc.StartInfo.UseShellExecute = false;
        proc.StartInfo.RedirectStandardOutput = true;
        proc.StartInfo.RedirectStandardError = true;
        proc.Start();

        // Get the output into a string
        var result = proc.StandardOutput.ReadToEnd();

        string err = proc.StandardError.ReadToEnd();

        // write the error and pause (if DEBUG)

        if (err != string.Empty)
        {
            Console.ForegroundColor = ConsoleColor.Red;
            Console.WriteLine(err);
            Console.ResetColor();

#if DEBUG                
            Console.WriteLine("Press enter to continue...");
            Console.ReadLine();
#endif
        }


        proc.WaitForExit();

        // Display the command output.
        Console.WriteLine(result);

        Console.WriteLine(" ");
        Console.WriteLine("------------------------------------------------------------------- ");
        Console.WriteLine(" ");

        Console.WriteLine("Finished executing command: " + command);

        Console.WriteLine(" ");
        Console.WriteLine("------------------------------------------------------------------- ");
        Console.WriteLine(" ");
    }

log sqlpackage.exe error in file

1 Answers