filter

dskogdskog Posts: 5
in DLM Automation
DLM automation does not use the Filter.scpf file from sql compare.

Background. We don't include users or roles in our source control. (If there was a way to always exclude these objects from the deploy, we would use that, but it doesn't look like there is.)

This works as intended, using the redgate plugin with MS SQL Server Management Studio. The users and roles are not added to TFS, and there is a "Filter.scpf" file in the root of the database folder. Also, the Filter.scpf is included in the Nuget package (from the Build step).

However, when the Sync step runs, the filter is not applied (all users and roles are dropped). If we set the "Filter path" to this same filter file, it works as intended (users and roles are not dropped). Note that we did try the "-IncludeDependencies" in the SQL Compare options, but that didn't make a difference (and we'd rather not do it that way anyway).

Why is the Filter.scpf file in the Nuget package ignored?

Seeing this on the info for the "Filter path",
"The path to a .scpf filter file. Overrides any existing Filter.scpf file present in the package/source control repository, and is used when creating the update SQL. Filter files can be generated by SQL Source Control and SQL Compare."
leads us to believe that the "existing Filter.scpf" would be used if this "Filter path" wasn't set in the build definition, but it doesn't seem to work that way.

We have a large amount of build definitions, and if we forget to explicitly set the "Filter path" in any one of those, the users get dropped from the database (and everyone has a bad day).

Please advise on what needs to be done to get this to work without having to set the "Filter path" in every build definition.
Tagged:
· Share on Twitter

Comments

  • AlexYatesAlexYates Posts: 264 Rose Gold 2
    You could try creating a single PowerShell script (with parameters) that you could add to source control once to handle your deployments. Then, instead of using the plugin you can simply call that same PowerShell script for each build.

    This way your configuration for your deployments is in just one place and it is under source control.
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
    · Share on Twitter
  • dskogdskog Posts: 5
    I think creating this PowerShell script, and passing it the correct parameters (many applications, and multiple environments) would be more complicated and be harder to maintain than simply putting the filter file location in the "Filter path" as we are now.

    I really just want to know why it's not using the Filter.scpf file in the Nuget package.

    If we set the "Filter path" we see this when we build:
    "filter file
    'C:\vNextBuildAgent\_work\36\s\[application name]\Dev\Database\Filter.scpf'
    specified by the FilterPath parameter is overriding the filter in scripts folder
    'C:\vNextBuildAgent\_work\36\s\[application name]\Dev\Database'
    in the source."

    As you can see, the file it says it's overriding is the same as the one that it's using to override (from the "Filter path"). This works correctly. However if we omit the "Filter path" it doesn't work correctly (drops the users and roles)

    · Share on Twitter
  • AlexYatesAlexYates Posts: 264 Rose Gold 2
    That is fair - and it's a question for someone in the dev team. :-)
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
    · Share on Twitter
  • dskogdskog Posts: 5
    Thanks for the feedback. Hopefully someone from the dev team will be able to answer.
    · Share on Twitter
  • Rob CRob C Posts: 419 Gold 2
    Hi! I'm sorry to hear that this is happening. It should be respecting the filter that is contained in the NuGet package so I'm not sure what's happening here.

    I'd really appreciate it if you could answer some questions so that I can get an idea of your current setup.
    • What current version of DLM Automation itself are you using?
    • Which version of TFS are you using?
    • Which TFS plugin are you using? The new VSTS-style plugin or the older TFS Build plugin with the XAML builds?
    • If you are using VSTS, are you using a hosted build server on an on-premise build server?

    Thanks!
    Software Engineer
    Redgate Software
    · Share on Twitter
  • dskogdskog Posts: 5
    Thanks for helping...

    • What current version of DLM Automation itself are you using?

    2.0.12.371

    • Which version of TFS are you using?

    14.102.25423.0 (we're planning on upgrading to TFS 2017 update 1 – 15.112.26307.0 soon)

    • Which TFS plugin are you using? The new VSTS-style plugin or the older TFS Build plugin with the XAML builds?

    New VSTS

    • If you are using VSTS, are you using a hosted build server on an on-premise build server?

    On premise
    · Share on Twitter
  • Rob CRob C Posts: 419 Gold 2
    Thanks for that information. There's nothing obviously wrong. Could you try updating to DLM Automation 2.0.14 to see if that helps? Here's a download link.

    Was the filter created using SQL Source Control? Also, if you look at the generated NuGet package, do you find the Filter.spcf file in the db\state folder?

    Software Engineer
    Redgate Software
    · Share on Twitter
  • dskogdskog Posts: 5
    I'll see if we can get the DLM Automation upgraded.

    Yes, the filter was created using SQL source control.

    Yes, the Filter.spcf is in the db\state folder in the NuGet package, and it looks as it should for the User and Role entries

    <User version="1">
    <Include>True</Include>
    <Expression />
    </User>

    <Role version="1">
    <Include>True</Include>
    <Expression />
    </Role>
    · Share on Twitter
  • Rob CRob C Posts: 419 Gold 2
    That's strange. It looks like you've got things set up correctly. I've asked our support team to create a support ticket for you as I think this is going to need a more detailed investigation. You should hear something regarding this shortly.
    Software Engineer
    Redgate Software
    · Share on Twitter
  • josh_stcujosh_stcu Posts: 14 Bronze 2
    edited February 24, 2020 10:55PM
    We're experiencing something similar I think (although with SQL Change Automation now)? The default filter is being picked up just fine but we have a deployment filter in the same location (db/state/ in the nuget package) we'd like to specify when deploying our package. When we pass in the file name to the Octopus Deploy "Deploy from Package" job step the step will fail with an error that the specified filter cannot be found in the working directory.
    · Share on Twitter
  • josh_stcujosh_stcu Posts: 14 Bronze 2
    Actually @AlexYates was came up with the answer I was looking for on SQL Community slack: the path to the alternative filter is absolute (or defaults the working directory). As the filter was in the nuget package we had to use #{Octopus.Action[Package Self Deployment].Output.Package.InstallationDirectoryPath}\db\state\<filter file> which created an absolute path from C:\ to where the tentacle unpacked nuget.
    · Share on Twitter
  • CamFCamF Posts: 1 New member
    Thanks @josh_stcu that helped me resolve a similar issue. Strangely, I get this deployment warning when I specify the FilterPath in Octopus Deploy:

    WARNING: Cmdlet parameter FilterPath is not supported for SQL Change Automation projects. This option will be ignored.

    Yet when I use the absolute path using the Octopus Deploy parameter you mentioned above, it does seem to respect it and deploy successfully. 
    · Share on Twitter
Sign In or Register to comment.