0

From a two .dacpac files I can get the update script using following command

sqlpackage.exe /Action:Script /SourceFile:"C:\Test\bin\Debug\Test.dacpac" /TargetServerName:localhost\DenaliRC0 /TargetDatabaseName:deploy_Test

How ever this will generate only DDL changes (schema changes). But that not included any data changes. Is there anyway I can get those Data changes (DML) from two dacpacs?

Flowing is the java code i use to get schema changes.

 CommandLineUtils.execute(this.log, "sqlpackage.exe", new String[]
{
  "/Action:Script",
  "/SourceFile:" + targetDacPac.getAbsolutePath(),
  "/TargetFile:" + previousDacPac.getAbsolutePath(),
  "/OutputPath:" + scriptOutput.getAbsolutePath(),
  "/TargetDatabaseName:changeme",
  "/p:ScriptDatabaseOptions=False",
  "/p:IgnoreAuthorizer=True",
  "/p:IgnoreLoginSids=False",
  "/p:DropObjectsNotInSource=True",
  "/p:IgnoreFilegroupPlacement=False"
});
Dasun
  • 19
  • 1
  • 5
  • Are you using pre or post-deploy scripts to handle data changes? That's where you'd need to put the changes in order to have them pushed from your dacpac to your server. How are you handling your data changes within the project right now? – Peter Schott Nov 19 '13 at 00:30
  • I have edit the question to explain what I actually do in code. I m really new to this area. what I do is take a .dacpac file from a database and do some DDL changes like create table and inserting data to that. Problem is Im getting the data changes to the script but didnt get Data (insert) changes. Is there any option to get those data changes also into the script file? – Dasun Nov 19 '13 at 05:07
  • Im getting the create table part in the script file but insert statements not generated. – Dasun Nov 19 '13 at 05:17

1 Answers1

2

I think you need to look at pre and post-deploy scripts to handle getting your data into the table and/or modifying existing data. SSDT (and its predecessors) do not handle data within the project except for what is called a "bacpac" file. That's a one-time schema and data combination with the schema stored in the normal XML format and data stored in native format BCP files (if I understand it correctly).

I'd recommend checking out this article on pre/post deploy scripts: http://schottsql.blogspot.com/2012/11/ssdt-pre-and-post-deploy-scripts.html

You can also see what I've written on SSDT here - you may find something helpful: http://schottsql.blogspot.com/2013/10/all-ssdt-articles.html

Peter Schott
  • 4,521
  • 21
  • 30
  • As I understood pre and post deploy scripts help to manipulate data before or after schema changes.But that is not what actually needed. But rather I need the data changes within two dacpacts (snapshots). May be Change Data Capturing is one way to do this. But I want to generate the Data as script from two dacpac files. up to now it seems not possible. @peter thanks for your answer. your blog has lot of important information about SSDT. – Dasun Nov 25 '13 at 10:35
  • 1
    In this case, it's not possible. bacpac files are a one-time deal to load data. dacpac files are primarily to synchronize the schema, but can also do some data manipulation in the pre/post deploy scripts. Your best bet for that may be looking at Red-Gate's SQL Data Compare. If you get the Pro version, you can automate the comparison and sync, but it only works between databases. – Peter Schott Nov 25 '13 at 17:46