rss

Team Blog

Keep up to date with everything going on at Simego.

The New Run Tool Step Types

Updates about some of the new step types being added to the Run Tool

25 June 2021

The development of the improved Run Tool is going well, and whilst we've been refining the functionality of the software we have also added in many more step types.

If you haven't already you can download a preview copy from the download page, the step types discussed below are all available from V3.0.2014.

Available Step Types

A few of the new Step types now available are the Project Status Report, Run an Ouvvi Project, Import Command Line Variables, Import Environment Variables, and Folder Delete.

Step Types List

Project Status Report

Have your project send you a message either if there is an error, if there has been a change in the data (project must have a Data Sync step) or each time the project runs.

An example of the email and report that is sent when an error occurs can be seen below:

Status Email Error

If you then click onto the HTML attachment it will open the report in your default browser. When there is a failure the log will show the full exception message that was thrown.

Status Report Error

Ouvvi Project

You can now run your Ouvvi projects directly from the Run Tool.

Just enter in the URL to your Ouvvi site, the name of the project you want to run and any credentials required to connect to your Ouvvi site. If the machine user account that is being used to run the Run Tool is an authorised user within Ouvvi then you can leave the credentials blank.

You can also enter in any context values you want to pass to the project.

Run Ouvvi Project

Import Command Line Parameters

When you execute your Run Tool projects from the command line, you can now pass in parameters that you have defined to then be added as a user setting and used within that run of your project.

You start by configuring the step to set the parameter as a user setting, simply by entering the name of the property to be extracted from the command line.

Import Command Line Parameter

To set this parameter we open the command terminal and enter in the project details to run followed by -p: or -property: , and the name of the value = the value to return.

An example of the full code would be:

"C:\Program Files\Simego\Data Synchronisation Studio 3.0\Simego.DataSync.Run.exe" -e "project.dsrun" -property:WarningLevel=2;OutDir=bin\Debug

Run from Command Line

To use the parameter value within other steps just call the step name with curly braces surrounding it{{MyStep}}.

Import Environment Variables

You can also make use of Environment Variables in each of your projects by importing them to be used as a user setting.

Just enter in the variable name and decide if you want the step to error if the value is empty. This value of this variable is then set as a user setting and can be called by surrounding the step name with curly braces {{MyStep}}.

Import Environment Variable

Folder Delete

You can now delete folders as well as single files within your project. So when you cycle through a series of files within a folder, and maybe copied them to a new location, you can delete the whole folder from your processing area in one go.

Delete Folder

These are only a select few step types and we will have our full documentation to show you how to configure them available soon.

In the meantime, if you have any ideas or thoughts of what should be added to the Run Tool send us an email.


The New and Improved Run Tool

Updates about the DS4 development and the move to develop the Run Tool

7 May 2021

Whilst trying to build a new version of Data Sync (DS4) we had lots of new ideas and have made some good progress. However we realised that we were making the product more complex and confusing, which is not what Data Sync was built to be. It should be really quick and simple, where you connect to your data sources and integrate your data.

We also want to keep compatibility between the versions for as long as possible. We don't want our customers to have to re-develop their integrations every few months because our products have changed. The integrations you develop should last years. We actually have some customers still running the same integration 10 years later!

So we had a look at what we have and compared it to what we were developing. One of the reasons for the development was to bring in source control capabilities around the project step configuration, as is expected with any modern development process. We were doing this in DS4 but it was going to force our customers to radically change what they were doing and require them to upgrade.

This lead us to look at the Run Tool again. The Run Tool was a basic way to connect multiple Data Sync projects into a Run Book and could run on a schedule via the Windows Task Scheduler. It is already based on XML files, like Data Sync project files, and could easily be used with a source control system. It just doesn't have the features Ouvvi has.

Ouvvi has been our go to system for running multiple Data Sync projects and it is fantastic, but customers struggle to install it and it can be over kill for those with only a few integration tasks.

So we decided a better route, would be to bring some of the Ouvvi features to the Run Tool whist maintaining compatibility with the old Run Tool so that customers can build larger integrations without the need for Ouvvi. You can download the working preview version with the new Run Tool from the download page, and it is available from version 3.0.2000.

Some of the features we are adding are:

  • Updated UX (new Step Designer)
  • Drag and Drop features
  • Execute Single/Multiple Steps
  • Copy Step
  • User Settings to pass values to the Step configuration
  • View the step error in the UI
  • More detailed error messages
  • New Logging generates a separate XML Log file per execution
  • The ability to use Command Line parameters
  • Updating the .Net Framework to 4.7.2
  • Migrating more step types from Ouvvi to the Run Tool such as Project Status Reporting Step
  • Addition of the SuccessDataChanges Run Condition

So let's go into detail on some of these.

Right Click Options

You can now right click onto a step to choose from a number of functions available. You can copy a step, execute single or multiple steps, change the run condition, delete steps, and view the exception that was thrown if the step returned an error.

Execute Single or Multiple Steps

If you need to test that a step you have created runs correctly, you can right click and select Execute Step to run the single step.

Execute Single Step

If you need to run a selection of steps from the project, just select those you need, right click and select Execute Step(s) to run them.

Execute Multiple Steps

Change the Run Condition

You can still change the run condition of your steps in the panel to the right of the main window, however with the right click shortcut it's just that little bit quicker and easier.

You can either select a single step or multiple steps and change the run condition in one go by clicking Run Condition and then selecting the run condition you want to change to. This can be Any, Success, SuccessDataChanges, Failure or Disabled.

Run Condition

Copy Steps

Another feature that comes in handy is the ability to copy a step. Just right click, select Copy Step, rename it and click OK.

This can be particularly useful if you have a Data Sync project where only the connection needs changing. Just copy the step, double click to open and then make the changes you need.

View the Error Exception

In the past if your project failed on a step, you would need to go to the log, scroll through to find the error, and many times the error you saw didn't help you diagnose the problem.

Now when a step errors you can right click and select View Error Details.

View Error

Clicking on this opens the error window and shows the exception that was thrown.

Error Window

This leads us onto the improved logging of errors.

Logs

One of the main reasons we preferred using Ouvvi to the Run Tool was the level of details found in the logs. The Run Tool didn't have much detail which made it difficult to figure out what was causing the issue and how to resolve it. So we brought in the exception handling that we had already been adding to Data Sync.

Within the Run Tool itself, the logs can still be viewed within the Logs tab. These now show the full exception thrown in an output window, to help you resolve any issues. You should find it easier to work out what the issue is.

The log files are now also stored locally within the .ds folder that is created in the same folder as your Run Tool project. Each run is stored as an XML file you can view as you need to.

Error Log

Drag Drop

To make importing existing Data Sync steps and rearranging your step order, we've brought in the drag and drop functionality. Just drag your steps around until they are in the order you need, and drag any Data Sync Projects from your file explorer into the Run Tool Project.

Pass in User Settings

One of the key selling features of Ouvvi is the ability to dynamically pass in settings to your steps, so we have added this to the Run Tool.

There are a number of built in settings that you can make use of, which will be listed on the documentation pages. But you can add any setting you might need. This can be a file location, a name, a time etc.

You can pass these settings into any of the steps available, as well as passing them into your Data Sync projects. Just add the setting to your project and then add the setting to a step by containing it within curly braces e.g.{{MySetting}}

User Settings

If you prefer to run your Run Tool projects from the command line you can also bring in settings from command line parameters or environment variables.

Edit Steps

We always found creating and editing step configurations in the Run Tool a little cumbersome. The small panel to the right, wasn't the easiest to work with so we can now double click onto a step to open a window specific to that step and edit its parameters.

In this window you can configure and edit your step details as you need to. Double clicking onto a Data Sync step will open the Data Sync designer.

Step Config Window

The panel on the right now contains basic details, for most steps, such as the name and the run condition. Data Sync steps will have some of the project configuration details shown as well.

Source Control

And finally, the Run Tool has the ability to be managed with source control. We are currently using GIT to keep track of the changes made to our test projects and will be listing the details on how to set this up in the future documentation. But you can use your preferred source control provider as you need.

By making use of source control, you ensure that you can always roll back to a version that worked if any breaking issues are introduced.

Future Features

Hopefully you are excited by the changes being made, and before we make it a full release we have a few more items to develop.

One of these is adding additional steps from Ouvvi so that you can extend your projects further. And as always we are adding to the documentation and training pages to cover the updates.

The Run Tool will still use Windows Task Scheduler to schedule the project for now, but we are looking into other options. So watch this space.


New Active Directory Connector

The new AD connector is finally here!

16 March 2021

After mentioning an update to the Active Directory Connector back in October, we have finally found a moment to build it!

The connector is available from V3.0.1318 which you can download here, and we have new documentation available here. Once downloaded you can find the connector in the normal spot in the connection window under Active Directory.

This now provides a fully no code solution for Active Directory, as all the lookups are handled for you. However if you have requirements outside of the general scope you can still extend your project with Dynamics Columns and Project Automation.

If you have any projects using the old connector they will continue to use that connection until you decide to change them. We did this so then your projects do not suddenly break when you decide to upgrade and you can take your time trying it on new projects before improving your old ones.

You also have the normal functionality of storing your connections in the connection library, the ability to preview the data before connecting, and the ability to drag and drop your connections to connect and create lookups.

What has changed?

In brief we've made it easier to update managers, users account status (userAccountControl), set a password on new accounts and add/remove users to/from groups. We've added in functionality to return computers within your AD and you can connect and manage contact records.

The way we have removed the complexity around these tasks is by introducing Data Sync specific columns (DS- columns). These DS columns handle lookups, data conversions, and the quirky requirements AD has in order to update certain fields. Meaning you now only need to map your columns and not worry about adding functions to your projects.

Connect to Groups

The ability to connect to and manage groups is a new feature to this connector and there are multiple options available for you to use. The one to choose will depend on your source data and what you are trying to do.

Group Members

You can manage group members by passing in a list of groups against a list of users with the Active Directory V2 - Group Members connector.

Group Members Data

You would connect your group names to DS-Group-SAMAccountName and the members to DS-Member-SAMAccountName with both being a key column. If you don't have both as a key column you will get a duplicates error for each user if they are listed against more than one group.

Group Members Schema Mapping

If you want to remove users from groups, don't forget to set EnableDelete to True.

Passing Users to Groups

Another option is to pass an array of users/members to each group, with the Active Directory V2 - Users/Contacts/Groups/Computers connector and selecting Groups from the drop down. Your users should be listed in alphabetical order and separated by a semi-colon (;).

Pass Users to Groups

You need to map your group name to the DS-SAMAccountName column and your users to the DS-MemberNames. Data Sync will then handle the user lookup and add the users to the groups. If any of the groups do not exist in your AD then Data Sync will create them and add any users listed.

Schema Mapping

Passing Groups to Users

Alternatively you can choose to add users to groups by connecting to the users and passing in an array of groups they are members of. Just add your group list column to the schema map and link it to the column DS-User-MemberOf.

The groups need to be listed in alphabetical order and be separated by a semi-colon (;).

Pass Groups to Users

Schema Mapping

Adding Users

Adding and managing your users in AD with Data Sync is now incredibly easy. Simply connect with the Active Directory V2 - Users/Contacts/Groups/Computers connector, keeping Users in the dropdown. Map the columns you need and Data Sync will handle the rest.

Users Connect

Users Schema Map

Updating Managers

To update the manager attribute you can choose between using the Employee ID, the sAMAccountName, the distinguished name or the email address to map your fields.

Which you pick will depend on the data you have in your source data. If you have more than one available in your source please only select one. You then map this column to the corresponding DS- column.

The DS column then does the lookup for you and will convert the manager details into the format AD is expecting.

Manager Mapping

Setting Passwords for New Users

You no longer need to write project automation code to supply a password for new users. Just map your password column to the DS-SetPassword column to add a password for the new users.

Schema Mapping

For updating the password on existing accounts you will need to use project automation which is explained here.

Setting the User Account Control

As you are all aware, the UserAccountControl determines if a user account is enabled or disabled. Rather than needing to figure out the code to pass to AD you can now just map a boolean true/false column to state the account status.

For example if you have a column named IsDisabled and when the account is disabled this is set to true, you can map this to DS-UAC-Disabled.

UAC Schema

We've also covered the User Account Control Flags Locked, PasswordExpired and PasswordDoesNotExpire. Just map your boolean source column to the corresponding DS column and Data Sync will set the flags accordingly.

Return Computers

This can be used for reporting what computers are listed in your Active Directory OU.

Connect to Computers

You can return a list of computers available, the managers associated with those computers, and apply LDAP filters to further limit your results depending on what you need returning.

Computers List

Manage Contacts

Contacts also has the useful DS columns so that lookups are handled for you. Just connect using the Active Directory V2 - Users/Contacts/Groups/Computers connector and select Contacts from the dropdown, map your columns, compare and sync.

Connect to Contacts

You need to map the contact name to the DS-Account-CN at a minimum to add contact records. An example of how your mapping might look is:

Contact Mapping

Any Suggestions?

As this is currently still in development, now is your chance to try it out and relay any feedback or requests you may have.

Go ahead and download the beta version to try it out for yourself, and as always if you have any questions just send us an email.


Coming Soon: Data Sync V4

A sneak preview at the new Data Sync in development now

11 February 2021

We've been busy since the end of 2020 working on a new version of Data Sync, and although it isn't yet finished I thought I would take the chance to give you a preview of what is coming.

This new version, Data Sync V4, connects to your Ouvvi workspace so you can develop, manage and edit your projects straight from your desktop.

Data Sync V4

We've found that it isn't always obvious how Ouvvi and Data Sync go so well together. Because of this many of you are not aware of how much Ouvvi can help with management and scheduling of your integration projects, let alone the other possibilities Ouvvi opens up to you. So we thought to help with this, lets try to combining the two together.

Over the last year we have played around with the idea of making Data Sync built into Ouvvi, however every time we've tried to do this we found that core functionality kept being lost. So this led us to wonder, what if we brought Ouvvi into Data Sync?

This is what we are developing now.

You will still be able to use Data Sync locally without the need for Ouvvi, with a local connection library. But for this post I'm going to talk about how it improves your experience with Ouvvi.

Workspace Editor

The new version has what we have termed the Workspace Explorer. This is where you connect to your Ouvvi site and can view/access your connection library, projects and steps, triggers and user settings.

Workspace Explorer

From here you will be able to right click onto items to run, create or edit.

Tabular Views

One of our key reasons for creating a new version was to be able to edit multiple steps and projects within the same window, to make designing projects a quicker and more efficient process. In version 4 this is now possible! You can have multiple Data Sync projects, along with multiple Ouvvi Steps open within the same window. Each item opens as a new tab within the window and will be highlighted when you are viewing it.

Open Multiple Items

This will allow you to edit projects side by side, without the need to open multiple windows.

Running Projects

When developing your integration projects it can be useful to be able to run projects to ensure they are configured correctly and each step is running as expected. Within Data Sync V4 you will be able to run your projects manually from your desktop, with the log output being shown as they run. Starting projects this way will run them directly from your Ouvvi server.

Execute Project with Logging

You will still be able to run your projects from the Ouvvi site and projects will still run if they have triggers applied to them.

Creating New Groups, Projects & Steps

Another key feature will be the ability to create steps, projects and groups from your desktop without needing to browse to your Ouvvi site. We have found that being able to create steps on your desktop made project building seem faster and flow better. So this functionality has been added in.

You will be able to create a step by right clicking on your project and choosing "New Project Step". Then select the step type from the list and the configuration page will appear as a new tab.

Add New Step

Editing Steps & Projects

The capability to edit projects and steps will also be available. So you can make quick changes during development without needing to browse to your Ouvvi site. Simply open the step or project, make the changes and save.

Edit Step

You will also be able to add and edit connections in your connection library. So if a password needs updating or credentials need changing, you can do this quickly from the designer.

This view uses our Dynamic UI that we mentioned in the October update, which allows us to render the components to either the web page or the desktop app.

Move, Reorder & Copy Steps

We also thought it would make development of your projects easier if you could drag and drop steps to reorder or move them between projects, so this functionality has also been added. If you drag a step and hover over a closed group or project it will open it so then you can add it to the project in the order you need it to be in.

To quickly make copies of your steps you can right click and select copy from the list, you can then use the F2 shortcut to rename your step.

All of these features have been added with the thoughts to make development smoother and faster, with no frustration.

Import Existing Data Sync Projects

You will also be able to quickly import a Data Sync file as a new step by right clicking on the Ouvvi Project you want to add it to and selecting "Import Data Sync Project". If you are not currently using Ouvvi, it will be easy to add all of your current Data Sync projects in bulk, using this feature. You may need to change the order that the projects are listed in, if you want them to run in sequence. But using the drag and drop function, this should only take a moment.

It will also be possible to drag and drop your existing Data Sync files from the file explorer into your chosen Ouvvi Project. You can move either a single file at a time or select multiple files and drag them into your chosen project.

Anything else?

Alongside the development of version 4, we have also been exploring different connector options with Node.js and Go. This is instead of using ASP.NET and C#. You can see some of our sample connections so far on our github page.

By having these examples we are hoping to make building your own connectors more accessible, and maybe giving you the confidence to write your own to connect to any systems we don't have a connector for. Let us know how you get on and we can always help you with some pointers if you get stuck.

The new Active Directory connector we mentioned in our October update has been a little waylaid, but we will be carrying on with it's development once the new version is released.

On another side note, you may have noticed we have updated the pricing page to allow you to purchase multiple server licenses, just select the number you want from the drop down and proceed to the checkout. You can also purchase additional licenses mid-term and have them co-termed to your existing license. If this is something you want to do, please either contact us or log in to your account and you can manage this yourself.

As always, if you have any questions send us an email and we will be happy to help.


Using Ouvvi Apps to Feed Data into Power BI

How you can use Ouvvi Apps to consume data in Power BI

29 January 2021

A little while ago, when we first released Ouvvi Apps we mentioned that they could be used to feed data into Power BI Reports. So we thought it was about time we showed you how you can actually do this with an example.

This example uses CSV files as the source dataset but it could make use of any of our connectors. Normally you would connect this to your internal system which might be a SQL tables, SharePoint Lists or Dynamics Entities.

Our sample data is split over multiple files, separate sales data with a linking customer number and customer details.

This whole scenario will work best (due to credentials and permissions) when Ouvvi is exposed on an endpoint in your network. Ideally you would also apply an SSL Certificate to keep the connection secure.

Data Cleaning and Transformation

We like to clean and transform our data before using it, and used Data Sync to combine, transform and extract what we need to ultimately be passed to Power BI.

To combine the two files, we used a lookup to gather the customer information to go with the sales data. We have then used calculated columns to combine the customer name into one field and also generated a sent status column so then we can know which data has been added on each run of the project. This makes sure that the data passed to Ouvvi Apps is in the format we want, and there's less work for you to do in Power BI.

Data Cleaning

Configuration

The whole project is contained within our internal Demo Ouvvi site that is exposed on a public endpoint in our Network with an SSL certificate. Our Ouvvi Project is made up of a few steps (some are optional).

  1. Data Sync Project to tidy the data and sync it to an Ouvvi App
  2. A Data Sync Project to set the SentFlag to true (this is only needed if you want a report on the data that changes.)
  3. An Ouvvi Email Step to notify users that the report has been updated and need to be refreshed.
  4. A Report Email Step to summarise the data changes. You will need to include the SentFlag Update step for this.

Ouvvi Project

We make use of Ouvvi Apps due to the capability of exporting the data in multiple formats, for this example we use the XML feed to input the data to the Power BI Report.

Ouvvi Apps Exports

If you haven't used Ouvvi Apps before, check out our documentation to see how to get started.

Get the data into Power BI

Once you have your data into your Ouvvi App, you can import it in to Power BI Desktop using the XML export option.

Get the XML export URL (you can remove the limit filter) and open Power BI. This URL will look similar to: https://demo.lab.simego.com/apps/xml/demosalesdata

Got to Get Data , choose the Web connector and enter in the URL.

Enter URL

You will then be prompted to enter your credentials to your Ouvvi site so that the Data can be accessed, choose the correct type (this might be windows or basic auth). In the next window click Transform Data, and then click Table to open the data into a Table. You can now move on to building your report.

Report

Our sample Sales Report looks like this:

Power BI Report

We have a series of KPIs with some conditional formatting on the background so then you know if targets are being met, surpassed or are falling behind.

We have a breakdown of the sales by product (as a pie chart and in table format), a breakdown of sales by location (on a map and in table format), and a graphical representation of the sales over the last 12 months.

Email Notifications

If you use the Email Step when records are added to the data or the data is changed, an email will be sent out to notify the recipients. They can then refresh the model to see the changes.

Email Notification

Another step to consider, if your data changes are not too large, is to have an additional email step sent out with the data that has been added and/or changed (fourth step). This is where the second step (updating the sync flag) comes in, as it marks the items that have been added as sent.

Using the report email step you can add the URL to the HTML output from your App with a filter applied to only return the items with the sent flag set to false (the new data). So your URL output would look like https://demo.lab.simego.com/apps/html/demosalesdata?limit=1000&pivot=1&filter=IsSent%20eq%200.

Sample Email Report

Schedule

Now you need to schedule your project so that it either runs at a certain time interval or on an event (when data is changed). For example run it every hour (during business hours).

If you have set the run condition of the steps below the first Data Sync project to run on SuccessDataChanges (do this by selecting the option from the dropdown) then you will only receive the email notification when there are updates or additions to the data.

This scenario might be useful to your current integrations. Why not extend your current Ouvvi projects to include these steps and get updated Power BI reports and reports of data changes.

Send us an email if you have any questions and we can help get you started.


Product Updates October 2020

It's been a while! Here's an update and a few of our future plans

19 October 2020

It's been a while since we last posted on our blog, so we thought we would give a quick update about some of the things that we have been up to and some of the things we have planned.

Website Updates

You may have noticed that our docs are looking a little different. We decided to change the structure of them so the articles are broken down into more sections and the sections are in a card view. We have also made them readily available on the main website in the main navigation menu, hopefully this makes them easier for you to find.

New Docs Layout

You will also notice a search bar within the documentation pages, which will search just the docs so you can locate the articles you need as quickly and easily as possible.

The old help pages have been absorbed into the how to articles or into the user-guide information with updated screen captures and guidance. So those old articles you all found useful do still exist, even though the old links to them will no longer work.

We are always updating the documentation content to make sure it is up to date and any new features are covered, however if there is anything you can't find let us know and we can either point you in the right direction or tell you how to do it.

Data Sync Changes

AD Connector Updates

The Active Directory connector has had an update where it now supports SSL/TLS connections. To connect using SSL change the UseSecureSocketsLayer dropdown to True.

Use SSL

We have big updates planned for the AD connector which you can read about further down this article.

Dynamics Connector

We have been working hard to keep on top of the many changes Microsoft are making, one of these changes being the deprecation of Legacy Authentication (Username and Password) on new and upgraded Dynamics instances. Because of this change applications will need to connect using OAuth, so the Dynamics connector now supports OAuth.

We've created updated guidance on how to connect as you need to create an App within Azure AD to get your ClientID and Client Secret needed to login. Check out the latest Dynamics connection docs here.

It may sound scary, but we promise you it isn't bad at all! The most difficult thing will be getting hold of your Azure AD & Dynamics administrators to configure this for you if you don't have the permissions.

As always, if they have any questions get them to send us an email and we can help guide them on what to do.

SharePoint Connector

As with the Dynamics connector we have also updated the SharePoint connector so it now supports OAuth. The setup for SharePoint requires you to create an App in SharePoint rather than in Azure AD directly, our full documentation details can be found here.

The OAuth connector also supports File Upload and Download, so you can continue to use Data Sync the same as you would have connecting via legacy authentication.

Exchange Connector

It's going to sound like a broken record now... Due to the deprecation of legacy authentication, which was due this year for Exchange but has been postponed till 2021, the Exchange connectors now support OAuth.

As you have come to expect, you can connect to your main mailbox, appointments, contacts and tasks as you need to with your new connection details.

You can find the details on how to connect with OAuth here.

AWS S3 Supports Incremental mode

Another update that I almost forgot to mention is: Our AWS S3 connector now supports Incremental Mode. So for large datasets you can switch to Incremental Mode to incrementally sync your changes, you should find your performance increases when you have a few updates to make (as it won't be trying to load your whole dataset).

Ouvvi Changes

Connector Updates

Like the Data Sync updates, the Ouvvi SharePoint and Dynamics connectors now support OAuth Authentication.

For Dynamics change the Authentication Type dropdown to OAuth and enter in your ClientID into the username field, Client Secret into the Password field, and your domain into the domain field. The documentation for this will be coming soon.

Dynamics OAuth Connection Ouvvi

For SharePoint, simply complete the OAuth credentials with your details. The full documentation can be found here.

SharePoint OAuth Connection Ouvvi

Ouvvi Authentication using Azure AD

You can now configure Ouvvi to use your Azure AD login. If you want to make use of MFA and not have yet another password, you can now configure Ouvvi to use you Microsoft Azure AD login details.

We have our full documentation explaining the process here, but in short you will create an App in Azure AD and then use the details from this app within Ouvvi. The deployment of an agent needs a different process to normal, and this is documented in the documentation, however we are planning on making the agent deployment for this more streamlined in the future.

Import and Export Ouvvi Apps

Ouvvi Apps can now be imported and exported.

If you make use of Ouvvi Apps (we recommend you do as they're incredibly useful) you can now export them to be imported in your other Ouvvi instances. This is also the same for if you move servers and want to create a clean install of Ouvvi on the new server.

Go to the Ouvvi Apps page, click Export and check the checkbox next to each of the Apps you want to include in your export. To import go to the Ouvvi Apps page, click Import and locate the exported files.

Import/Export Ouvvi Apps

ZipArchive Provider

To make your Ouvvi projects have DevOps solution the import and export can be opened within an IDE so you can make changes and store the updates within your chosen source control system. When you're ready to deploy, simply package the solution and import it back into Ouvvi.

AWS S3 Upload and Download Step Handlers

To keep adding to the value of Ouvvi, we now have built in connectors for S3 Upload and Download. You can include S3 Upload and Download steps within your Ouvvi projects.

We specifically added the S3 steps to help us automate our website deployment.

Ouvvi Online Update

It has been a little while since we first launched Ouvvi Online. We know we didn't go with a big launch party but we did want to see how it would go down with you all first.

So for our progress update: We have a number of customers using Ouvvi Online, some of these are existing customers who have migrated to the online platform (thank you for your support!) and some are brand new customers (welcome to the family!)

We also make use of Ouvvi Online for all our system processes and we have two major instances of Ouvvi to handle these: Systems and Operations.

Systems handles the rolling deployment of Ouvvi Online updates, the website deployment and the building of Data Sync and Ouvvi releases.

Simego Systems

Operations handles the working parts such as the Helpdesk, order notifications, trial notifications and the GDPR data control.

We have been using Ouvvi Online since February/March 2020 and haven't had any issues (touch wood!) We get reports from Pingdom to let us know if there are any issues/downtime, and when you look at our report you can see 100% uptime!

Pingdom Report

To handle updates to the platform we use a rolling deployment and this has been working brilliantly. One of the many benefits of Ouvvi Online is that we handle the updates to the system so you don't have to. You also get access to the latest features before they become available in the on-premise version of Ouvvi, and some features will only be available in Ouvvi Online.

UPDATE (14-Jan-2021): We have come to the conclusion over the past year that running a data integration platform in a SaaS way is very difficult. Many customers are in highly regulated industries and data protection/privacy requirements limit the ability to run operations on shared infrastructure.

With this in mind we have decided to retire our Ouvvi Online project. Overall it was a great learning curve, and showed us that we could build it. We will be migrating some select features from the Online platform to the normal Ouvvi so that you too can make use of these.

Automated Build System

Using Ouvvi Online we have now automated our Build process of Data Sync and Ouvvi. So in a slightly strange but still ever so cool way we use Ouvvi to deploy Ouvvi, chicken or egg situation anyone? So now when we make an update to Data Sync and/or Ouvvi and we want to deploy this to the beta site we simply log in to our Systems Ouvvi and start the project. Yes we could take this further to be fully automated so then when a file is changed in a folder it automatically kicks off the project, but we like having this bit of control.

Automated Build

Previously we found our website deployment build was incredibly manual, but why couldn't we automate it? So in our process clean up we thought "Couldn't we use Ouvvi to do this?"

So we now have three projects that manage the deployment. One to upload to S3, one to deploy to the staging site for our testing, and another to deploy to the live site.

Website Deployment

Going Forward

To wrap it all up lets have a quick look at what you can expect from us in the near future.

Active Directory Connector Updates

We want to do extend the Active Directory connector so that it can do more, however it is most likely going to be a brand new connector so that we can do things behind the scenes a little differently to make the changes possible. Some of the features we want to add are: Automatically map the Manager column without having to define the column, support connecting to multiple OUs and support lookups into other OU's and groups.

Are there any other features you think would be useful to have added?

Now is your chance to shape the AD connector to potentially work how you want it to, let us know any thoughts you have and we can look at adding them in.

Adding SQL Lite to Ouvvi

We are looking to add a self contained database option for Ouvvi such as SQL Lite. The thought process behind it is that it will simplify the installation and you will not need to install SQL Express or have your own SQL Server to make use of Ouvvi.

What are your thoughts, do you think this could be a good option and would you be interested?

Dynamic UI for Step Handlers

We are currently working on a Dynamic UI for the Ouvvi step handlers. This means that you can build a custom user interface from the code without having to define it elsewhere.

You'll be able to create custom step handlers to upload into Ouvvi and use and the UI will automatically return with the default view unless you decide to define it exactly. You can group the various properties to return in separate cards. By default it will return each as a column that spans the space, but you can then further define this so that properties are returned side by side spanning half or a third of the row etc.

We've done this to clean up our code base and make our lives easier when writing new step handlers, triggers and connectors for Ouvvi, and if you want to write your own it should help you too!

It will also make it possible to dynamically hide options that aren't relevant for the configuration option you chose.

Got any questions about writing your own Handler? Send us an email and we can help you out.

Continuing through Covid

We've been lucky enough that throughout this pandemic you, our customers, have continued to find ways to use Data Sync and Ouvvi to help automate your processes. Over this time we have taken on 30 new customers, who are either completely new to our Products or have been recommended to use them by someone else.

So this has kept us busy, making sure that everyone can continue to connect to all their applications and trying to plan ahead for the future. If everyone is going to be working remotely more often then is a shift towards being a SaaS product where we need to be? Let us know your thoughts, would being able to access Ouvvi and edit your projects from anywhere either on your laptop or your mobile or tablet be useful for you?

If it is, maybe you want to try Ouvvi Online out.

Final Note

On another more personal note, we got to have a bit of a celebration in September as Rebecca got married! Due to restrictions they have had multiple small wedding days and so far all of them have had glorious sunshine. Let's hope the rest are as sunny when they get back to celebrating again in spring.

So as our final note of the post here is a picture of the happy couple on the day.

The Happy Couple


Bulk Upload Documents into SharePoint from Excel

How to use Data Sync to upload documents to a Document Library from a single Excel Spreadsheet

27 April 2020

Lets take a scenario that most of you come across on a frequent basis. You have a directory of documents that need to be added to SharePoint. With Data Sync there are a few ways to do this, and the one we are going to cover today makes use of a simple Excel Spreadsheet. Your other options can be seen in our documentation here.

The example shown below uploads only a small sample of documents, you can use this method to upload many thousands of documents.

Before you begin

The Document Library

You want to make sure you have a Document Library ready in SharePoint to take your documents and define any metadata columns you want to be included.

Empty Document Library

The Spreadsheet

In your Excel spreadsheet you need to specify the name of the document and any metadata you want to be uploaded alongside it. In our example we have a range of ten documents with different modified dates and different approval status’.

Spreadsheet of Data

Documents in SubFolders

If your documents are contained within folders in your directory, you want to include the sub-directory folder name with the file name for example Folder1\doc1.docx. This will ensure that the document can be found, and your directory structure is replicated in the Document Library. An example spreadsheet can be seen here:

Spreadsheet of Data With Folders

The Project

Open Data Sync and connect to your spreadsheet as the Source choose the Open XML provider from the list, then locate your spreadsheet containing your metadata and click Connect.

Excel Connection

In the connection properties go to the section Settings.Writer and complete the following fields:

  • BlobBasePath : Enter in the file path to your directory of documents in our example this is C:\Users\Rebecca\Documents\Demo Documents.
  • BlobFileName : Choose from the drop down the column that returns the name of the file. In this example it is the column FileName.

Property Settings

Connect to your SharePoint Document Library as the Target by choosing the SharePoint Online or SharePoint ClientAPI (if using an on-prem version of SharePoint) provider from the list.

SharePoint Connection

Schema Mapping

Now map your source columns to your target columns. You need to map one column to the URLPath in SharePoint. For this example we are adding a Title Property to each document, setting whether it is approved or not and a modified date. The full schema mapping of the example can be seen below:

Schema Mapping

As of version 3.0.1276 you can map a different filename to the one you have specified in the BlobFileName field, and the BlobFileName column does not need to be included in the schema mapping. So if you want your files to go to different folders or have a different name, map the other Filename column (such as TargetFileName in the example spreadsheet) to the URL path in the schema map.

Mapping without BlobFileName Included

Run the Compare and Sync

Now run the comparison by clicking Compare A -> B and you can preview the results to make sure that they are appearing as expected.

Comparison Results

Once you are happy synchronise the results. You can now go to the Document Library in SharePoint and you should see all the documents added with their corresponding metadata.

Results in SharePoint

Results with Folders

If you had defined folders for the documents to be in your results in SharePoint should be like this:

Results in SharePoint with Folders

Clicking inside a folder shows the documents.

Results in SharePoint Inside Folder


Introducing Ouvvi Apps

An introduction to Ouvvi Apps

4 September 2019

We've been busy working on some new Ouvvi developments over the few months, one of these has been Ouvvi Apps.
This new addition has so many possibilities we are going to split it into multiple blog posts so that we don't overwhelm you with information.

So lets get started with our introduction to Ouvvi Apps.

What are Ouvvi Apps?

Ouvvi apps, in a nutshell, is a new form of table storage with a modern restful API. Now that may not sound like much to you right now but when you see how it'll save you time and effort in your integrations, and the possibilities it generates, I think we might make you think otherwise. Ouvvi apps was designed to make data integration and reporting on your data simpler and more useful.

Simego Ouvvi Apps

Simego Ouvvi Apps

Ouvvi Apps are SQL tables on steroids. Not only can you use the apps as a store or backup of your data, but you also get to use some useful additional features. Ouvvi Apps have a simple REST based API and an Export API which can output the data in a number of standard Data formats.

Built in Connector

Ouvvi Apps comes with a built in ready to use Data Sync connector, so you can implement these in your integrations quickly and with no hassle.

Ouvvi Apps Connector

You also get a quick start function to create new apps within your Ouvvi environment!

Quick Start

Export Formats

Each app has its own API endpoint that you can connect to.
You can also generate exports of your data in a number of formats. We're talking XML, HTML, CSV, Excel and JSON. These can all be consumed by a URL from the Ouvvi server so a great way to share data with other applications.

Export Formats

Data Consumption

Consume the data stored in Ouvvi Apps straight into Power BI. Sometimes it can be difficult to get your data into Power BI or slow depending on the source system. Now you can use Data Sync to update your Ouvvi Apps and then connect Power BI to the Ouvvi App.

Power BI

Data Transformation

With Data Integration it can be helpful to use a SQL Database as a Temporary Staging Store. Now with Ouvvi Apps you can use these instead and no longer need to deal with creating SQL Tables on the Database Server.

Email Reporting

Go from simply having a data store to getting emails about your data. We have two HTML report views TABLE and LIST and a new Ouvvi Email handler than can consume a Web Page as the body of an email. By combining these we have a new powerful way to import data into an Ouvvi App and then send this as a report by email.

We've been using this loads! One of the ways is for when we receive a new order. The data we get from our reseller wasn't useful enough for us and we kept having to log in to find out the information we needed. So we utilised Data Sync + Ouvvi Apps and now we get an email every time we get a new order with all the information we need to know.

New Order Email

We're going to follow up with some more detailed blogs in the future but for now this is a heads up for what is coming in the next release.


Introducing the Data Sync Connector for Pipedrive

The Pipedrive CRM system connector

30 August 2019

We’ve recently begun using Pipedrive as our main CRM, so naturally thought we should build a connector for this!

So let's tell you a bit more about how you can connect and use the Pipedrive REST API.

The Connector

The Pipedrive connector is available from version 3.0.1228, and has full read and write, lookups and connection library capabilities.

To use it you will need your API token from Pipedrive, get this by going to Settings > Personal > Other > API. Then you simply choose the list you want to connect to.

Pipedrive Connector

What can you connect to?

The connector can connect to the Activity, Deal, Note, Organization, Person, Pipeline, Product, Stage, and User lists.

If you want to test what fields will be returned for each list you can do so at the Pipedrive API reference page. Enter in your API token, choose an endpoint to test and then click GET. This will then return a sample of your data stored in Pipedrive.

How might you use this connector?

Maybe you have Pipedrive but don’t know where to get started. Let us give you a few examples of how you might use it, so then you can get up and running automating your Pipedrive integration.

Data Migration

The very first task we needed the connector for was to migrate from our old CRM system to Pipedrive.

We simply connected to the old CRM as the source, connected to Pipedrive as our target, mapped the columns we wanted to include and then clicked Synchronise.
As you have all probably found out by now, Data Sync doesn’t take long to run these sorts of projects, so within under a minute we were done.

Now we can go into Pipedrive and have our customer data to hand, joining the emails we receive to customers and deals in the system.

Adding Leads

You may receive a list of new leads from one of your departments that need to be uploaded into Pipedrive. This could be a CSV file, or maybe you connect directly to the SQL database where the data is added.
Just connect to your list, connect to Pipedrive, map the columns and sync.

Updating Records

Maybe you edit the user data within Pipedrive but then need to reflect these updates in your business systems. Or spin it the other way and the data is edited in another system, but these changes need to be reflected in your Pipedrive CRM.
Just follow the same process: connect to your source system, connect to your target (destination) system, map your columns, compare and sync.

You can automate the project by using either the run tool to schedule it to run at a set time or by using Ouvvi to apply event or time-based triggers. You could even have Ouvvi send you an email once it has run to let you know something has changed… We will be telling you more about these sorts of possibilities in an upcoming blog post so keep your eyes peeled for our Ouvvi Apps release post.

Want to know more?

So if this has got you thinking about all the possibilities you could do and you want to know more, send us a message and we can help get you started.


Data Sync Projects vs Traditional Blocky Workflows

Comparision of Data Synchronisation Studio Projects against other ETL Tools

5 July 2019

In this blog we will cover the difference between Data Synchronisation Studio and other Data Integration products that take a blocky single step at a time workflow approach.

Scenario

For this example lets take a simple scenario where you may receive a file daily into a mailbox you want to then take this file and save it to a folder and at the same time rename the file with today's date.

Typical Workflow

Below is an example of the steps you might take to complete this process.

Email Attachment Process Flow

The Data Sync Approach

In Data Sync we model our Source Data how we want it represented in the Target doing everything at once and in batch. Data Sync then works out for us what it needs to do to make the target the same as the source. We then run multiple Data Sync projects in a sequence to build more complex processes.

So in this example we wrap all this up into a single Data Sync project where we connect to an Exchange Mailbox and get mail for Today with a specific subject then map it to a Folder and rename the file before its written.

Filter Email for Today and Subject

This is done via Project Automation to update the project configuration at runtime to return email messages received today. To filter only messages starting with NEW LEADS we enter a value of NEW LEADS* into the FilterBySubject property of the source.

DataSourceA.FilterByReceivedDateTime = DateTime.Today.ToString("yyyy-MM-dd HH:mm:ss");

Extract Attachment and Save to Folder

This is done by just mapping the Filename from the Attachment to the Target Folder at the same time we use a Calculated Column to rename the file using today's date.

FORMAT("LEADS_{0}.csv", DATESTR(TODAY(), "yyyyMMdd"))

Data Sync Project in Designer

Data Sync Project

Next Process Import File

Next we might want to import the data from the file into our Target System this could almost be anything but in general the process is the same. We process each row one at a time looking up against the target to decide whether we need to add the row as its a new row or update an existing row with new data. This can get really complicated when our source data doesn't contain the required identifier in the target system. It can also be really slow to lookup each record one at a time.

Email Attachment Process Flow

Fortunately in Data Sync all this is really easy we just need to connect the source to our CSV file. We can use a wildcard for the path if we do not know the filename i.e. C:\Temp\Leads_Drop\LEADS_*.csv.

We then connect the Target and define the mapping between the two data sources. We need a Key column to identify the records this is used by Data Sync to calculate whether a record is new or is an update.

Internally Data Sync tracks the target identifiers so we can always update a record even when the source data doesn't include this value.

Data Sync Project

Bringing it all together

Finally we need a way to run these Data Sync projects in a Sequence. Configure a Schedule or real-time trigger and have a way to monitor the execution once the process is in production.

Ouvvi

Ouvvi is our solution for scheduling and monitoring the individual Data Sync projects. Within Ouvvi you define a project and add steps to execute in a sequence using flow control to manage which steps run based on a running status.

For our example here we add three steps.

  1. Download CSV File from Email.
  2. Import CSV file to SQL Table
  3. On Failure send an email report.

We can document each of these steps in Ouvvi to ensure we have some visibility on the defined process.

Once the solution is configured and tested we add a Trigger to run this at Start Of Day to ensure it runs automatically for us.

Projects configured in Ouvvi

Ouvvi Project

This is just a small fraction of what is possible with Data Sync+Ouvvi. Hopefully this helps you understand the difference between Data Sync+Ouvvi and other products and you can see how easy it is to use Data Sync+Ouvvi for your Data Integration processes.


Data Sync Avangate/2Checkout Connector

Avangate/2Checkout Connector for Subscriptions and Promotions

2 July 2019

We have created a new Data Sync connector for Avanagte/2Checkout REST V4 API.

This new Connector can read Subscriptions and Promotions from the API. With this new connector you can easily sync your subscription data in 2Checkout with your CRM System or SQL Database.

Avangate/2Checkout API docs are here https://knowledgecenter.2checkout.com/Integration/REST_4.0_Reference

Avangate-2Checkout Connector

Write operations are manual via Project Automation Events and calling the REST API directly.

For example if you wanted to synchronise promotion discount rates with your own internal system you might use the following code in Project Automation BeforeUpdateItem event to retrieve the current promotion json document, update the rate and then send the document back.

public override void BeforeUpdateItem(object sender, DataCompareItemInvariant item, object identity)
{
	var mapping = new DataSchemaMapping(SchemaMap, DataSchemaSide.DataSourceB);
	var toUpdate = item.ToUpdateItemDictionary(mapping);
		
	if(toUpdate.ContainsKey("DiscountValue"))
	{
		var helper = DataSourceB.GetWebRequestHelper();	
		var info = DataSourceB.GetDatasourceInfo();
		var url = info.GetAvangateItemEndpointUrl((string)identity);
		// Get the Existing Promotion Details
		var promotion = helper.GetRequestAsJson(url);
		// Update the Discount
		promotion["Discount"]["Value"] = toUpdate["DiscountValue"].ToString();
		// Send it back
		helper.PutRequestAsJson(promotion, url);			
	}
}

Data Sync Mailchimp Connector

Import, Export Mailchimp Audience data and set Tags

1 July 2019

We have created a new connector for Data Synchronisation Studio to work with Mailchimp data.

With this connector you can Import and Export Mailchimp Audience Contacts. You can also update existing Contacts with any changes you may have in your database. This connector also supports Incremental Sync mode when you use the ID column as the Key column.

Mailchimp Data Sync Connector

When you prepare your data for the Mailchimp data import its a good idea to lower-case the email address this is to ensure that we can also calculate the MD5/id value that Mailchimp uses as the record identifier.

It's easy with a calculated column to Lower case the Email address with the LOWER(Email) function.

Mailchimp Lower Email Address

We can also generate the same MD5 hash from the email address that Mailchimp uses with the MD5HASH(LOWER(Email)) function.

Mailchimp MD5 Hash Email Address

To configure your project map your source columns to the Mailchimp columns and use the results of your functions to map Email and ID columns.

Mailchimp Data Sync Project

You can specify the contact status during the import by providing a text value for the status i.e. subscribed, transactional etc.

Its also possible to add/remove tags from contacts this is done by specifying an array of Tags to apply to the contact.

If your source data holds your tags in different columns like Tag1, Tag2 etc its quite easy to combine these into an Array with a helper function.

If you add a function to the Dynamic Columns class like this ARRAY function below you can then call this from Calculated columns with the function ARRAY(Tag1, Tag2)

public string [] ARRAY(params string [] values)
{
	return values.Where(p => !string.IsNullOrEmpty(p)).ToArray();
}

You can also extend the import further in Project Automation by intercepting the Item Events and calling the Mailchimp REST API directly with the help of the Data Sync HttpWebRequest helper.


Would you like to try Data Sync and Ouvvi for your Data Integration projects?

START YOUR FREE TRIAL