1

Is there functionality that I am missing within Visual Studio Data Tools (2015) that allows you to export the results of a data comparison? It looks like you can only export the DML script generated? Thanks!

RJ7
  • 883
  • 9
  • 17

1 Answers1

1

The functionality was deprecated and hidden in Visual Studio 2015 and later. It wans't removed completely - you can still create a data comparison by creating a file with the .dcmp extension and adding it to your Database project. Once you open it, you'll be presented with the Data Comparison wizard.

Unfortunately, you can't save your table selections and options. The only things that can be saved in the file are the connection strings. This means that you'll always have to use the wizard to make a data comparison.

For example, one of my dcmp files looks like this :

<?xml version="1.0" encoding="utf-8"?>
<DataComparison>
  <Version>10</Version>
  <ConnectionProperties1>
    <ConnectionString>Data Source=1.2.3.4;Initial Catalog=MyDb;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True</ConnectionString>
    <FullDisplayName>My Source Name</FullDisplayName>
  </ConnectionProperties1>
  <ConnectionProperties2>
    <ConnectionString>Data Source=A.B.C.D;Initial Catalog=MyDB;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=True</ConnectionString>
    <FullDisplayName>My Target Name</FullDisplayName>
  </ConnectionProperties2>
  <SessionSettings>Default</SessionSettings>
</DataComparison>

BTW, SSDT is now a separate download currently at version 17.3 for Visual Studio 2015, 15.4 Preview for VS 2017.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
  • Hi, just trying to understand... is ssdt and ssdt-bi different? I am googling it as well. I thought Visual Studio Shell Extension which includes SSIS/AS/RS is nothing but ssdt. Are we referring to SQL Server within VS ie (Web Install) as SSDT ? – Prabhat G Oct 31 '17 at 14:37
  • @PrabhatG as I said, these are a separate download nowadays. Check the link for details. Things *are* messy right now, with the 2015 components more stable than 2017. The standalone package contains all features. What used to be BI are now also available as individual VS extension (SSIS, SSRS, SSAS) . Data Comparison was a feature of the core Database project, not BI – Panagiotis Kanavos Oct 31 '17 at 14:42
  • Thanks @PanagiotisKanavos - I did try to save the data comparison but I am using alternate keys, so I quickly found out that the settings of the compare don't save. Cheers! – RJ7 Oct 31 '17 at 14:57
  • @RJ7 you should probably look to Redgate's tools, despite the cost. Microsoft already bundles some of them in the Enterprise version. It doesn't look like Data Comparison is coming back – Panagiotis Kanavos Oct 31 '17 at 14:58
  • Please note that for VS2017, all the relational DB support ships as part of Visual Studio updates. There is a standalone installer but this is to include relational DB, SSAS, SSRS and SSIS in one package since SSIS still requires extra components that can't be included in a simple extension. That's on the way towards a full GA release but the DB, SSAS and SSRS components are all GA and fully supported in VS2017 – Kevin Cunnane Oct 31 '17 at 18:55
  • @KevinCunnane unfortuantely, no. SSRS, SSAS are *not* included in the VS 2017 updates at all, they have to be added as VS Extensions. SSIS is only available through the *standalone* preview download. The teams' priorities were to get SQL Server 2017 support to VS 2015 and *then* start work on VS 2017. Even the *core relational* feature that comes with 2017 updates lags compared to the 2015 features. The last year has been very frustrating, to put it kindly. You had to use both versions of Visual Studio up until September 2017 to get all components – Panagiotis Kanavos Nov 01 '17 at 08:23
  • @KevinCunnane for example, up until August or September, SSDT on VS 2017 SSDT *couldn't* detect a change in column orders, although SSDT on VS 2015 could. This resulted in a lot of unneeded change scripts – Panagiotis Kanavos Nov 01 '17 at 08:26