Saturday, 14 April 2018

New ADF Pipeline activity: Execute SSIS Package

Case
Microsoft released a new ADF Pipeline activity today: Execute SSIS Package. How does it work and is it easier/better than the trick with the Stored Procedure Activity?




















Solution
The new activity can be found under General (just like the Stored Procedure) and it is indeed much easier than the Stored Procedure activity solution. If you want to execute the  package below then follow the steps below.
The package which I want to execute












1) Add activity 
Drag the new SSIS activity to the canvas of the pipeline and give it a describing name. For example something with the projectname of package name in it.
Execute SSIS Package activity

















2) Settings
Go to the Settings tab and first select the name of the Integration Runtime that should execute the package. The second mandatory setting is the Logging Level, but it already has a default setting for 'Basic' and the last mandatory setting is the package path. The path starts with the catalog folder name followed by a forward slash, the project name, an other forward slash and then finally the package name. It shoud look like this: ssisjoost/MyAzureProject/Package.dtsx
Settings

















3) Run Trigger
Now run publish the new pipline and run the trigger to see the result.

Possible errors:
When the Integration Runtime is not running, it shows "Activity Execute my first package failed: The integration runtime 'IR-SSISJoost' under data factory 'ADF-SSISJoost' does not exist.". This message is a bit strange.

When the Integration Runtime is starting up, it shows "Activity Execute my first package failed: The state of Azure ssis integration runtime 'IR-SSISJoost' under data factory 'ADF-SSISJoost' is not ready."

When the package is failing it shows "Execute my first package failed: Package execution failed.". This shows the shortcoming compared to the Stores Procedure activity which allows you to show the Execution Id and even the error messages from the catalog if you fancy a bit of T-SQL scripting.

Conclusion
Much easier, but in case of package errors not very helpful. It forces you to search for errors in the catalog. If you are using the ADF monitor to the check for errors I would probably still prefer the Stores Procedure activity.









No comments:

Post a Comment

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.