The March 2014 release of SSDT added support for SQL Server 2014 databases. But it ALSO provided new features in VS 2012 and VS 2013 for sorting and filtering the data in the Data Editor!

If you are using VS 2012, you can use the update option to get this update (SQL | Check for Updates).

If you are using VS 2013, the update should appear in the Notification window when you click the notification flag:

If not, you can look for it under Tools | Extensions and Updates.

Once you have it installed, your Data Editor will have two additional buttons:

The first button disables sorting and filtering.

The second button displays a dialog for entry of the sorting and filtering criteria.

In this example, I set a sort on the FirstName column. Any number of sorts can be added, either ascending or descending. I also added a filter on the LastName so only customers with a last name that begins with B will be listed.

This dialog also allows you to uncheck columns to remove them from the Data Editor display. This helps you focus on only the columns you need to see.

Notice at the bottom of the dialog is the SQL expression SSDT will use to query the table. This expression changes as you modify the columns, sorting, sort order, or filter.

Clicking Apply immediately executes the query and re-populates the data in the Data Editor. This allows you to view the results without closing the Filter and Sort dialog.

When you have the results you need, click OK to close the dialog.

NOTE: When I attempted to edit the sorted and filtered data, I received an error message:

HOWEVER, the edits were actually made to the data.

Try out these new features any time you want more control over the data displayed in the Data Editor.

There are several different tools that you, the DBA, or another individual can use to deploy a DACPAC as defined in this prior post. This current post details how to deploy a DACPAC using the DacFx API.

The DacFx API is a set of classes that you can use in your code to perform operations on a DACPAC. This allows you to write your own DACPAC utility application or include DACPAC functionality in any application.

DacFx API Version 3.0 is defined in Microsoft.SqlServer.Dac, which is a different DLL than prior DacFx API versions. Along with a new DLL, the functionality in Version 3.0 changed significantly from prior versions. The information in this post is for Version 3.0 and won’t work with prior DacFx API versions.

NOTE: DacFx 3.0 can work with DACPACs from older versions, but only generate Dac 3.0 formats.

Why would you want to write your own code to process a DACPAC when you can deploy a DACPAC with existing tools?

You can completely control the target connection and database(s) used, the DACPAC that is used, and the deployment options.

You can repeat the processing of the DACPAC for multiple databases.

For example, say you have a set of testers, each with their own copy of the database so they can better verify their results. You can store their connections in a table or configuration file. Then write a DACPAC utility application that loops through each connection and deploys the DACPAC to each tester’s database in one operation.

The code below is in C#, but this technique works in VB.NET as well.

using System; using System.Collections.Generic; using Microsoft.SqlServer.Dac;

A MessageList property retains any messages generated by the process. The code using this class can display the contents of this list.

The constructor initializes the MessageList.

The only method in this class deploys a DACPAC file.

The parameters to the method define the appropriate target connection, target database, and DACPAC path and file name.

The process kicks off with a starting message in the MessageList.

If desired, you can define deployment options. In this example, the only option that is set is the BlockOnPossibleDataLoss.

An instance of the DacFx DacServices class is then initialized.

Optionally, you can elect to respond to ProgressChanged events. These events are invoked when the state of the operation changes. In this case, any ProgressChanged message are added to the MessageList.

Optionally, you can elect to respond to Message events. These events are invoked when an operation reports status updates or errors.

Within a Try block, the code loads the DACPAC using the DacFx DacPackage class. The argument is the full path and file name to the DACPAC file.

Finally, the DACPAC is deployed using the Deploy method of the DacServices class.

To see how this method is called, here is an automated code test:

using DacpacUtility; using Microsoft.VisualStudio.TestTools.UnitTesting;

There are several different tools that you, the DBA, or another individual can use to deploy a DACPAC as defined in this prior post. This current post details how to deploy a DACPAC using Windows PowerShell.

The first line adds the Dac DLL to the PowerShell session. Add-Type is a Utility Cmdlet that adds any .NET Framework type to a PowerShell session. Change the directory as appropriate for your system.

The second line creates an instance of the DacServices object and defines the SQL Server instance for the connection. In this example, we are using SqlExpress.

The third line loads the DACPAC. Be sure to change <Path> to the path of your DACPAC and that it is all on one line.

The last line performs the deployment. The first argument is the loaded DACPAC. The second argument is the database that is the target of the deployment. The third argument is whether to allow update of an existing schema. This is "True" because we want to allow this script to upgrade the TestACM database if it already exists.

There are several different tools that you, the DBA, or another individual can use to deploy a DACPAC as defined in this prior post. This current post details how to deploy a DACPAC using SqlPackage.

SqlPackage is a command line utility that automates DACPAC operations, including publishing a DACPAC to a target database. On my system, I found SqlPackage.exe in: C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin.

SqlPackage has many parameters, properties, and variables you can use to perform DACPAC operations. See this MSDN link for details.

The benefits of using SqlPackage are:

Its straight-forward and the API is clear.

You can include it in a batch file.

You can use it to automate your deployment process.

Here are the basic steps for deploying a DACPAC using SqlPackage from the command line:

Open a command prompt (cmd.exe).

Execute SqlPackage.exe with the desired parameters.

For example: to deploy a DACPAC using a publishing profile, the command would look like this:

There are several different tools that you, the DBA, or another individual can use to deploy a DACPAC as defined in this prior post. This current post details how to deploy a DACPAC using SQL Server Management Studio.

Many DBA’s are more comfortable using SQL Server Management Studio. So if the developers provide a DBA with a DACPAC, the DBA may prefer to use SQL Server Management Studio to deploy it.

This post covers the steps for using SQL Server Management Studio 2012 to deploy a DACPAC. For SQL Server Management Studio 2008 R2, the steps are different and it only supports older versions of DACPACs. However, you CAN use SQL Server Management Studio 2012 to connect to an older SQL Server instance, such as SQL Server 2005 or 2008 R2, and deploy a DACPAC.

Here are the steps for deploying a DACPAC with SQL Server Management Studio 2012:

Open SQL Server Management Studio.

Connect to the SQL Server Instance containing the database to deploy to.

Notice that in the example below, I am connecting to a SQL Server 2008 R2 SQL Express instance.

Navigate the tree in the Object Explorer to the database to deploy to.

Right-click on the database name and select Tasks | Upgrade Data-tier Application…

The Upgrade Data-tier Application wizard is displayed. It presents the set of steps that will be executed by the wizard.

Click Next.

Use the Browse button to find and select the DACPAC file. It is located in the Bin\Debug directory of the database project on the developer’s machine. Or where ever it was put if the file was provided to a DBA or other individual.

If the target database was changed externally from a DACPAC deployment (by making changes directly to the database), you are warned that the database was changed.

You can optional proceed anyway, or exit the wizard and research the issue.

Click Next.

The deployment script is automatically generated.

From the above dialog, you can optionally select to save the deployment script to a file.

When ready, click Next.

The Upgrade Plan is then displayed for review.

You can elect to save the report.

You again have the option to save the generated script.

Click Next.

A summary is displayed containing the information that will be used during the deployment.

Note that in the above screen shot, I opened each node in the summary so you can see the details.

When you are happy with the summary information, click Next and the deployment process (finally!) begins.

The generated script is executed, completing the DACPAC deployment process.

If desired, click Save Report.

Then click Finish.

If you compare this process to the steps for deployment using Visual Studio, you can see that there are MANY more steps. But the DBA may be happy with all of these extra steps because they provide additional verification before deploying the DACPAC.

Enjoy!

For more information on this and other SQL Server Data Tools (SSDT) features, see my latest Pluralsight course: "Visual Studio Data Tools for Developers", which you can find here.

There are several different tools that you, the DBA, or another individual can use to deploy a DACPAC as defined in this prior post. This current post details how to deploy a DACPAC using Visual Studio.

Starting with Visual Studio 2013, the SQL Server data tools (SSDT) are available in*every* edition of Visual Studio, including the Express editions. So the DBA or other deployment personnel can use one of the free Express editions of Visual Studio to manage database projects and deploy DACPACs.

If you have Visual Studio 2012 or older, see this blog post for information on obtaining the appropriate SSDT.

If you are using Visual Studio 2012 or above, you can create a SQL Server Database Project to include all of your table, view, stored procedure, and data scripts. (See #1 below)

When you build the Database Project (#2), Visual Studio generates a DACPAC file (#3). The DACPAC is a single unit of deployment that you, your DBA, or another individual can use to deploy database schema changes or reference data.

When deployed, the SQL Server Data Tools (SSDT) perform some magic. They compare (#4) the contents of the "master" database as defined in the DACPAC (#3) to the target deployment database.

SSDT then automatically generates (#5) a change script (#6). The change script includes all of the changes to apply to the target database to bring its schema up to match the schema defined in the DACPAC and to deploy any reference data defined in the DACPAC.

The SSDT then executes (#7) the script against the target database and the deployment is complete.

COOL!

So I created my database project, built it to generate the DACPAC. Now what? How do I make it do all of that magic? How do I (or my DBA) actually deploy the DACPAC?