Scripts? We don’t need no scripts! – Express custom setups for SSIS in Azure Data Factory

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 3^rd 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:

1. 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
2. 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
3. Install Azure PowerShell, where you can you can install the Az module of PowerShell by entering the required version number, so you can run Azure PowerShell cmdlets/scripts in your packages that run on SSIS IR to manage Azure resources, such as Azure Analysis Services (AAS)
4. Install licensed component, where you can then select any integrated components from our ISV partners, e.g.: 
   1. 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
   2. oh22's HEDDA.IO to install the HEDDA.IO data quality/cleansing component from oh22 on your SSIS IR after purchasing their service
   3. 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
   4. KingswaySoft's SSIS Integration Toolkit to install the SSIS Integration Toolkit suite of connectors for CRM/ERP/marketing/collaboration apps, such as Microsoft Dynamics/SharePoint/Project Server, Oracle/Salesforce Marketing Cloud, etc. from KingswaySoft on your SSIS IR by entering the product license key that you purchased from them
   5. KingswaySoft's SSIS Productivity Pack to install the SSIS Productivity Pack suite of components from KingswaySoft on your SSIS IR by entering the product license key that you purchased from them
   6. Theobald Software's Xtract IS to install the Xtract IS suite of connectors for SAP system (ERP, S/4HANA, BW) from Theobald Software on your SSIS IR by uploading the product license file that you purchased from them
   7. AecorSoft's Integration Service to install the Integration Service suite of connectors for SAP and Salesforce systems from AecorSoft on your SSIS IR by entering the product license key that you purchased from them
   8. CData's SSIS Standard Package to install the SSIS Standard Package suite of most popular components from CData, such as Microsoft SharePoint connectors, on your SSIS IR by entering the product license key that you purchased from them
   9. CData's SSIS Extended Package to install the SSIS Extended Package suite of all components from CData, such as Microsoft Dynamics 365 Business Central connectors and other components in their SSIS Standard Package, on your SSIS IR by entering the product license key that you purchased from them
   10. 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.