Concluding this weeks work on PoshSSDTBuildDeploy is adding one of the features I spoke about yesterday, which was joining the OperationSummary and Alerts objects to produce a new table that will make it easier to figure out which Operation is creating which Issue.

Let’s revisit the xml file that I running my tests with -

<?xml version="1.0"?>
        <Alert Name="CreateClusteredIndex">
            <Issue Value="Primary Key: unnamed constraint on [dbo].[TestTwo]"/>
        <Alert Name="DataIssue">
            <Issue Value="The table [dbo].[TestThree] is being dropped, data loss could occur." Id="1"/>
        <Operation Name="Drop">
            <Item Value="Permission" Type="SqlPermissionStatement"/>
            <Item Value="Permission" Type="SqlPermissionStatement"/>
            <Item Value="[dbo].[TestThree]" Type="SqlTable">
                <Issue Id="1"/>
        <Operation Name="Create">
            <Item Value="[dbo].[TestOne]" Type="SqlTable"/>
            <Item Value="Primary Key: unnamed constraint on [dbo].[TestTwo]" Type="SqlPrimaryKeyConstraint"/>

We have two alerts, one of which only has an ID. I can only assume this is because the unnamed constraint creation is happening as part of the create tableone operation, and if I was to put it in its own file or statement in the source then I would get an issue id. But at any rate, we can see that the Operation to drop tablethree is the cause of this alert, so it makes sense to present this info more easily to the end user.

The first cut of the tables looked like this -


Which was OK, but a little basic, and that list would just grow and be difficult to understand. So I put a bit more effort in and this was the result of my efforts -


And today I have finally managed to join the Operations and Alerts tables together -


So all very pretty and usseful when running a deployment locally or when viewing on a console, but because things like artifacts are important, all this info is added to a text file in the same location as the .xml file.


…and this is what the contents look like -


Nice. I’m using the PowerShell Function Join-Order available here to join the two objects. Many thanks to the author as that saved a whole lot of effort! I’ve updated the tests to take into account that this new file is created. And of course the latest version is now on PowerShellGallery. PoshSSDTBuildDeploy

It’s good that info is being passed out from the Deployment Report and that issues and operations are being linked, however I still think that there is scope for creating rules based on expereinces with SSDT. For example, there is a scenario where constraints are always being rebuilt. There’s no reason why we can’t scan all the drop operations and attempt to find if these objects are being created in the same script. If this were the case, then something odd might be happening, and so we can raise an issue of our own if one wasn’t created. Something like this is going to be a little bit of work, but there’s plenty of rules that can be added over time.