CI/CD – SSIS via Jenkins

Hi

As we all know from many blog post the benefits and the functionality the Jenkins setup provides to any enterprise development setup. So in this post I would not talk much about CI/CD concepts and the theories around it.

I this post I would like to present the way we have setup the CI/CD process for the SSIS packages for our organization. This setup has greatly simplified our deployment processes related to SSIS and provides a good team collaboration.

Background: We have a Jenkins setup on Windows 2016 server, including the Visual Studio Team Explorer 2017 for the TFS CLI.

The key task we want to achieve in our SSIS automation are as illustrated:

Step 1, TFS download the latest Source Code Change: This was a step which took bit of struggle to setup in this process. But the following script I believe would greatly help for any one try to achieve the same.

SET Path=%PATH%;"C:\Program Files (x86)\Microsoft Visual Studio\2017\TeamExplorer\Common7\IDE\CommonExtensions\Microsoft\TeamFoundation\Team Explorer";
cd C:\tfs
tf get /login:devopuser@org.com,devoppwd /noprompt

Step 2, SSIS Build the Latest ISPAC from the Source Code: For this step one has to download the SSIS tools provided by Microsoft (link below) and the following command will build the ISPAC as required.

cd C:\build\ssis
SSISBuild.exe -p:"C:\tfs\SSIS\IntegrateDataToBI\IntegrateDataToBI\IntegrateDataToBI.dtproj" -pp:projectpwd

Step 3, SSIS Deploy the latest ISPAC to SSIS Catalog Server: For this step also one has to download the SSIS tools provided by Microsoft (link below, as previous) and the following command will build deploy the latest build ISPAC to the SSIS Catalog Server.

cd C:\build\ssis
SSISDeploy.exe -s:"C:\tfs\SSIS\IntegrateDataToBI\IntegrateDataToBI\bin\Development\IntegrateDataToBI.ispac" -d:"CATALOG;/SSISDB/Data Load;HFMNAVBI\SSIS" -at:win -pp:projectpwd

And Voila, our task listed above can be orchestrated by Jenkins (Freestyle Project, Execute Windows Batch Command Task), so that each deployment is now controlled, audited and governed.

Important Links:

SSIS Build/Deploy Tools: https://docs.microsoft.com/en-us/sql/integration-services/devops/ssis-devops-standalone?view=sql-server-ver15

The documentation of the above tools, is pretty good and easy to understand, So I hope the above helps in adopting the CI/CD concepts in your BI world too.

Important Note:

I faced another difficulty with the above Microsoft SSIS Build/Deploy tool where the tools constantly kept failed in the Build step for the following error:

System.IO.FileNotFoundException: Could not find file ‘C:\tfs\SSIS\IntegrateDataToBI\IntegrateDataToBI\obj\Development\BI_DB.conmgr’.
File name: ‘C:\tfs\SSIS\IntegrateDataToBI\IntegrateDataToBI\obj\Development\CUB_BI.Analytics.conmgr’
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize)
at System.Xml.XmlDownloadManager.GetStream(Uri uri, ICredentials credentials, IWebProxy proxy, RequestCachePolicy cachePolicy)
at System.Xml.XmlUrlResolver.GetEntity(Uri absoluteUri, String role, Type ofObjectToReturn)
at System.Xml.XmlTextReaderImpl.OpenUrlDelegate(Object xmlResolver)
at System.Threading.CompressedStack.runTryCode(Object userData)
at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)
at System.Threading.CompressedStack.Run(CompressedStack compressedStack, ContextCallback callback, Object state)
at System.Xml.XmlTextReaderImpl.OpenUrl()
at System.Xml.XmlTextReaderImpl.Read()
at System.Xml.XmlLoader.Load(XmlDocument doc, XmlReader reader, Boolean preserveWhitespace)
at System.Xml.XmlDocument.Load(XmlReader reader)
at System.Xml.XmlDocument.Load(String filename)
at Microsoft.SqlServer.IntegrationServices.RuntimeObjectModel.Default.Project.GetConnections()
at Microsoft.SqlServer.IntegrationServices.RuntimeObjectModel.Default.Project.Load(XmlDocument document)
at Microsoft.SqlServer.Dts.Runtime.Project.OpenProject(IProjectStorage projectStorage, String projectPassword)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow)

To resolve the above please navigate to you SSIS project in the Visual Studio, and ensure the File Name of the Connection and the Internal name of the Connection are exactly the same when opened as a file (via F7) XML.

The above change will not impact the downstream referencing packages which are using the project level connection (shared).