Real-time Dynamics 365 data integrations with SSIS + KingswaySoft

by Lucas Alexander

I'm a big fan of KingswaySoft's Dynamics 365 SSIS integration toolkit for moving data between Dynamics 365 and other systems. Because the integrations are run in SSIS packages, there's a lot of power and flexibility, and you can use SQL Server Agent to orchestrate your integration jobs. This is great for scheduled jobs, but what do you do if you need to trigger a job based on activity in Dynamics 365? In today's post, I will describe an approach that you can use to solve this problem.

Starting with SQL Server 2012, the SSIS catalog database includes a set of stored procedures that can be used to execute SSIS packages from T-SQL. Here's an example of how to call them:

--create variable to hold execution id
Declare @execution_id bigint  

--create execution to run later
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'MyPackage.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'MyPackageFolder', @project_name=N'MyPackageProject', @use32bitruntime=False, @reference_id=Null  

--populate any input parameters
DECLARE @var0 sql_variant = N'Some input parameter value'  
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'Parameter1', @parameter_value=@var0  

--execute the package
EXEC [SSISDB].[catalog].[start_execution] @execution_id  
GO

I am using a Dynamics 365 plug-in to execute the SSIS packages, but you could just as easily use a custom workflow activity. One other advantage to using the plug-in is that I can access the pre/post-operation images and parse them for data to pass to my SSIS package.

I also created a wrapper stored procedure in a separate database to run the T-SQL above just to streamline the code in my plug-in.

There are a couple of issues with this approach:

  1. Calling SQL stored procedures is not supported in an isolated plug-in, so you can't use this approach in a Dynamics 365 online deployment, and your infrastructure team will need to be OK with running plug-ins outside the sandbox.
  2. Calling operations in the SSIS catalog database requires Windows authentication instead of SQL Server authentication, but you can't supply specific Windows credentials from a plug-in when you execute the SQL stored procedure (you can only use integrated security). The only way I could make this work was to grant permission for the service account running CRM (NT AUTHORITY\NETWORK SERVICE on my development VM) rights to execute the wrapper stored procedure and to access the SSIS catalog database.

If either of these issues are a showstopper for you, an alternate approach would be to build an intermediate web service that can call the SQL stored procedure to execute the SSIS packages. You can then call this web service from a sandboxed plug-in or custom workflow activity.

What do you think about this approach? Have you done something different to solve a similar integration challenge? Please let us know in the comments!