What image is used to automate SSIS package deployments to SQL Server through bitbucket cloud?

user_11669889 0
Viewed 352

I'm trying to automate the deployment of an SSIS package to a SQL Server instance's Integration Service Catalog, using bitbucket cloud pipelines.

Visual Studio based apps are normally fine, as you can just pull down the microsoft/dotnet:sdk image at the top of the .yml and msbuild will do its thing.

SSIS packages however need devenv to build, which means the build as above fails.

I'm hoping someone else has managed to get devops in bitbucket working for SSIS packages and can point me in the right direction for an image that supports devenv builds on Bitbucket cloud.

devops bitbucket ssis sql-server
1 Answers

Yes it is disappointing how the data side is not supported in DevOps.

To generate an .ispac from a .sln on a Hosted VS2017 build agent, use this command line:

"C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\devenv.com" ./YourSolution.sln /build

(I found it on the internet somewhere)

I haven't worked out how to deploy, because you need SSIS libraries installed, which the build agent doesn't have as far as I can tell, and I can't work out how to install them.

My next try is to use catalog.deploy_project, which should only need SQL libraries to connect to the SSIS instance

https://docs.microsoft.com/en-us/sql/integration-services/deploy-and-execute-ssis-packages-using-stored-procedures?view=sql-server-2014

Here's a powershell script that you should be able to adapt to DevOps:

https://www.powershellgallery.com/packages/AssistDeploy/1.3.0.0/Content/Functions%5CPublishSsisIspac.ps1

It doesn't use any integration services libraries. It does use System.Data.SqlClient. This should be on the Hosted VS agent. You should be able to open your ISPAC from your artifacts and use it

user_16901930
Related