This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.
SQL Server Integration Services (SSIS) as an on-premises ETL platform has been around for a very long time – more than 15 years I believe – an eternity in the Internet/cloud computing age. During that time, a lot of investments have been made to extend its data extraction/transformation/ingestion capabilities with additional components beyond the built-in ones that are bundled with SQL Server.
These additional components come out-of-the-box to be downloaded separately and installed on local/production machines for designing SSIS packages/running SSIS workloads. They include:
- Advanced/premium components for SSIS Enterprise Edition
- Privately built custom components that serve enterprise-specific purposes
- Publicly shared Open Source components that are developed by SSIS user community
- Commercially licensed/sold components that are developed by our ISV partners/other 3rd parties
Custom Setup Interface for SSIS IR
In 2018, when we released SSIS as an ETL Platform as a Service (PaaS) in Azure Data Factory (ADF), we made sure that all these additional components can still be installed on SSIS Integration Runtime (IR) – dedicated ADF servers for running SSIS workloads – via a custom setup interface, see https://techcommunity.microsoft.com/t5/SQL-Server-Integration-Services/Enterprise-Edition-Custom-Setup-and-3rd-Party-Extensibility-for/ba-p/388360.
The custom setup interface provides almost limitless possibilities to customize your SSIS IR – Essentially, anything you can install on your SSIS machine on premises, you can also install on SSIS IR using this interface. Unfortunately, with great flexibility comes great complexity, as this interface requires you to do the following:
- Prepare your own custom setup script (main.cmd) + associated files
- Bring your own Azure Storage account to store your custom setup script + associated files + setup execution logs in a blob container
- Prepare your Shared Access Signature (SAS) Uniform Resource Identifier (URI) for your blob container with a sufficiently long expiry time and read + write + list permissions
- Ensure that your SAS URI and custom setup resources are always available during the whole lifecycle of your SSIS IR
After a period of time, we noticed that there are common/popular custom setups that are repeatedly configured by many users when they provision their SSIS IR, so we’ve decided to make life a little bit easier for them and those who follow them later by introducing express custom setups.
Express Custom Setups for SSIS IR
Express custom setups are common/popular/selected custom setups that have been deeply integrated into SSIS IR, such that you need not prepare any scripts to configure them nor bring your own blob container in Azure Storage to store associated files. It’s as simple as selecting your express custom setup types when provisioning SSIS IR and entering the values for relevant custom setup arguments. At the time of writing, we’ve released the following express custom setup types:
- Run cmdkey command, where you can persist access credentials for your file shares/Azure Files on SSIS IR by entering your targeted computer/domain names, account names/usernames, and account keys/passwords
- Add environment variable, where you can add Windows environment variables to use in your packages that run on SSIS IR by entering your environment variable names and values
- Install licensed component, where you can then select any integrated components from our ISV partners, e.g.:
- SentryOne's Task Factory to install the Task Factory suite of components from SentryOne on your SSIS IR by entering the product license key that you purchased from them
- oh22's HEDDA.IO to install the HEDDA.IO data quality/cleansing component from oh22 on your SSIS IR after purchasing their service
- oh22's SQLPhonetics.NET to install the SQLPhonetics.NET data quality/matching component from oh22 on your SSIS IR by entering the product license key that you purchased from them
- More integrated components from our ISV partners are coming soon!
For more info, see https://docs.microsoft.com/azure/data-factory/how-to-configure-azure-ssis-ir-custom-setup.
I hope you’ll find these express custom setups useful and please don’t hesitate to contact us if you have any feedbacks/questions/issues on using them or suggestions for more common/popular custom setups to integrate into SSIS IR. Thank you as always for your support.