In this second article on building Microsoft MEAP, I’ll focus on implementing Gartner’s Enterprise Application Integration Tools critical capability using SQL Server Integration Services (SSIS) to connect to back end systems.

As I mentioned in the Introduction article, one of the top priorities for CIOs today is extending critical data from their backend systems out to the wireless devices used by employees.  This can often be easier said than done.  If your backend ERP, CRM, and other bespoke systems provide efficient, resilient, wireless-friendly connectivity and mobile client apps for smartphones, tablets, and laptops, then you’re in good shape.  Similarly, if your organization has spent the last decade building a mature Service Oriented Architecture (SOA) to expose your backend data sources, then your mobile devices have a way to consume that composite data.  Of course, you need to migrate those bloated SOAP + XML web services to something lighter and faster like REST + JSON.  On the other hand, if your organization is deficient in some of these areas, you need a Mobile Enterprise Application Platform (MEAP) with the adapters needed to connect those backend systems and data sources to Mobile Middleware.

As a recap, let’s take a look at the Gartner critical capabilities that pertain to backend adapters and the tooling needed to make those connections:

Enterprise Application Integration Tools:

  • Gartner Definition:  Tools for integration of mobile server with back end systems, both bespoke & purchased apps or application suites.
  • Microsoft Offering: SQL Server Integration Services (SSIS), Visual Studio SQL Server Data Tools.
  • Value Proposition:  Developers visually compose connections, actions, events and data movement rather than writing separate sets of integration code.  Adapters provide consistent connectivity to dozens of backend systems and data sources.  Microsoft is providing unrivaled, easy to use, drag and drop tools to connect ETL adapters with backend systems and databases.
Integrated Development  Environment:

  • Gartner Definition: Dedicated environment or plug-in for composing backend server & client side logic, including UI.
  • Microsoft Offering: Visual Studio
  • Value Proposition:  As the world’s most widely-used commercial IDE, you’re more likely to find plenty of proficient developers than with any other MEAP offering.  Additionally, developers are more productive since they don’t have to use different or specialized tools to target laptops, tablets, smartphones, servers, or the cloud.  Competing MEAP vendors have unfamiliar native and hybrid SDKs or 4GLs while Microsoft has millions of seasoned developers.
The key takeaway here is that Microsoft provides easy to use Enterprise Application Integration (EAI) Tooling in the form of SQL Server Data Tools in Visual Studio (IDE) and adapter technology in the form of SSIS.  This allows you to pull composite data into SQL Server (Mobile Middleware) for aggregation.  Keep in mind that the value of the EAI technology found in a MEAP vendor’s offering is derived from the following:
  • Must be easy to use and should connect to multiple backend systems in a consistent way.  In other words, if you have to connect to 20 different systems and you’re required to write unique code or connect 20 different ways then your MEAP package has failed.  Anyone can find a way to integrate with any system.  Doing so elegantly and consistently so that your people only have to be trained once is what you’re paying for.  Microsoft provides visual drag and drop tooling to make this task as simple as possible.
  • The more backend packages and data sources you can connect to the better.  It goes without saying that if your MEAP package can only connect to a handful of backend systems, if won’t be very valuable to your enterprise.  That being said, stay on the lookout for MEAP vendors that provide an extensive list of backend systems they can connect to – but connect to all of them via widely different methods.  SSIS can access data from any heterogeneous data source, package, message bus or interface including:
    • Database Systems: Oracle, Teradata, IBM DB2, SQL Server, MySQL, SQL Server Compact, Sybase, Access, PostgreSQL, Informix, FoxPro, Ingres, VSAM, IMS, LDAP, Lotus Notes, ADABAS, ADO, ADO.NET, ODBC, OLEDB (All databases)
    • Packages: SAP, Siebel, Dynamics, Hyperion, Salesforce, SharePoint
    • HTTP (Web Services), FTP, SMTP
    • File, Flatfile, Excel, EDI, XML
    • MSMQ, IBM MQ Series,Tibco Rendezvous, WebSphere, webMethods, SeeBeyond
  • The speed with which the data moves between backend packages and data sources and your SQL Server Mobile Middleware is critical.  Being able to interface your MEAP package with backend systems won’t be good enough if it can’t meet corporate performance SLAs.  Business operations in today’s real-time enterprise move at the speed of light and your MEAP package must do the same.  Luckily, Microsoft is ahead of the pack in this regard with its in-memory solution since it holds the world ETL record for moving in excess of 2 TB of data per hour (650+ MB/second).  It should come as no surprise that SSIS is depended on by more customers than any other ETL solution in the world.
  • Last but certainly not least, since Gartner requires security at every tier of any MEAP solution, EAI data movement between the Mobile Middleware server and backend systems must also be secure.  Microsoft provides the ability to password protect and encrypt all SSIS packages.  Furthermore, once your composite data is aggregated inside SQL Server, it is encrypted at rest.
Now that you know the facts about Gartner’s EAI critical capability and what to expect from Microsoft and other MEAP vendors, it’s time to make things real.  Theory is great, but seeing something in action is better and much more believable.  To keep things simple, I’ll build the EAI critical capability of Microsoft MEAP on my Windows 8 laptop.  I’ve got SQL Server 2012 installed and I’ll use 3 Access databases to represent a backend CRM, ERP, and mainframe.  I figured you’d be more likely to reproduce my examples on your own PC using Access than if I chose to connect to Microsoft Dynamics CRM and SAP.

CRM

To represent customers you might find in a CRM system, I’ve created an Access database with a simple Customers table with a schema that includes Id and Name:

I’ve filled the table with a short list of customers that we’ll use:

I’ve also created a Customers table in SQL Server to serve as the data destination for the CRM Access database.

ERP

To represent products you might find in an ERP system, I’ve created an Access database with a simple Products table with a schema that includes Id, Name, and Quantity:

I’ve filled the table with a short list of products that we’ll use:

I’ve also created a Products table in SQL Server to serve as the data destination for the ERP Access database.

Mainframe

To represent orders you might submit to a mainframe, I’ve created an Access database with a simple Orders table with a schema that includes Id, CustomerId, ProductId, and Quantity:

Since the mainframe is the destination after a mobile transaction is completed, the Orders table is currently empty.  As you might imagine, I’ve created an Orders table in SQL Server to serve as the data source for the Mainframe Access database.

Now it’s time to get started building the SSIS package to perform the data movement to and from our Mobile Middleware.  Since I have SQL Server 2012 installed, all I need to do is launch SQL Server Data Tools and create a new Integration Services Project.  I called my Solution “Adapters.”

In order to connect the 3 Access databases to SQL Server, you’ll need to create a few connections.  Go the bottom-center of the screen and right-click inside the Connection Managers tab area and select New Connection.  In the Add SSIS Connection Manager dialog, select OLEDB and click Add.

In the Configure OLE DB Connection Manager, click New.  In the Connection Manager dialog, select the SQL Server Native Client as the Provider, the name of your server, the appropriate Windows or SQL Server Authentication credentials, the name of the database, and click Test Connection to ensure everything is correct.  If everything checks out okay, click OK twice.

Now it’s time to create Access connections.  Right-click again to create a new OLEDB connection to the CRM database.  Since I’m using Office 2013, I used the Office 15 Access Database Engine OLE DB Provider and pointed to the path on my laptop where my database file resides.  You might use a different Access driver depending what you have installed on your PC.  As before, test your connection and then repeat this process to create Connection Managers for the ERP and Mainframe databases.

Before we move on to create the Data Flows, I want you to view the Properties for the SSIS Package.  Earlier in the article, I mentioned how important it was to secure every tier of any MEAP solution and integration is no different.  If you scroll down to the ProtectionLevel property, you’ll see a variety of ways to encrypt your Package.

One other thing I need you to configure has to do with the use of 32-bit Access drivers in an SSIS system that expects to operate in 64-bit when debugging.  In the Solution Explorer I want you to right-click on Adapters and select Properties.  In the Adapters Property Pages dialog you need to expand Configuration Properties and select Debugging.  You then need to set Run64BitRuntime to False to get things working properly if you happen to be using a 32-bit version of Access on your PC.

Data will move between SQL Server and our 3 Access databases through the use of Data Flow Tasks.  I now want you to drag a Data Flow Task from the SSIS Toolbox and drop it on the open workspace area beneath the Control Flow tab.  Rename it to CRM Data Flow Task.

Double-click on the new CRM Data Flow Task and you will be taken to the Data Flow tab.  In the SSIS Toolbox, expand Other Sources and drag OLE DB Source on to the open workspace.  Double-click on it to bring up the OLE DB Source Editor.  Select your CRM database in the OLE DB connection manager combo box, select Table or View in the Data access mode combo box, and select Customers in the Name of the table or the view combo box and then click OK.  To complete the CRM connection to our Mobile Middleware, I want you to expand Other Destinations from the SSIS Toolbox and drag OLE DB Destination on to the open workspace.  Click on the original OLE DB Source and drag the blue arrow to make a connection with the OLE DB Destination.  Double-click on OLE DB Destination to bring up the OLE DB Destination Editor.  Select your SQL Server database in the OLE DB connection manager combo box, select Table or View in the Data access mode combo box, and select [dbo].[Customers] in the Name of the table or the view combo box.  Click on Mappings to ensure you have the appropriate linkages between the Available Input Columns and the Available Destination Columns.

If everything looks good, click OK.  At this point you should Save and Build your solution just to verify there’s no errors.  Now it’s time to test this Data Flow.  Click the Play button or hit F5 on your keyboard to try it out.

If your OLE DB Source and OLE DB Destination have green circles with check signs inside, then there’s a good chance your data transferred without issues.  The connecting arrow should display 5 rows.  The final check is to go into SQL Server Management Studio and refresh the Customers table to verify that the 5 customers made it from Access to SQL Server.

The 5 customers made it over so now it’s time to complete the other 2 connections.

Go back to the Control Flow tab and drag a Data Flow Task from the SSIS Toolbox and drop it on the open workspace area.  Rename it to ERP Data Flow Task.  Double-click on the new ERP Data Flow Task and you will be taken to the Data Flow tab.  In the SSIS Toolbox, expand Other Sources and drag OLE DB Source on to the open workspace.  Double-click on it to bring up the OLE DB Source Editor.  Select your ERP database in the OLE DB connection manager combo box, select Table or View in the Data access mode combo box, and select Products in the Name of the table or the view combo box and then click OK.  To complete the ERP connection to our Mobile Middleware, I want you to expand Other Destinations from the SSIS Toolbox and drag OLE DB Destination on to the open workspace.  Click on the original OLE DB Source and drag the blue arrow to make a connection with the OLE DB Destination.  Double-click on OLE DB Destination to bring up the OLE DB Destination Editor.  Select your SQL Server database in the OLE DB connection manager combo box, select Table or View in the Data access mode combo box, and select [dbo].[Products] in the Name of the table or the view combo box.  Click on Mappings to ensure you have the appropriate linkages between the Available Input Columns and the Available Destination Columns.  If everything looks good, click OK.  Save and Build your solution just to verify there’s no errors and then test this Data Flow.  Look for the green circles with check signs and look inside SQL Server Management Studio to ensure the Products made it over.

The only connection left to make is Orders so return to the Control Flow tab and drag a Data Flow Task from the SSIS Toolbox and drop it on the open workspace area.  Rename it to Orders Data Flow Task.  Double-click on the new Orders Data Flow Task and you will be taken to the Data Flow tab.  In the SSIS Toolbox, expand Other Sources and drag OLE DB Source on to the open workspace.  Double-click on it to bring up the OLE DB Source Editor.  Select your SQL Server database in the OLE DB connection manager combo box, select Table or View in the Data access mode combo box, and select [dbo].[Orders] in the Name of the table or the view combo box and then click OK.  To complete the Orders connection to our backend data source, I want you to expand Other Destinations from the SSIS Toolbox and drag OLE DB Destination on to the open workspace.  Click on the original OLE DB Source and drag the blue arrow to make a connection with the OLE DB Destination.  Double-click on OLE DB Destination to bring up the OLE DB Destination Editor.  Select your Mainframe Access database in the OLE DB connection manager combo box, select Table or View in the Data access mode combo box, and select Orders in the Name of the table or the view combo box.  Click on Mappings to ensure you have the appropriate linkages between the Available Input Columns and the Available Destination Columns.  If everything looks good, click OK.  Save and Build your solution just to verify there’s no errors and then test this Data Flow.  Keep in mind that this Data Flow goes in the reverse direction of the first two.  An Order placed on a smartphone or tablet will make its way to SQL Server via Web Services and wireless data networks.  This makes things a little hard to test so we’ll need to insert some dummy data to mock up this scenario.  Launch SQL Server Management Studio and manually insert data into the Orders table.  My Id is an Identity column and I inserted 1, 1, 1 in CustomerId, ProductId, and Quantity.  You can now test this inside Visual Studio and look for the green circles with check signs to verify that things worked.  Last but not least, take a look inside your Mainframe Access database to ensure the Orders made it over.

Congratulations on making it to the end of this exercise!

As I show you how to build Microsoft MEAP, my goal is to illustrate how easy this can be. After walking you through the exercise in this article, the takeaway in integrating your Mobile Middleware (SQL Server) with backend systems (Access databases) is that it’s a SimpleVisual, Drag and Drop operation.

I hope you now have a good understanding of Gartner’s Enterprise Application Integration (EAI) critical capability for MEAP.  I’m also hoping you see how easy it is to perform this EAI to multiple backend systems and data sources using the Microsoft technology your organization already owns.  In the next article I’ll show you how to create .NET Business Objects that model the aggregated data schema you’ve created in SQL Server.  Then I’ll show you how to expose that data to your mobile devices via the ASP.NET Web API.

-Rob

Tagged on:                                     

One thought on “Building Microsoft MEAP: Adapters

Leave a Reply