Keep upto date with everything going on at Simego.
20 November 2013
Often it is necessary to keep a SharePoint Document Library synchronised with the local file system including sub-folders and Created Dates. This could not be easier using DS3!
Step 1. Connect the Source to a SharePoint Document Library
Step 2. Connect the Destination to the file system
Step 3. Map the fields and Compare A -> and Synchronise
Delete all other columns and create the following map:
Click the Compare A –> B button, then the Synchronise Button and then the Start Button and you will now have a SharePoint Document Library including subfolders and files on the file system.
18 November 2013
One of the challenges with Dynamics CRM Online, and the cloud in general, is integrating cloud data with all your internal touch points and requirements.
If you want to do some detailed analysis of your account, contact or any entity data from Dynamics CRM, exporting a complete entity can take time and be laborious. Some companies like to have a ‘backup’ in case of extended outages.
Recently a client asked if he could download his 925000 row account table!
Step 1: Download and install the trial (you can currently do this using the trial edition up to 10k rows without registering!)
It is currently in the supported BETA:
Step 2: Open DS3 Developer and connect to Dynamics CRM Online
Click the green ‘Connect Data Source’ on the left.
Select the Dynamics CRM provider
Enter in Dynamics CRM Online Details:
- Authentication: Federated
- URL: https://crm4.Dynamics.com
- Pick your organisation then the entity name.
Step 3: Select the columns we want to download
We must tell DS3 which columns we want to extract from the entity. The more columns we include, the more data that has to be downloaded which is therefore slower (Dynamics CRM will bring down about 1000 records per second). If you want ALL of the available columns, check the tick beside the ‘Columns’ item in the columns tree view on the left.
Step 4: Use DS3 to create a CSV file for you.
In this step we use DS3 to create a CSV file with the same shape (columns) as your source entity.
Click tools on the menu bar, then Select the ‘Create CSV File’. Enter in the destination of the CSV file.
This creates a new CSV file and points the destination of the download to it.
Step 5: Compare and download the data
DS3 is more than just a download tool and is designed to synchronise two systems. In this case the CSV file is empty but we still have to compare it to the online entity.
For performance you can change the Page Size in the CRM settings to be 5000 (the max permitted).
1. Compare. Click the Compare A –> B so that DS3 can determine what to add to the CSV File.
DS3 will now download all of the data form the Dynamics Online Entity.
2. Synchronise. DS3 will prepare the synchronisation and present you with the ‘Synchronise Data’ Dialog.
3. Start. Click the start button and DS3 will now write the data to the CSV files.
When it has finished synchronizinga, all of the data will be written to the csv file.
All your records will now be in the CSV files, for some more interesting downloads you can download to a SQL Table, an XML File , an XML Dataset, an Excel Sheet etc etc.
This can be automated now using the DS3’s Server component to just incrementally download the changes.
Get Started and download here it is only 8 MB and no registration for the trial:
1 November 2013
It is more and more important to businesses to have up to date data to act upon. Customer service representatives informing their clients ‘If you wait a couple of minutes you should be able to log-in’ or ‘Your balance has not been updated yet, can I take a number or can you call back?’ are not what successful businesses are made of.
Earlier on this year an Aberdeen Group report discovered that business leaders wanted more and more access to real-time data.
David Linthicum, the author wrote “The report noted that 89 percent of enterprises that use real-time integration have the power to provide managers with accurate information when it is needed, as opposed to only 73 percent of organizations that do not use real-time services,”
This on face value looks like a 16% difference in capability (or competitive advantage). In reality, it is a huge difference due to the source of data no longer being from clean, managed data store but from core transactional systems.
“This includes updating the real time data that is flowing to the decision maker, as the data changes over a given period of time. For instance, the ability to track factory production over an afternoon, as the production data changes minute-to-minute.
“In other words, we are moving from a report-oriented mentality to a dashboard-oriented mentality.”
Who really needs Real-Time?
Finance and banking solutions are very reliant on systems being in-sync due to the timely nature of balances, pricing and transactions. Information such as available balances, current status of accounts and the actual cost of a transaction are required seconds after their change. As mentioned above, businesses who want to be able to build new processes should think hard of the benefits of not having to wait until later or worse an overnight update to make decisions.
What is involved in integrating in Real-Time?
There are obviously three critical stages to integrating data from one system into Dynamics CRM in ‘Real-Time’:
- Detection of the change in the source data.
- Identifying if this change impacts on a Dynamics CRM destination entity.
- Updating or Inserting data in the Dynamics CRM destination entity.
In addition there critical dimensions to the data changes:
- The amount of entities changed in the source entity (eg Start of Day balance updates) in a time window.
- The time it takes to process an update or inserts vs the next required update window .(eg If there are 100 changes in 30 seconds and the integration solution takes 0.8 seconds to process a change serially then they system will never be in sync).
Critically Real-Time integrations have to be ‘in sync’ before starting the real-time data integration process. This is possible with preparation and good data management but the most difficult moment is when the source system or the Dynamics CRM system are unavailable. There must be a process by which once reconnected, the system will bring themselves back in sync. Recovery from unavailability, is the hardest problem in real-time synchronisation.
An example of Dynamics CRM Integration
As a demonstration I am going to configure a Real-Time synchronisation between a Microsoft SQL Server Table and a Dynamics CRM entity.
For the example to be realistic we are going to test two scenarios and record the shortest and longest time from the change in the source systems to the update completing in the target system:
- Simulate a ‘Large Single Update’ such as a balance update in the morning or mass change in data. Updating 3000 records in the SQL Table a Dynamics CRM entity with 6000 items.
- Simulate a ‘Regular Change’ of data in the source system: 5 updates every 5 Seconds from a table with 6000 records targeting a Dynamics CRM entity with 6000 items .
Detection of the change in the source data
To detect the change in the source table I attached a DS3 Monitoring Trigger (this is not a SQL ‘Trigger’ attached to the table). This monitoring trigger activates a DS3 Integration project when it detects a insert or update to the source table. There are many Monitoring triggers built into DS3 Server (File Monitor, SharePoint List, Http result etc etc). This should table about 1 minute to set up .
Identifying if this change impacts on a Dynamics CRM destination entity.
Create a new DS3 project mapping between the source SQL table and the target entity.
Updating or Inserting data in the Dynamics CRM destination entity.
I uploaded the DS3 project into DS3 Server. I can now connect the Trigger to the project.
We don’t have to worry about recovery in this example as DS3 detects the last successful sync and automatically synchronises from there. This technique is only possible if you have a high performance insert/update process as you have to ‘catch-up’ i.e. Insert/update 100x faster that required to keep the system in sync.
Scenario 1: 6000 Instant Updates
|Source SQL Table Updated||16:11:23||Start|
|First Update Appears in Dynamics CRM||16:11:25||3 Seconds from Start|
|Last Update Completes in Dynamics CRM||16:11:42||9 Seconds from Start|
Scenario 2: 5 Updates every 5 Seconds
|Source SQL Table Updated||16:51:18||Start|
|First Update Appears in Dynamics CRM||16:11:21||3 Seconds from Start|
|Last Update Completes in Dynamics CRM||16:11:22||4 Seconds from Start|
If we are having updates every 5 seconds we would probably do away with the change detection and just schedule the updates every 5 seconds, thus reducing the load on the source. DS3 Server is also doing some clever stuff like stopping the trigger running another insert when detecting changes and making sure any updates that happened during a sync were processed.
Real-Time Dynamics CRM synchronisation is possible and manageable, I set this up in about 15 minutes and ran the tests over an hour. When we implement these systems with our clients, we also run a reconciliation process which verifies all the updates and delete have been completed. This is a simple job we schedule nightly and allows us to also manage deletes elegantly and gives the business managers a solid reconciliation position.
If you would like to discuss with us how you can increase the productivity of you Dynamics CRM solution through Real-Time integration, give us a call or email : firstname.lastname@example.org.
18 October 2013
Ouvvi Automation Server can now be licensed and configured to support multiple isolated environments on a single server. This is great if you want to separate your line of business applications or create a clear separation between DEV and PROD environments on the same machine.
Each Environment has an individual Database, Windows Service and Website therefore ensuring that each Instance/Tenant is completely independent of the others. This ensure that you can separate both the operation of the integration and who has access to the environment.
Each Instance shares the Data Synchronisation Studio installation on the server.
The number of instances on a physical machine is limited only by what is practical, since each instance requires it’s own dedicated Windows Service the maximum we would recommend would be 20 instances on a single server.
The instances are licensed individually if you would like to talk to us about this configuration please get in touch via our support desk email@example.com
17 October 2013
Quick guide that shows how you can use Data Synchronisation Studio with your Active Directory and SharePoint to create a Staff Directory or similar from Data Stored within your Active Directory.
First step is to create a “Contacts” List in the SharePoint Site you want to use to hold the data from Active Directory. Here we’re using SharePoint 2010 but you can use SharePoint 2007 and 2013.
Next Step is to create a Column to store the Active Directory Identifier (DSID in Data Sync). Data Sync includes a Wizard that allows you to create a Read-only Column on your SharePoint list.
Go to the Tools menu and Select “Create SharePoint Column”
This starts the Column Wizard.
Enter your SharePoint Site URL and credentials (Leave Credentials blank for current logged on user).
Choose the List that you created earlier.
Create the column as DSID and Type System.String, Hidden (DSID is a special column type known to Data Sync).
Click finish and Data Sync will add the DSID column to the SharePoint List.
Next Step is to connect Data Source A in Data Sync to your Active Directory, Click “Connect Data Source” on Data Source A.
Then Locate the Active Directory Users Provider and enter the LDAP path to your Directory, this could be something like LDAP://dc=corp, dc=litware, dc=com or a server name or IP address like LDAP://yourdomaincontroller
If You want to also filter the returned users to a specific Group you can also define the Group filter in the LDAPFilter property.
For example this filter returns objects of Type User from OU office.simego.com where the users are members of the Simego Users group.
Next Step connect Data Source B to your SharePoint List (Use the SharePoint (Client API) Provider with SharePoint 2010 and 2013)
Locate the List you created earlier from the SharePoint Explorer.
Pressing OK, brings up Data Sync Automatic Mapping feature where Data Sync will automatically apply a pre-defied schema map. Select the AD to SharePoint Contact List (DSID) Map.
This then creates a pre-configured schema map ready to go.
Now click Compare A->B to load the data from AD and SharePoint and work out the changes required to make both sides equal.
Data Sync then display the results where you can view the data before it is coped to SharePoint.
Click Synchronise on the results window to open the synchronise data window.
Click Start to actually Synchronise the Data with SharePoint.
After a few Seconds the sync process will complete.
Now if you switch to SharePoint you will see your data created in your list.
Now if you want to run this on a Schedule via Windows Task Scheduler you should stave the project and run the “Create Scheduled Task” option from the Tools Menu.
Select the schedule Click OK and enter your Password. Windows will then Run your project based on the Schedule you selected to keep your Data between AD and SharePoint up to date.
17 October 2013
Often when working with Dynamics CRM there are other systems and processes that require data from Dynamics CRM. This is a task that can be easily completed by using DS3 and Ouvvi in three steps.
For this example we wanted to export 3 items to Excel and one to XML at 6 o’clock in the evening.
1. All ‘Open’ opportunities have not been modified in the last week.
2. All new contacts (FirstName, LastName, email address and Telephone) that were created this month.
3. All new accounts that were created this month (AccountNumber, Name and web address)
For each of these exports it is necessary to complete 3 steps in DS3:
1. Connect to the Dynamics CRM and the entity for export.
2. Pick the columns for export and filter the rows being returned.
3. Connect to the Excel or XML Files.
We want to schedule these exports so the first thing is to create a Project in the Automation Server Ouvvi and then three empty DS3 Steps:
I open the first empty step “Export Unmodified Opportunities” in DS3 and connect to the Dynamics CRM 2013 Opportunity entity:
I can then just drag and drop the columns onto the schema window until I am happy:
Then I can use the ‘Tools'->Create Excel Sheet’ to create the exact source schema in an excel file . DS3 will also automatically set the destination to that Excel sheet. Interestingly, DS3 can create SQL Tables, SharePoint Lists, XML files etc from source schemas.
We can then click, Compare A-> B..Synchronise..Start and all of the Opportunities all be in the Excel sheet.
In reality we want to filter the opportunities to just the ones in the last month. In Dynamics CRM we in an advanced find (or view a current view) we can export the filters by clicking the ‘Export to XML’ to see the <filter> expression.
We can copy the <filter> portion into the FilterXmlExpression property in the Dynamics CRM Source connection details in DS3:
Previewing the Data again will show that we have filtered the opportunities down to this months opportunities:
Synchronising again will update the Excel file with the filtered rows. We can now just click the save button and the project will be saved inside Ouvvi and safly under version control.
We can run the project from inside Ouvvi to make sure that the server connect to the Dynamics CRM instance and the target Excel file. We do this by Clicking on the ‘Start Project’ button:
We repeat this for the other exports.
The next step is to schedule the project to run at 6 o’clock every evening. Projects are started by triggers. To create a trigger click on the Trigger menu and then Add Trigger and make it a time trigger:
We can then go back to the ‘Schedule Exports’ project and add the new trigger from the ‘Triggers’ tab:
The project will now run, exporting the three files every night (except Sat and Sun) at 18:00. We can use the ‘Documentation’ feature to create some documentation:
15 October 2013
Here at Adapx we work with 30+ partners to offer our customers easy Microsoft SharePoint integration into a range of systems like: SAP, IBM Maximo, EMC Documentum, Microsoft Dynamics, OSIsoft PI System, Oracle, Kronos, SalesForce, GeoPDF, and many more.
Thanks to our new partner Simego, a UK-based independent software and custom-development services company, Capturx customers can now easily get their Microsoft SharePoint data exported and integrated into SQL table(s) via OData.
As a quick refresher, Capturx for SharePoint enables teams to easily collect data with tablets or digital pens, and then send the data in real-time to their SharePoint Server. With Capturx, handwritten data is automatically captured, sent to SharePoint, converted to text, available for review, queued in automated workflow processes, and archived. From there, as mentioned above, integration into a range of systems is possible.
With Simego's Data Synchronisation Studio, Capturx customers can export data stored within their SharePoint Server and synchronize it with a SQL Server Database Table for reporting or archive purposes. The process is quick, easy, does not require special coding, and the Data Sync Project can be saved and scheduled to run at a specific time schedule either with Windows Task Scheduler, SQL Agent or Simego Automation Server (Ouvvi). For a step-by-step guide to see how you can go about configuring the integration see here.
If you'd like to learn more please contact a Capturx Solutions Engineer by emailing firstname.lastname@example.org.
15 October 2013
This is an example of how you can use Data Synchronisation Studio to Import or Export a CSV File with Podio. You can use Data Sync to schedule this operation to happen at regular intervals to create an Integration between systems.
First step is to connect Data Source A of Data Sync to your Source CSV File
This will then Open in Data Sync with a default schema
Click Preview A on the Schema Map to preview the Data and Check that it loads as expected.
Then in Podio create your App that will be used to store this information.
Next you need to connect Data Sync Data Source B to your Podio Account. First press the … button next to Credentials and sign-into your Podio Account.
Now Select your App from the App drop-down.
Now define your schema map, some fields will map automatically others you may need to do manually by either drag and drop or selecting from the drop down.
Important here you need to define a unique key that links the 2 systems together. Here we using the Podio external_id field to hold the ID value of our records from our source CSV file.
Now click Compare A->B on the toolbar so that Data Sync reads the data and calculates the changes to apply to Podio. You will get all records here since the App is empty.
Click Synchronise and Start to copy the data over to Podio
After the process has finished you will see Podio Updated with your data.
Now that this is setup and configured, it simple to save the project so that it can be run again and via the Data Sync Run Tool or Ouvvi Automation Server this can be configured to run at regular intervals.
14 October 2013
If you have upgraded your online instance to Dynamics CRM 2013 you may come across a reduction in performance when using the API to integrate external systems.
Historically, Dynamics CRM Online (2011) has had the ability to ‘throttle’ the amount of data being passed through the API but from our experience this has never been used. Dynamic CRM Online 2013 has throttling in place which can affect integration performance.
To get a better idea of how an much of a performance difference this can make we are going to compare insert/update and delete performance of Dynamics CRM Online 2011 vs 2013:
We created a file containing 50000 (49996) cheque records with minimal fields:
And we are going to insert it directly into a custom entity:
50k Record Performance at 9:30 am EST with Simego DS3
Dynamics CRM Online (2011)
Dynamics CRM Online (2013)
Dynamics CRM 2013 On-Premise*
*Run on DELL Desktop with SSD within a VM with 8Gb of memory
Deep Dive into Dynamics CRM Online 2013 Throttling
It has long been accepted that to achieve performance at the above 50/s level you will have to employ the following technologies:
- Parallel Loading
- Re-Use of Connection
- Re-Use of Authentication
These three techniques have worked well against Dynamics CRM Online and 2011………until now.
The Dynamics CRM 2013 SDK documentation states:
Throttling of concurrent calls – for Microsoft Dynamics CRM Online there is a limit of 2 concurrent ExecuteMultipleRequest executions per organization. If that limit is exceeded, a “Server Busy” fault is thrown before the first request is ever executed. For an on-premises deployment, throttling is not enabled by default.
Deeper down the message stack this throws an error related to the SQL Governor.
Essentially, if your integration solution attempts to create a third concurrent connection: GAME OVER.
If you dig deep enough and experiment long enough there is an interesting behaviour.
Throttling will not engage if the batch size is 10 or below. This means you can have as many parallel connections as you like without getting the ‘Server Busy’ error.
You can investigate these performance numbers yourself by trying DS3 for free
14 October 2013
We have a great new provider in Data Sync that can be used to import email messages into a SharePoint List. You can even extract data from the message body and update SharePoint Metadata columns with the results.
The Email provider is for Exchange Server only (sorry no generic IMAP support) and works with Office 365 as well as On Premise.
Requires: Data Sync Release 3.0.752+ and Ouvvi Release: 2.0.264+
This is an example of using Email with SharePoint to create a simple Purchase order Request/Approval type application. Users will Email the PO request and Data Sync will import them into SharePoint where a standard Approve/Reject workflow can be used to manage the approval process.
Importing Email needs to be incremental so that we are not continuously downloading your mailbox each time. Therefore we’re going to use Ouvvi to manage the project. Ouvvi will automatically add some project properties to the project when the project is run which we can use to manage the point in time where we sync from.
First Create a new Ouvvi Project
Next add a Data Sync Step
You can now open the Project from Ouvvi
Now open this project in Data Sync and connect Data Source A to the Email Provider, enter your Exchange Server Credentials and URL to the Exchange Web Services.
Once we have connected the Email provider, we need to adjust some settings. As we’re interested in PO requests we are going to filter the Mailbox and only return messages who’s Subject is ‘PO Request’.
You will also notice a FilterByRecievedDateTime field we will look at that later.
You can then Preview the Data to check that Data Sync returns the messages correctly.
Next we need to connect the Target to a SharePoint Document Library that were using to store the Email Messages. Use the SharePoint Client API provider since this has better support for SharePoint 2010 and 2013.
Choose the List ‘PO Requests’ from SharePoint Object Explorer.
We need a minimal Map for this to work however you can extend your SharePoint List with additional columns if you want to capture more information.
Click Compare A->B to Load your Email and compare it with what’s in SharePoint.
Then Synchronise the Results, In SharePoint you will see that the actual message is stored as a document which can be opened directly from SharePoint. From here you can setup a SharePoint Approval Workflow to approve or reject the PO Request.
Next for Incremental Sync, Ouvvi will add the Following Properties to your Project when it’s opened/run and this is where we need a little magic. What we’re going to do is update the value of FilterByRecievedDateTime from the Value in the Project Properties.
Go to View->Project Automation Window to open the code editor for the automation steps.
Click enable and enter 1 line of code to copy the value from the project properties into the DataSourceA FilterByRecievedDateTime property before the project runs.
public override void Start()
DataSourceA.FilterByReceivedDateTime = Properties["Auto_LastSuccessfulRun"];
Then finally set the SyncOption of the Project to SyncAtoBIncrmental to enable the Incremental support with the SharePoint Provider.
Now you can schedule this to run directly in Ouvvi by adding a Time based Trigger to the Project and watch as your PO Request emails are all fed nicely into your SharePoint List.