How to generate delta script using SQL Change Automation

Nirmal_Raisoni

I am using Jenkins to generate the package but while generating package its generate complete db objects. I just want added object in the package

Any help!

Diogo

Do you mean just get new objects in your script and ignore any that should be removed or modified?

Nirmal_Raisoni

Hi Diogo,

I mean the package only contain newly created object, removed objects and modified objects

Is there a way to make it? I am using SQL Source Control for version control with mapped to working folder.

Thanks In advance

Nirmal

Diogo

I'm sorry @Nirmal_Raisoni but I don't think I've fully understood it yet. Yes, the package contains new objects, removed objects and modified objects which is what you would need to do a database deployment in order to update it.
Are you after a script that only has new objects in it and ignores modified and removed objects? If it's that I don't think there is a way to get it at the moment. What would that allow you to do?

way0utwest

What is your source for the Jenkins package? Are you using SQL Source Control or SCA in Visual Studio to do this?

The SCA project should produce a set of all migration scripts, but also a package of just changes, based on the target db being set to the downstream environment.

way0utwest

If you're using SQL Source Control, the output of the build is the entire repo, because this is the state of the database. Once you use an SCA cmdlet to produce a deployment package that is just the changes.

Nirmal_Raisoni

Thanks wayOutwest,

Yeah I am using SQL Source Control and In Jenkins it is not generating a package of difference,

I mean I am using New-DatabaseReleaseArtifact -Source $stage -Target $package -FilterPath $filter and it is not generating artifacts in packge?

Any idea?

way0utwest

So, if you look in the folder where you've specified for the release, there should be an Update.sql file. When I run this in TFS/VSTS/Octopus, this is what is generated as a set of artifacts. You can also run this from the command line, using the parameters for your system and look at the ouput. I assume you have a build, correct? The output of the build should be a nuget of all your vcs.

In my release folder, the folder that I have specified as the location for the package , here's what I see:

Nirmal_Raisoni

Thanks wayOutwest and Diogo