Limitations of DLM/SQL Data Compare

leehbileehbi Posts: 6 New member
in SQL Data Compare
Hi,
We use SQL Data Compare as part of our CD process with the DLM scripts to deploy changes from dev to test to prod.  
The workload is a data warehouse.   We have data for things like ETL audit/last record loaded etc..  This data must not be changed in test/production.  Data changes we need to deploy is meta data to drive the ETL processes.  We're using BIML to build our packages and they read this meta data to build packages hence this data needs to be deployed to test/prod.

We haven't found a clean way to do this, other than take a copy of previous meta data and re-load after DLM scripts.

I don't like this as there's an extra step that increases complexity/failure rates.
Has anyone else experienced this and taken a different path?

Tagged:
· Share on Twitter

Answers

  • Alex BAlex B Posts: 1,158 Diamond 4
    Hi @leehbi,

    I'm just trying to understand the process a bit better - is the metadata actual data in tables that is used elsewhere?  Or is is extended properties or something else?

    What is your existing DLM process that you are adding the SQL Data Compare step onto?  Have you looked into using Static Data in SQL Source Control that you would then update in dev and commit and would then be rolled out to the target environments with the DLM deployment?

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
  • leehbileehbi Posts: 6 New member
    Metadata is data in tables that describe schema for use in packages. Not extended properties - just plain old data.   It's actually used by BIML to build packages i.e. the meta data describes sources/destinations.  We're using Jenkins to control the build  We check in the scripts from SQL Compare/Data Compare and jenkins then uses these scripts when building/deploying.  Not a problem deploying the data it's just that some of the data shouldn't be deployed.  I think the issue is that the powershell scripts aren't as powerful as SQL Data Compare. 

    Is there an easy way for me to exclude specific columns from a table from being deployed?

    Lee
    · Share on Twitter
  • Alex BAlex B Posts: 1,158 Diamond 4
    Hi @leehbi,

    It sounds like you are using DLM Automation (now SQL Change Automation) Powershell cmdlets which are more geared towards schema deployments and include a small static data capability that doesn't allow a lot of configuration.

    You should be able to use the SQL Data Compare command line included with DLM Automation (and SQL Change Automation) to run a specific data compare after the schema deployment.  It's located in the C:\Program Files (x86)\Red Gate\SQL Change Automation PowerShell\SDC folder.  In the SQL Data Compare command line there is a switch /ExcludeColumns to exclude specific columns from the comparison.

    I hope that helps!

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
Sign In or Register to comment.