SSIS and CRM Series, Part 1 - Use SSIS to Run Scheduled Jobs for Microsoft Dynamics CRM

In CRM projects, we often have the requirements to run scheduled jobs, on either daily, monthly basis (or maybe even every few minutes), to implement certain business logics. For instance, one of the typical scenarios is a business entity has an expiry date, and you want to deactivate all records that have passed the expiry date in the system. The challenge with Microsof Dynamics CRM is, there is currently no out-of-box support of scheduled jobs.

With the availability of SSIS Integration Toolkit for Microsoft Dynamics CRM, you can leverage SQL Server scheduling capability to implement this kind of scheduled jobs very quickly.

In this blog post, I will talk you through the process of implementing a scheduled SSIS job that runs against Microsoft Dynamics CRM. The business scenario of the implementation is, we have a CRM entity called Legislation, which has a physical name of new_legislation (This is just a make-up sample, I couldn't find a better sample at this moment) in the system. Legislation entity has a field called Expiry Date, with new_expirydate as its physical name. What we try to achieve in this blog post is, the SSIS job runs once a day at off-peak hour to deactivate all Legislation records in the system that has an Expiry Date of today.

Let's get started.

Creating SSIS Project and Package

1. First of all, we need to ensure we have an SSIS development environment with SSIS Integration Toolkit for Microsoft Dynamics CRM installed. The SSIS development environment could be a SQL Server 2008, SQL Server 2008 R2, or SQL Server 2012 development machine that has Business Intelligence Development Studio (BIDS) installed. 
2. We would then create an SSIS project after we have launched Business Intelligence Development Studio (or Visual Studio if SQL Server 2012 is used).
   
3. In the SSIS project, we will need to add a new package by selecting "New SSIS Package" option after right-clicking SSIS Packages in Visual Studio Solution Explorer.
   

Creating CRM Connection Manager and a SSIS Data Flow Task

After we have created a SSIS package, we would then need to create a connection manager which facilitates the connection with Microsoft Dynamics CRM server.

1. Within the SSIS Package that we created above, right-click in Connection Manager area, and choose "New Connection".
    
2. We will be then prompted to select a connection manager from a list of all available connection managers in the system, including those provided by Microsoft and third-party software vendors. Choose "DynamicsCRM" from the list.
   
3. We would then fill in the necessary information in order to be able to connect to our CRM server.
   
    

Creating a Data Flow Task

Within SSIS package, we can see four tabs. Control Flow and Data Flow are two of them where we would spend most of time when developing our ETL jobs (CRM scheduled jobs in our case here). As indicated by the names, SSIS Control Flow manages and controls the ETL tasks within the SSIS package, which can be made of various data flow tasks and some other ETL tasks. Within Data Flow task, we can design the flow of our data from one or more data sources to a destination.

Creating a data flow task is fairly simple, we can drag Data Flow Task from toolbox and drop it to the design surface of SSIS Control Flow. 

Adding a CRM Source Component

After we have created a data flow task, we would then double-click the task to switch to its design surface, in which case, we will be switched to Data Flow view. Now we need to create a CRM source component within the data flow task so that we can read records from CRM system that we need to perform the scheduled manipulation. 

1. In order to add CRM source component to data flow, we need first add it to SSIS toolbox. To do so, we right-click on the toolbox area to bring up the context menu, where we select "Choose Items..." option as shown below.
   
2. We would now be presented with a window called "Choose Toolbox Items", switch to "SSIS Data Flow Items" tab, and find Dynamics CRM Destination and Dynamics CRM Source components from the list, and make sure to check both of them (Dynamics CRM Destination component will be used next).
   
3. Then we can add a Dynamics CRM Source component by dragging it from SSIS toolbox and dropping it to the data flow design surface. 
4. In CRM Source Component, we need to choose a CRM connection manager that we have previously created. Also we would need to specify how we read data from CRM. We can choose to read data from a CRM entity, or we can provide a FetchXML query to read data from CRM. In our case, we will use FetchXML option so that we can read the only CRM records that we need to process.
   

In the above sample, we have tried to use a simple FetchXML query, which reads all CRM Legislation records that are active (statecode = 0) and have an Expiry Date of today.

SSIS Integration Toolkit for Microsoft Dynamics CRM supports any complex FetchXML query, and also you can use SSIS variables to parameterize your query, which gives you all the flexibility. 

Adding Processing Logic Using SSIS Transformation Components

After we have created the CRM source component, the next thing we need to do is to make changes to the CRM records in order to deactivate them. SSIS provides a number of transformation components that help transform and manipulate data. In our case, we will use Derived Columns transformation component to achieve our purpose. 

1. Drag Derived Column component from SSIS toolbox and drop it to Data Flow design surface.
2. Connect the Derived Column component to the CRM source component we previously created in the same data flow task.
3. Double click the Derived Column component to open its editing window.
4. In Derived Column Transformation Editor window, choose "Replace 'statecode'" in the grid, and enter 1 as expression (for most CRM entities, setting statecode to 1 is to deactivate the record). The component should look like the following screen.
   

After this step, we will have a data flow like the following screen. 

As mentioned, SSIS has a lot more transformation components that can be used to implement different business logics. We have tried to keep the sample simple. Even the Derived Column transformation component can be used in many different ways. 

Adding a CRM Destination Component

The final thing to do to finish the data flow development is to add a CRM destination component to the data flow. 

1. Drag Dynamics CRM Destination component from SSIS toolbox and drop it to Data Flow design surface.
2. Connect the CRM destination component to Derived Column component that we created in previous step.
3. Double click the CRM Destination component to open its editing window.
4. Choose the CRM connection manager that we previously created, select Update as the Action option. Then select "new_lesgislation" as the Destination Entity.
   

After this is done, our data flow development is finished. The entire development shouldn't take you more than a few minutes, if you have been acquainted with SSIS. So you will have a data flow like the following screen. 

Schedule SSIS Package

After we have developed the SSIS package, the next thing we need to do is schedule it using SQL Server. This should be typically handled by DB team.

SQL Server has great scheduling capability so that we can run tasks at scheduled time. I will not cover all the details about how to setup such scheduled jobs in SQL Server, which is a pretty straight-forward process using SQL Server Agents, and in most cases are handled by your DBA guys. The following screen shows the available scheduling options that you can use to run SSIS packages. 

After your DB team has setup the scheduled tasks for you, you can be rest assured that the business logics that you have implemented in SSIS packages will be fired based on the schedule that you have defined. 

Final Notes

1. In the community, we often talk about scheduled CRM Workflow, which is not a feature available today. With the availability of SSIS Integration Toolkit, you can implement almost any such kind of business logics using SSIS. 
2. As a disclaimer, SSIS Integration Toolkit is a commercial product, and I work for KingswaySoft, the provider of the tool. 

Thanks for reading, hope this helps.