Migrating Rocket UniVerse to SQL Server using Rocket driver and SSIS


UniVerse® is an extended relational data server optimized for embedding in vertical applications.

UniVerse offers a nested relational (MultiValue) data model which means intuitive data modeling and fewer resulting tables. UniVerse V11.1 adds powerful high availability, interoperability, administration and security capabilities to the solid extended relational database.

The Rocket UniVerse Oledb driver provide a data interface to dsn , and this can be easily done using an ADO.Net data source in SSIS. The UniVerse files will be visible as normalised table structure and can be loaded through separate pipeline.

World of multivalue database : UniVerse and SSIS Integration


What is a multivalue database ?

MultiValue is a technology that understands three dimensional data structure. The 3 dimensions can be referred as Fields, Values, and Subvalues.

A field( column) is the same as in a normalized database. A Value is a further breakdown of a column. For example, in a normalized database there might be columns defined for Address1, Address2, Address3. In a MultiValue database there would be a definition for a column named Address, and stored in that column would be either one, two, three, or more values. These different values would be delimited by a special character known as a Value Mark. A Subvalue is a further breakdown of a value. For example, if there is a column defined for Phone, and there is a value called Home, there may be two sub values for the home phone number- perhaps the main number and a home office phone number.

Another distinct advantage to the MultiValue world is that the tables are extremely flexible. Columns can usually just be added to the database definition and used immediately. There is no need to shut down the database , lock out the users, add the column, and rebuild the database. A new column is simply added to a dictionary and that column is then immediately available.

MultiValue databases also have calculated columns. These columns, which actually contain small programs that are run when the fields contents are needed, allow real-time calculation of values. For example, in a MultiValue database a person’s age wouldn’t be stored, the person’s birth date would be stored. There would be a column named AGE, but it would be a calculated column. The calculation in the column would take the current date, subtract from it the person’s date of birth, and then display the age.

UniVerse database

UniVerse developed by Vmark Systems. In 1997, the Unidata Corporation merged with VMark Systems to form Ardent Software.Later Ardent Software was acquired by Informix. In 2001 April ,IBM has acquired the database division of Informix making UniVerse as a part of IBM’s DB2 product family.

On the 1st of October 2009 it was announced that Rocket Software had purchased the entire U2 portfolio from IBM. The U2 portfolio is now grouped under the name RocketU2.

MultiValue Query Language

Each query is issued against a single dictionary within the schema, which could be understood as a virtual file or a portal to the database through which to view the data.

LIST PEOPLE LAST_NAME FIRST_NAME EMAIL_ADDRESSES WITH LAST_NAME LIKE "Van…"

The above statement would list all e-mail addresses for each person whose last name starts with "Van". A single entry would be output for each person, with multiple lines showing the multiple e-mail addresses

Bluefinity (bluefinity.com) Multivalue components for SSIS

The set of tools will give cross product data from U2 file and will be available to stage.

mv.NET – MultiValue & Microsoft .NET Integration
mv.NET is a comprehensive solution for accessing MultiValue databases from within Microsoft’s .NET environment. It enables software designers to combine the power and flexibility of proven MultiValue technology with the feature rich .NET environment.

mv.SSISMultiValue SQL Data Integration
mv.SSIS allows MultiValue users to extract data from any major MultiValue database (in both bulk and filtered export mode) to other data repositories using Microsoft’s SQL Server Integration Services (SSIS) product. The definition, testing and execution of these extraction processes is performed entirely from within the standard SSIS environment and ensures data consistency, eliminates redundant data and saves time.