Build and Deploy SSIS packages using VSTS

1
votes

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. enter image description here 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?

1
ssissql-server-2012sql-server-data-toolsazure-pipelinesazure-pipelines-release-pipeline
Do you open 1433 ports to a virtual machine with the Azure portal? Do you configure TCP/IP for SQL Server? Connect to Windows Azure Virtual Machine Using SSMS? Can you connect to that SQL Server from your local machine? – starian chen-MSFT
Yes, I am able to connect the SQL Server inside virtual machine from my local machine. – Pradeep
Set system.debug to true, then queue build and post the detail log here (can share it on the OneDrive). – starian chen-MSFT
Logs available in this link 1drv.ms/u/s!At-JUB9_wu9CvWv57X_rRyhkH4xD – Pradeep
I followed this link marketplace.visualstudio.com/items?itemName=TG.VSTS-SSIS to setup release definition for SSIS package, but I did not understand what are project parameters are given. So, can you please tell me which project parameters are given in the Deploy SSIS step in release definition? – Pradeep

1 Answers

1
votes

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)