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:email@example.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.
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.
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.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.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).
3 Replies to “CI/CD – SSIS via Jenkins”
Hi Amol, I have sql server on azure windows VM, will the above steps work for sql server on azure VM? My other question is how does win authentication happens when I configure these steps in Jenkins? Do I need to provide any permissions to any user? Your response is highly appreciated, thanks.
Can you pls explain how does windows authentication happens when running from Jenkins?
Our Jenkins runs on a windows server which is on the same domain as the SQL server On-Prem and the service account have appropriate rights in SQL server under which the Jenkins service runs. Hence the SSISBuild/Deploy commands work seamlessly for us.
For further reference, please visit to MSDN link for details suited for your setup.
Standalone SQL Server Integration Service (SSIS) DevOps Tools