Using SSIS with Microsoft Dynamics Mobile

  • Article

Let me start with a small announcement

I am happy to announce that we have upgraded our SSIS (SQL Server Integration Services) solution for Mobile Sales from the 2005 to the 2008 version of Microsoft SQL Server Integration Services. So from now on we will internally work with the 2008 version which will give us some new features.

Side note: Please notice that I am talking here about upgrade which has not been released yet. In case you have your own SSIS solution you may wait for our next release - however I encourage you to perform your own upgrade. Upgrading of SSIS is a straightforward and not time consuming process - just follow this link.

Posting plan

My plan is to give you a small introduction why and how we use SSIS (today’s posting) and to publish few SSIS tips and tricks (next postings). Of course I will also take into consideration all your comments / questions / requests / recommendations.

General introduction

If you are completely new to SSIS please read this short introduction or go for details to the official site.

SSIS role within the Microsoft Dynamics Mobile architecture

One of the questions solved by Microsoft Dynamics Mobile architecture is which business related data should be displayed on the mobile device and how to get them. Our current solution uses three different database types (Resource, Staging and Reference) and two different technologies of transforming data between these databases. SSIS as the first mentioned technology takes care about the data transfer from Resource (also called Backend or Business) Database to the temporary database called Staging Database. The next logical step – passing and filtering user related data from the Staging to the Reference (also called Device) Database located on the mobile device – is based on a different technology called SQL Merge Replication and its description is not part of this article.

I am going to describe only very particular piece of the Microsoft Dynamics Mobile architecture - see the highlighted parts of the picture below.

When we are talking about the data transfer from Resource to the Staging Database proceeded by the SSIS package we can describe it as an Extract, Transform and Load principle. During one SSIS iteration the data are not only being extracted from the Resource Database, but also converted to more consistent data types or transformed to more meaningful units to fit better the business needs displayed later on the mobile device. Finally the extracted and transformed data are being loaded to the Staging Database.

Typically SSIS package is installed as an SQL Server job scheduled to run every few minutes, so you can understand the Staging Database as an always-up-to-date simplified copy of the Resource Database.

Currently our SSIS solution contains three SSIS packages: Mobile Sales-AX4-Package, Mobile Sales-NAV4-Package and Mobile Sales-NAV5-Package supporting Microsoft Dynamics AX 4.0 SP1 and SP2, Microsoft Dynamics NAV 4.0 SP3 and Microsoft Dynamics NAV 5.0 SP1 backend solutions.

You can see that sometimes one package can cover two versions of one backend solution (as for AX 4.0 SP1 and SP2); sometimes two different packages are needed (as for NAV 4.0 and 5.0). This of course depends on how many and how complex changes have been introduced in the newer backend solution.

Sometimes different backend solutions could share the same structure of the Staging Database (e.g. AX and NAV), which basically means that the same set of features could be built on the mobile client. Other time special version of the Staging Database is required – mainly because of set of features supported by the particular backend solution.

General introduction to the SSIS development experience

SQL Server Business Intelligence Development Studio (BIDS) is the tool you want to use when creating new or modifying existing SSIS packages. This IDE is based on Visual Studio and comes together with the SQL Server 2005 or 2008 respectively.

Introduction to the Mobile Dynamics SSIS development experience

The different versions of the packages differ significantly in e.g. the way how they handle switching between different companies; however it is pretty easy to assemble the generic description how the packages do their works.

Each package consists of several so called Data Flow Tasks - sequence of these tasks is being defined in the Control Flow view. Please have a look at the simplified version of the Control Flow diagram below:

Simplified version of the BIDS Control Flow view

Typically there is a one-to-one relationship between Staging Database tables and Data Flow tasks. So for example Customer flow task corresponds to the Customer table in the Staging Database and takes care about data transformation related to this particular table. If you double-click one of the tasks the environment will switch to the Data Flow view.

Here is the generalized version of one Data Flow task demonstrating the general pattern how these are built:

Generalized version of the BIDS Data Flow view

Extraction – This is in fact only a wrapper for the SQL select statements – one for the Backend and the other one for the Staging Database.

Data Conversion – Just extracted data could be converted to different data types - e.g. to different numeric types, to Unicode strings, to strings with a specific length etc.

Sorting – Data must be sorted before Merge Join. This step could be avoided by using ORDER BY clause within the extraction SQL statement and setting IsSorted property to true. Much better performance could be gained by such a shift. However - be careful - the sorting step may be avoided only if you are absolutely sure that the reference and staging databases are using the same sorting methods.

Merge Join – Probably the most complicated part which deserves a separate article. Just notice it must be Full Outer Join for now.

Conditional Split – Based on the previous Full Outer Join we can specify three conditions which decide whether the data row coming from the Backend Database should be inserted to the Staging Database (New rows); or already existing data row should be modified (Modified rows); or data row exists in the Staging Database but not (anymore) in the Backend Database and in this case it should be deleted from the Staging Database.

Loading – Performs the operations decided in the previous step using the correct data mappings.

This must be enough for today :)

As mentioned at the beginning I will return to this topic giving more detailed description of selected parts and publishing some small and useful tips & tricks.

Comments

  • Anonymous
    June 15, 2008
    You wrote: "Sorting – Data must be sorted before Merge Join. This step could be avoided by using ORDER BY clause within the extraction SQL statement and setting IsSorted property to true. Much better performance could be gained by such a shift." The sorting cannot be avoided if your reference and staging databases are not using the same sorting methods. Example: Extracting data from an AX Oracle refrence DB using ORDER BY will not necessarily result in the same order as the ORDER BY on the staging DB. And this will give you some strange results. So be careful before you remove the sorting step.

  • Anonymous
    June 16, 2008
    hey TommyA, You are absolutely right, be very carefull when you remove any of the SSIS flow tasks. Data might not be passed on to the next flow step as you want it to. But it is important to have performance gains in mind when you are developing ETL packages. Furthermore we dont, at the moment, support Oracle scenarios, but watch out for the next releases. brgrds. Jesper Schaadt

  • Anonymous
    June 17, 2008
    Hi Tommy, Thanks a lot for your comment. I updated that article part with a warning regarding the same sorting methods. In case you are sure the sorting methods are working as expected the performance gain is significant. Jan

  • Anonymous
    June 18, 2008
    Parece que la cosa se mueve y de cara a la llegada de Dynamics 2009 el equipo de Microsoft ya se está