I have to automate the way we build and deploy our SSIS projects. We use VS2013, SQLSERVER2014 and TFS2015. I'm already aware of two solutions:
1) MSBuild: I followed this blog https://speaksql.wordpress.com/2013/06/07/a-journey-to-db-deployment-automaton-ssis-build-using-msbuild/ and it works fine for VS2010 but unfortunately not for VS2013.
The error I'm getting is:
Project "SSISTest\SSISTest\SSIS.MSBuild.proj" on node 1 (SSISBuild target(s)). SSISBuild:**************Building SSIS project: ..\SSISTest\SSISTest.dtproj for onfiguration: Development************** ------ Loading project file '..\SSISTest\SSISTest.dtproj' Setting output directory to '..\SSISTest\bin\Development' Setting project ProtectionLevel to 'DontSaveSensitive' Loading package '..\SSISTest\Package.dtsx' error : Error while loading package '..\SSISTest\Package.dtsx': The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails.\r error : error : The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails.\r error : \r error : at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(String packageXml, IDTSEvents events)\r error : at Microsoft.SqlServer.IntegrationServices.Build.DeploymentFileCompilerTask.LoadPackage(String path)\r error : at Microsoft.SqlServer.IntegrationServices.Build.DeploymentFileCompilerTask.Execute() Done Building Project "SSISTest\SSISTest\SSIS.MSBuild.proj" (SSISBuild target(s)) -- FAILED.
2) devenv: works fine but our projects are all protected with a password, and everytime you try to build it prompts the dialog to insert the password.
Is there any other way I can automate this in VS2013?
I'll answer myself. After adding the proper dll references (expecially the Microsoft.SqlServer.ManageDTS one) for VS2013 to the http://sqlsrvintegrationsrv.codeplex.com/SourceControl/latest project, generating the .dll allowing MSBuild to build SSIS project as suggested by https://speaksql.wordpress.com/2013/06/07/a-journey-to-db-deployment-automaton-ssis-build-using-msbuild/, worked just fine. I'm now able to automatically build SSIS projects with MSBuild and deploy them to a SQL Server 2014 instance.