![\"Azure](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/azure-data-factory-adf-logo.png\"){kind=logo}
<\/a>If you are using SSIS for your ETL needs<\/strong> and looking to reduce your overall cost then, there is a good news. Microsoft recently announced support to\u00a0run SSIS in Azure Data Factory (SSIS as Cloud Service).<\/strong>\u00a0Yes – that’s exciting,\u00a0 you can now run SSIS in Azure without any change in your packages (Lift and Shift<\/strong>).\u00a0 SSIS Support in Azure is a new feature of Azure Data Factory V2 (i.e. ADF)<\/a>. This article also explains how to deploy SSIS Packages to Azure which use 3rd party components<\/strong> (e.g. SSIS PowerPack<\/a>\u00a0v2.6.13 or Higher<\/strong>). There are few additional steps involved if you wish to install Custom SSIS Components<\/strong> in Azure Data Factory (explained later in this article).<\/p>\n<\/p>\n
What is Microsoft Azure Data Factory (i.e. ADF)<\/h2>\n
Azure Data Factory (i.e. also referred as “ADF”<\/em>) is a fully managed cloud service by Microsoft for your ETL needs. ADF comes with two completely\u00a0different ETL approaches (referred as Integration Runtimes).<\/p>\n Both runtimes offer their\u00a0own Authoring\u00a0tools and they have completely\u00a0different authoring, execution, and pricing models<\/a>. Check later in this article for some tips to save Cost by automating Start \/ Stop of SSIS IR based on your limited execution need (Only Start SSIS-IR when you need to run package).<\/p>\n Choose ADF SSIS Runtime when:<\/strong><\/p>\n Choose ADF Default Runtime (Copy Activity) when:\u00a0<\/strong><\/p>\n There few obvious advantages if you decide to run SSIS Package in Azure Data Factory (ADF) compared to running on your own dedicated server (on-premises or VM running in Cloud).<\/p>\n Anyway’s enough said now let’s see how to provision and deploy SSIS packages in Azure Data Factory (ADF).<\/p>\n Before we deep dive to learn how to run packages in Azure Data Factory. Here are high-level steps to deploy SSIS Packages in Azure Data Factory. Here is another tutorial link<\/a> from Microsoft (It doesn’t mention deploying third-party SSIS components but good to check anyway). Some steps are optional and only needed if you planning to run SSIS Packages with third-party components such as\u00a0SSIS PowerPack<\/a><\/p>\n In the following sections, we will see each step in detail.<\/p>\n There are a few important facts you should know about how Azure Data Factory (ADF) Hosts, Runs your SSIS Packages<\/p>\n Many customers ask whether they should run their SSIS packages on an Azure Virtual Machine (VM)<\/strong> or inside Azure Data Factory (ADF)<\/strong> using the Azure-SSIS Integration Runtime (IR)<\/strong>. Both deployment options are supported by ZappySys<\/strong>, but the best choice depends on your workload characteristics, cost model, and operational requirements.<\/p>\n Running SSIS packages in ADF with Azure-SSIS IR<\/strong> is fundamentally different from running them on an Azure VM<\/strong>. ADF provides a fully managed environment with automatic scaling, built-in scheduling, and native integration with other Azure services, while VMs offer more control over the operating system and custom configurations.<\/p>\n For a deeper comparison of these two approaches, refer to our detailed guide here: Now let’s look at how to create your first Azure Data Factory Instance and then configure to run SSIS Packages with Custom Components such as SSIS PowerPack<\/a>.<\/p>\n Before we move forward with our steps to Deploy and Run SSIS Package in Azure Data Factory let’s confirm that you meet\u00a0the following prerequisite.<\/p>\n Using your valid Azure Subscription create new Azure Data Factory Instance (Thi will host your SSIS Runtime). Click here to learn more how to create a new Azure Data Factory Instance<\/a>.<\/p>\n Once you have a valid ADF Instance, next step is to make sure you have a valid Azure SQL Server Instance to host SSISDB (Catalog Database for SSIS). If possible choose the same region for both ADF and SQL Instance.\u00a0). Click here to learn more on how to create a new Azure SQL Server Instance<\/a>.<\/p>\n Once you have provisioned ADF instance and Azure SQL Server instance, Now it’s time to prepare setup files for custom SSIS components. Behind the scene, ADF uses VM instances to run your SSIS Packages so you can install pretty much anything you want just like a regular VM. However, there is no Remote Desktop Option for SSIS node in ADF, in other words, it’s pretty locked down. The only way you can install custom components is through Custom Setup Option provided during your SSIS Runtime creation. This custom script is executed only once after SSIS VM is created in ADF. It also executed once you stop and start SSIS Runtime. You have no way to execute the custom script on demand while SSIS Runtime is running (Maybe Microsoft can change this in the future but no option for now).<\/p>\n During SSIS Runtime setup you can instruct ADF to perform a custom setup (e.g. Run the custom installer, copy files, execute PowerShell scripts etc). Your custom setup files are downloaded in the SSIS node (i.e. VM) at some point during SSIS Runtime setup. This is done using SAS URL<\/strong> (i.e. URL with Shared Access Signature). Treat SAS URL like a Password if you have some sensitive data in your container. For an additional read on the custom component setup, you can refer this link<\/a>.<\/p>\n Before we create SAS URL, you need to gather setup files on your local disk and then you can upload to Azure Blob Container. SSIS PowerPack needs the following 2 files to be uploaded in your blob container (root level)<\/strong>.<\/p>\n Each file listed above has download link next to it so download locally (except main.cmd<\/strong> which we will discuss next).<\/p>\n Create a\u00a0new file and name it as “main.cmd<\/strong>“. Enter content like below.<\/p>\n Few things you should change as per your need<\/p>\n Other than the above three changes you can leave everything default. Save the file and upload to your setup blob container. Once ADF executes main.cmd it will generate new Log folder in your Blob container (You will see a new folder. Example\u00a0 main.cmd.log\\tvm-325613345_1-20180503t144850z##2018-05-03T145334Z<\/span>\u00a0 ).<\/p>\n See below example script file which you have to create if you are a trial user (you don’t have a license key). The file must be named as main.cmd.<\/p>\n See below example script file which you have to create if you are a paid customer and wish to apply license key. The file must be named as main.cmd. Make sure:<\/p>\n Once you have prepared setup files now let’s upload them to your blob container and generate SAS URL using the\u00a0below steps.<\/p>\n Connect to Azure Storage Account in Azure Storage Explorer<\/p><\/div>\n \u00a0<\/strong><\/li>\n Create new Azure Blob Container in Azure Storage Explorer<\/p><\/div><\/li>\n Upload Custom Setup File for Azure Data Factory – SSIS Runtime<\/p><\/div><\/li>\n Create SAS URL in Azure Storage Explorer for Azure Data Factory (ADF) – SSIS Runtime Custom Setup (3rd party component installation)<\/p><\/div>\n Generate SAS URL from Azure Portal Online<\/strong><\/p>\n Create SAS URL in Azure Portal for Azure Data Factory (ADF) – SSIS Runtime Custom Setup (3rd party component installation)<\/p><\/div>\n <\/li>\n Get SAS URL for Azure SSIS Runtime – Custom setup<\/p><\/div><\/li>\n<\/ol>\n <\/p>\n <\/p>\n Once you upload setup files for custom SSIS components and generated SAS URL for container we are ready to move on to our final step (Create SSIS Integration Runtime in Azure Data Factory)<\/p>\n Steps for Creating SSIS Runtime in Azure<\/a> are clearly documented here except one change (see below).<\/p>\n <\/p>\n Follow all steps as per this Microsoft Tutorial<\/a>\u00a0(Skip creating ADF step if you already did in the previous section). However\u00a0Microsoft Tutorial does not mention about Custom SAS URL<\/strong> (again this is still in preview so screenshot may update later). However In the\u00a0Advanced Settings<\/strong>\u00a0page during SSIS Runtime Setup (see below), Where you set\u00a0Maximum Parallel Executions Per Node you will have to enter Custom Setup URL<\/strong>. Enter SAS URL we generated in the previous section. <\/div><\/div>\n Enter Custom Setup SAS URL during SSIS RUntime Creation in Azure Data Factory<\/p><\/div>\n Once your SSIS Runtime setup is done you can see your Node running in Dashboard (Status must be Green checkbox with\u00a0\u00a0Running<\/strong> ).<\/p>\n Verify SSIS Runtime Node Status in Azure Data Factory Portal<\/p><\/div>\n <\/p>\n There will be a time when you no longer use ADF instance or you like to use your license on a different ADF Instance. As of now there is no Customscript option for Stop action in ADF so perform the following work around to fire Unregister License command. Basically you have to restart your IR (the one which is holding your license) once to fire custom script which should contain license register action first and then unregister command right after it.<\/p>\n Here is full script to unregister your license. Make sure to use this script for old ADF instance which is being deleted or from where you like to unregister ZappySys license .<\/p>\n Use below code in main.cmd<\/strong><\/p>\n <\/p>\n Once your SSIS Runtime is set up and running your next step is to Author and Deploy SSIS Packages.<\/p>\n Before you deploy package to ADF you must change to to the supported Runtime by ADF. As of now 2017 (i.e. SQL Server 2017) is supported runtime so choose that one.<\/p>\n Change SSIS TargetServerVersion Property<\/p><\/div>\n If you are using latest version of Visual Studio (VS2017 or Higher<\/strong>) then you can also deploy SSIS Project right from Visual Studio (See below).<\/p>\n Deploy SSIS Packages to Azure Data Factory from Visual Studio 2017 \/ 2019<\/p><\/div>\n Now let’s talk how to deploy SSIS Packages from your local machine to SSISDB in Azure. The process is pretty same just like how you do when deploying SSIS to On-Premises SQL Server Instance except for few steps.<\/p>\n Other than those two things all steps to deploy SSIS your packages to Azure Data Factory are listed here<\/a>.<\/p>\n Here are the steps to connect to SSISDB Catalog for ADF SSIS-IR<\/p>\n Connect to Database from SSMS<\/p><\/div>\n Connect to Azure Data Factory – SSISDB via SSMS 17.x Or Higher ( Deploy \/ Monitor SSIS Packages)<\/p><\/div>\n <\/p>\n Deploy SSIS Package to Azure Data Factory using SSMS UI (Wizard mode)<\/p><\/div>\n To execute SSIS Package via SSMS UI you can use below steps.<\/p>\n Execute SSIS Package using SSMS UI<\/p><\/div><\/li>\n Monitor SSIS Package Execution using SSMS UI<\/p><\/div><\/li>\n<\/ol>\n <\/p>\n In Azure there is no SQL Agent like feature we have for on-premises SQL Server so how do you schedule packages when you deploy to ADF ? Well there is slightly different approach in ADF. You need to use feature called Trigger. Check this article to learn how to schedule SSIS Package in Azure (Use Trigger)<\/a><\/p>\n Here are few more approaches to to schedule SSIS Packages deployed in Azure Data Factory<\/a><\/p>\n If you want to schedule SSIS Package stored in Azure Data Factory on your On-Premises server then you can use Linked Server approach mentioned in the next section. You can create a new T-SQL Job Step and call a user-defined stored procedure which can trigger SSIS package execution as per next T-SQL code.<\/p>\n <\/p>\n In a real-world scenario, you have to execute SSIS Packages in an automated way from code or trigger execution on certain schedule. Microsoft has documented this scenario here<\/a> but below section will add few more details which you may find very useful. Basically, if you wish to call or schedule Azure Data Factory SSIS packages from your remote server job (i.e .Calling SSIS from On-Premises Agent Job via Job > Job Step > Integration Services Step ) then there are few caveats you should know. SQL Server Agent Job doesn’t allow Mixed Mode Authentication for SSISDB to call SSIS Packages (If you check Job Step UI in SQL Agent supports only Windows Authentication).<\/p>\n Well, not to worry solution is not too hard. There is an easy way you can trigger remote SSIS package execution<\/strong> by using Linked Server<\/strong>\u00a0approach\u00a0<\/strong>(i.e .Call SSIS Packages stored in Azure Data Factory SSISDB).<\/p>\n So let’s look at how to execute and monitor ADF SSIS Packages using Linked Server.<\/p>\n To \n
\n
\n
Advantages of running SSIS on Azure Data Factory<\/h2>\n
\n
How to deploy and run SSIS Packages in Azure with 3rd party components<\/h2>\n
\n
Important things to know about SSIS Runtime in ADF<\/h2>\n
\n
Choosing Between Azure Virtual Machine (Azure VM) and Azure Data Factory (ADF) for Running SSIS Packages<\/h2>\n
\nChoosing Between Azure Virtual Machine (Azure VM) and Azure Data Factory (ADF) for Running SSIS Packages<\/a><\/p>\nGetting Started – Setup SSIS Runtime in Azure<\/h2>\n
Prerequisite<\/h3>\n
\n
Create a new Azure Data Factory (ADF)<\/h3>\n
Create a new Azure SQL Database (i.e. new SQL Instance)<\/h3>\n
Create and Setup Azure Blob Storage Container (Only for 3rd party SSIS setup)<\/h3>\n
Prepare custom setup files for ADF – SSIS Runtime<\/h3>\n
\n
\n
main.cmd – For Trial Users<\/h4>\n
set DIR=%CUSTOM_SETUP_SCRIPT_LOG_DIR%\r\n\r\necho Calling Step 1 : %TIME% >> \"%DIR%\\steps_log.txt\"\r\ndir \/s \/b > \"%DIR%\\file_list.txt\"\r\n\r\necho Calling Step 2 : %TIME% >> \"%DIR%\\steps_log.txt\"\r\n\r\n::Install SSIS PowerPack\r\nmsiexec \/i \"SSISPowerPackSetup_64bit_Trial.msi\" ADDLOCAL=ALL \/q \/L*V \"%DIR%\\powerpack_trial_install_log.txt\"\r\n\r\necho Calling Step 3 : %TIME% >> \"%DIR%\\steps_log.txt\"\r\n\r\n::Activate SSIS PowerPack Trial Extension Key (Optional) -- Uncomment below line to apply the trial extension key.\r\n::\"C:\\Program Files (x86)\\ZappySys\\SSIS PowerPack (64 bit)\\LicenseManager.exe\" -p SSISPowerPack --register \"lgGAAO0-----REPLACE-WITH-YOUR-TRIAL-EXTENSION-KEY-----czM=\" --logfile \"%DIR%\\powerpack_register_log.txt\"\r\n\r\ndir \"C:\\Program Files\\Microsoft SQL Server\\*Zappy*.*\" \/s \/b >> \"%DIR%\\installed_files.txt\"\r\ndir \"C:\\Program Files (x86)\\Microsoft SQL Server\\*Zappy*.*\" \/s \/b >> \"%DIR%\\installed_files.txt\"\r\n\r\necho DONE : %TIME% >> \"%DIR%\\steps_log.txt\"\r\n\r\necho complete<\/pre>\n
main.cmd – For Paid Customers<\/h4>\n
\n
set DIR=%CUSTOM_SETUP_SCRIPT_LOG_DIR%\r\n\r\necho Calling Step 1 : %TIME% >> \"%DIR%\\steps_log.txt\"\r\ndir \/s \/b > \"%DIR%\\file_list.txt\"\r\n\r\necho Calling Step 2 : %TIME% >> \"%DIR%\\steps_log.txt\"\r\n\r\n::Install SSIS PowerPack\r\nmsiexec \/i \"SSISPowerPackSetup_64bit.msi\" ADDLOCAL=ALL \/q \/L*V \"%DIR%\\powerpack_install_log.txt\"\r\n\r\necho Calling Step 3 : %TIME% >> \"%DIR%\\steps_log.txt\"\r\n\r\n::Activate PowerPack license (Optional)\r\n\"C:\\Program Files (x86)\\ZappySys\\SSIS PowerPack (64 bit)\\LicenseManager.exe\" -p SSISPowerPack --register \"lgGAAO0-----REPLACE-WITH-YOUR-LICENSE-KEY-----czM=\" --logfile \"%DIR%\\powerpack_register_log.txt\"\r\n\r\n::De-Activate license (Optional) -- Uncomment below line to Unregister previously used license. Must register first and then unregister\r\n::\"C:\\Program Files (x86)\\ZappySys\\SSIS PowerPack (64 bit)\\LicenseManager.exe\" -p SSISPowerPack --unregister --logfile \"%DIR%\\powerpack_un_register_log.txt\"\r\n\r\n\r\n::Show System Info\r\necho Calling Step 4 : %TIME% >> \"%DIR%\\steps_log.txt\"\r\n\"C:\\Program Files (x86)\\ZappySys\\SSIS PowerPack (64 bit)\\LicenseManager.exe\" -i -l \"%DIR%\\sysinfo_log.txt\"\r\n\r\necho Calling Step 5 : %TIME% >> \"%DIR%\\steps_log.txt\"\r\ndir \"C:\\Program Files\\Microsoft SQL Server\\*Zappy*.*\" \/s \/b >> \"%DIR%\\installed_files.txt\"\r\ndir \"C:\\Program Files (x86)\\Microsoft SQL Server\\*Zappy*.*\" \/s \/b >> \"%DIR%\\installed_files.txt\"\r\n\r\necho DONE : %TIME% >> \"%DIR%\\steps_log.txt\"\r\n\r\necho complete<\/pre>\n
Create SAS URL of Azure Blob Container (Only for 3rd party SSIS setup)<\/h3>\n
\n
<\/a><\/a><\/a>
\n<\/a><\/a><\/a>Create a new SSIS – Integration Runtime for SSIS<\/h3>\n
<\/a><\/a>Unregister \/ Move ZappySys License<\/h2>\n
set DIR=%CUSTOM_SETUP_SCRIPT_LOG_DIR%\r\n \r\necho Calling Step 1 : %TIME% >> \"%DIR%\\steps_log.txt\"\r\ndir \/s \/b > \"%DIR%\\file_list.txt\"\r\n \r\necho Calling Step 2 : %TIME% >> \"%DIR%\\steps_log.txt\"\r\n\r\n::Install SSIS PowerPack\r\nmsiexec \/i \"SSISPowerPackSetup_64bit.msi\" ADDLOCAL=ALL \/q \/L*V \"%DIR%\\powerpack_install_log.txt\"\r\n \r\necho Calling Step 3 : %TIME% >> \"%DIR%\\steps_log.txt\"\r\n \r\n::Activate PowerPack license\r\n\"C:\\Program Files (x86)\\ZappySys\\SSIS PowerPack (64 bit)\\LicenseManager.exe\" -p SSISPowerPack --register \"lgGAAO0QmCxxxxxxxxxxxxxxxxWSxczM=\" --logfile \"%DIR%\\powerpack_register_log.txt\"\r\n\r\n::De-Activate same license -- This is the work around to unregister license from ADF\r\n\"C:\\Program Files (x86)\\ZappySys\\SSIS PowerPack (64 bit)\\LicenseManager.exe\" -p SSISPowerPack --unregister --logfile \"%DIR%\\powerpack_un_register_log.txt\"\r\n \r\n::Show System Info\r\necho Calling Step 4 : %TIME% >> \"%DIR%\\steps_log.txt\"\r\n\"C:\\Program Files (x86)\\ZappySys\\SSIS PowerPack (64 bit)\\LicenseManager.exe\" -i -l \"%DIR%\\sysinfo_log.txt\"\r\n \r\necho Calling Step 5 : %TIME% >> \"%DIR%\\steps_log.txt\"\r\ndir \"C:\\Program Files\\Microsoft SQL Server\\*Zappy*.*\" \/s \/b >> \"%DIR%\\installed_files.txt\"\r\ndir \"C:\\Program Files (x86)\\Microsoft SQL Server\\*Zappy*.*\" \/s \/b >> \"%DIR%\\installed_files.txt\"\r\n \r\necho DONE : %TIME% >> \"%DIR%\\steps_log.txt\"\r\n \r\necho complete<\/pre>\n
Author SSIS Packages to Run in Azure Datafactory<\/h2>\n
\n
Change SSIS Project TargetServerVersion<\/h2>\n
\n
<\/a>Using Visual Studio to Deploy SSIS Packages to Azure (i.e. ADF SSIS-IR)<\/h2>\n
<\/a>Using SSMS to Deploy \/ Execute SSIS Packages to Azure (i.e. ADF SSIS-IR)<\/h2>\n
\n
\n
<\/a><\/a><\/a>Test \/ Execute and Monitor SSIS Package via SSMS UI<\/h2>\n
\n
<\/a><\/a><\/h2>\n
Schedule SSIS Package in Azure Data Factory<\/h2>\n
Execute and Monitor SSIS Package via T-SQL Code in Azure Data Factory<\/h2>\n
Create a Linked server for SSISDB (Azure SQL DB)<\/h3>\n