Wiki  >  TechNet Articles  > 

Using and Packaging SQLDeploy Utility to deploy Dacpac - TechNet Articles - United States (English) - TechNet Wiki

I recently hit a problem where we were trying to build DACPAC using VSTS2012, to be deployed in SQL Server 2008 R2. I believe this is worth sharing.

To deploy DAC the following approach can be leveraged:
 

DAC Depolyment Approach

1. Using SSMS

2. Using SqlPackagement page no-wrapper" id="fragment-6615">

Using and Packaging SQLDeploy Utility to deploy Dacpac

I recently hit a problem where we were trying to build DACPAC using VSTS2012, to be deployed in SQL Server 2008 R2. I believe this is wor

Please note the DACPAC referred here is built using VSTS 2012.


Using SSMS

When trying to import DACPAC using SSMS of 2008 R2 it refused to run throwing this serialization exception.

Quite anticipated as the DACPAC was built using new version of DAC version.

So workaround is to connect to SQL Server 2008 R2 using SSMS from SQL Server 2012 and import the data tier application. It will work perfectly as below snapshot says.


Using SQLPackage.exe MSDN

I tried to follow the steps from http://technet.microsoft.com/en-us/library/ee210569%28v=sql.105%29.aspx but it didn't work for me. 

For me deployment is happening in target system which didn't had Sql Server 2012, Which means I have to provide SqlDeploy.exe as part of my deployment package.
When I found out that it worked with VSTS 2010 DACPAC package. I decided to switch to SqlDeploy. You can find SqlDeploy.exe @ C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\  location, if Sql Server 2012 is deployed. 

Workaround found

  1. I found the link http://sqlblog.com/blogs/jamie_thomson/archive/2012/05/30/redistribution-of-sqlpackage-exe-ssdt.aspx, which provided me option to install 4-5 msi in the deployment machine but that again requires quite an effort especially when this need to deployed in multiple environments.
  2. Download ISO from http://download.microsoft.com/download/3/4/5/3458A46A-5593-40B2-BA56-81A713D21B81/SSDT_11.1.30914.0_EN.iso and deploy in the machine which handles deployment. Same reason waste of effort for small tool. My problem is we had all the database instances running in SQL Server 2008 R2 and installing SSDT tool though free was a major amount of effort in all the environments dev, test, integration, pre-production, etc.

    Since the above two workaround didn't work for me, I decided to find one myself and came up with one.

Wrap SqlDeploy Utility in deployment package

So I decided to wrap SQLDeploy utility in my deployment package itself. This requires to find all the dependencies which are required by this utility. After some research I was able to find the set of dependent assemblies and files which can be independently taken to any environment and executed in standalone. It worked fine in most of the environments. Most of the files you can find from any SQL server 2012 deployed machine(C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\) with the exception for Microsoft.SqlServer.TransactSql.ScriptDom.dll, which you can find in C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies folder.

SqlDeploy Command Example

SqlPackage /Action:Publish  /Sf:Northwind.dacpac  /TargetDatabaseName:Testdac  /TargetServerName:MyServer /Variables:INSERTDATA=0 /Variables:IPADDRESS=127.0.0.1 /Variables:DATABASE_CONNECTIONSTRING="Data Source=.;initial catalog=Engine;integrated security=True;MultipleActiveResultSets=True;"


Please let me know if you are able to import DACPAC using the above files.


Please let me know if you are able to import DACPAC using the above files.