Select Page

LGSS Digital Blog

Service design for local government

Ben Thompson

Lead Developer

Data migration with Outsystems

Outsystems is a low code platform with drag and drop functionality and scalable architecture. It’s great to use when developing applications while upskilling a team, or when you might want to reuse products or parts of products for different councils.

This blog post covers the best way to migrate data with Outsystems. You probably need to be a dev to read it.

So, how to migrate? It’s easier, faster, and better to use SQL server but there are caveats.

The traditional approach

The recommended route for importing data into Outsystems is not an elegant one.

1) Right-click on an entity and choose Create Action to Bootstrap data from an Excel. This will create a new Action that will process the contents of an Excel file that will be read from a resource.

2) Run the Action from a Timer that is also created after you have published the project. If you want to make the entity static then you will have to remove the ‘bootstrapping’ code.

3) At this point you might wonder about the term ‘bootstrap’ which is defined as ‘a self-starting process that is supposed to proceed without external input’… this process is anything but self-starting. The process is so onerous that often, we’ve found it literally faster to input smaller tables’ data by hand.

Outsystems SQL server

Fortunately, there is a better way. Outsystems will, after a support call, provide limited access to the underlying SQL server. You will need to provide a list of IP addresses that are whitelisted. We actually add an extra step where the SQL connection is tunnelled through SSH.

Once you are connected to SQL server, you can begin to understand the underlying Outsystems data-model.

The entities that you create will be placed in tables that will be prefixed with OSUSR_ after that there will be an (apparently random) three letter prefix, such as BIR_, and finally there will be your entity name in upper case. For example ‘Customers’ might become OSUSR_BIR_CUSTOMERS.

In addition to the base table, there will be multiple views per table with numbers to differentiate them, for example OSUSR_BIR_CUSTOMERS_22. These can be ignored.

Inserting your data

DML access; you can’t create, alter, or drop any objects.

At this point, you can INSERT the records you are importing. One important thing to bear in mind is that after you have inserted records into Static Entities, you may need to click Import from Database to create the records!

There is some limited access to temporary tables, but we found that it worked well to add new intermediate entities to represent the tables you needed in the database. For example, you can create a ‘CustomersImport’ table which will be reflected in the database by OSUSR_BIR_CUSTOMERSIMPORT.

Intermediate import tables

When you create intermediate tables, it’s often sensible to add all the columns you’ll need but to create them with a String datatype. This will make the bulk import of data from text-based data formats (e.g. CSV) easier. For larger and more complex migrations, these tables were useful for what comes later.

If you try to import dates, floats, etc. directly into those datatypes, you will rely on the default conversion routines which will often fail.

The source data

The source of your data can cause problems. CSV files are perhaps the most simple and most portable way of storing tabular data. However, there is no common standard for how to format these files and different providers will take different approaches. Some common problems are:

– Floating point numbers are always tricky. Try to avoid very long entries that come from rounding errors that lead to things like 3.4 being represented as 3.39999999999. These errors can be rendered in different ways, either as a long string or with different notation such as 1.48E06

– Date formats will often be interpreted differently. The safest option is the ISO date format of YYYY-MM-DD

– Escape characters, new lines, and string delimiters can vary a lot. The best answer is not to use CSV. NULL handling is usually not available. Excel 2007 onwards is usually a safer bet because it has some limited datatyping and handles delimiters, etc. It still can’t really handle NULLs, though. Excel 97-2003 can’t handle more than 64,000 rows so be cautious about using that. That being said, Excel can still suffer with floating point numbers.

Data held in SQL server should fit like a glove and should be the easiest to migrate from.

 

Importing the data

This is usually the most repetitive step. You can set up a transformation package if you really want, though.

SQL Server Management Studio is now freely available as a separate download for Windows and unlike the otherwise powerful Azure Data Studio, it includes support for SQL Server Integration Services via the super easy-to-use Import/Export Wizard. The wizard is also available in some editions of Visual Studio.

To get started, right-click on the database and choose Import Data.

Then, set up the datasource. There are some special handlers for some file types (pictured is the Excel handler) or you can use any ADO or ODBC driver. 

Setup the data source

The next page will be for the data destination and this will be the Outsystems database.

The page after that will ask whether you’re copying tables or writing a query. Unless you have a compelling reason to write a query, choose table copy.!

Choose copy data

Next, you select the table mapping. In Excel documents, you can migrate multiple sheets in one go which is a bit of a time-saver as this process can be a bit fiddly.

By default, the wizard will enter a new table name. If you leave it like this, the import will fail. You’ll need to enter the fully qualified table name with the dbo prefix.

You can also use the drop-down list but it will display a *very* long list of tables and views!

Table mappings

After this, you can press Finish >>| (and then Finish again).

Hopefully, everything will run through properly but sometimes data formatting can cause problems.

Data transformation

You can then use your favourite query analyser (check out the SQL 2000 reference) to run a SQL script to clear down any existing data in the database and insert new data.

As part of this you can write a SELECT statement to control how data is formatted or converted.

An example migration script.

Let’s finish with part of a migration script from a recent project: