Build and Deploy SSIS packages using VSTS

Question

I created the simple SSIS project in VS2015 with SQL Server 2012 as a targeted server, it will worked in local machine successfully without any issues. After that I have created the build definition and release definition by following this link.

For this project build will be success. But release definition failed with this “##[error]Task_InternalError Exception calling ".ctor" with "1" argument(s): Failed to connect to server DNS name label.eastus.cloudapp.azure.com,1433" error message in Deploy SSIS step like this below figure. But here I used the virtual machine with SQL server2012 for hosting the SSIS package.

Can you please tell me how to resolve the above issue as soon as possible and also tell me is there any alternative way to deploy the SSIS project using VSTS?

Answer 1

Based on the source code, the connection uses windows authentication (Integrated Security=SSPI), but the Hosted agent uses service account, so it can’t connect to the server.

You can refer to the source code to build the PowerShell script to deploy SSIS project and the project name is the name of the SSIS project. (Using SQL Server authentication: User ID=XXX;Password=XXX)

Another article with the code about deploying SSIS: Deployment Automation for SQL Server Integration Services (SSIS)