How to transfer table data from one Innovator instance to another fast and easy?

Hi community,

I have two Innovator instances. Both have different purposes and unique applications.

I want to transfer one of the custom applications (incl. ItemType, Form, Method,..) AND the existing data from one instance to another.

The application itself is no big problem. We can create a PackageDefinition and use the existing Export/Import tool for transfer.
But what about the data?

In theory we could also create a PackageDefinition for the data. But the generated package do not contain all item properties. The exported items do not contain important properties like created_on/modified_on, permission_id, id, config_id, current_state, state, etc. .

But it´s important for me that the data is transferred with all of these properties.

How would you solve this task? There are many possible ways, but which one would you use?

My current ideas:

- SQL Insert select?
- CSV SQL export/import?
- AML export/import?
- transfer via IOM/REST connection?
- Innovator Admin?
- BatchLoader?
- Shell script?
- SQL script?
- Hire intern that type in the data manually?
- Something else?

I want to avoid using Excel or CSV as transfer medium. I want to use a concept that is reusable and maybe can be automated.
In addition I have to modify some of the data before import. These should be integrated into the process.

Any ideas and thoughts welcomed! Smiley

Thanks!

Angela

  • You can explore about the data synchronization service in ARAS (Theoretically knowledge but I didn't used the DSS yet)

  • Hi Gopi,

    thanks for your post!

    I once tried the DSS in another project. It killed the database and made it impossible to edit Parts.FireFireFire

    The current DSS version modifies a some of the default ItemTypes. So use it with caution. It´s a pretty complex project, I didn´t spend much time to understand how it works.

    Regarding this topic I will probably use a mix of AML and SQL scripts. I will temporary copy the 2nd database to the main server. Then prepare the scripts for the data transfer and make a few test runs. When everything is fine, I make the cutover.

    Basically a similar approach the Aras update team uses.

    Scripts have the advantage, that I can include the required item modifications (link correct users identities, etc.). It requires a lot of preparation, but cutover can be reduced to minimum.

    Nevertheless, I haven´t started yet. So I am still happy for any tips!

    Angela

  • Angela,

    I would like to hear how this goes for you.  I am interested in this as well.

    Nate

  • Basically this one is a test balloon for my own update concept.Sunglasses

    When we one day get a new open release, I at least want to try to update. Of course this is easier said then done. With my current main database, I probably need 2 years of preparation (and cleanup) to cover everything.

    But I made first tests and was able to transfer some Users+Alias from one db to another with SQL. It´s actually a pretty dumb concept. 

  • Angela,

    I would love to see how you are doing this.  I am currently refactoring our database, moving parts and documents from custom made ItemTypes to the OOTB ItemTypes.  I have reports to gather the information I am looking for, then I put that into an Excel spreadsheet, then use the spreadsheet to do a mass import.  It works, but it is a time consuming process (the reports can take a long time to run, if I am gather too much data they will crash).

    Nate

  • Hi,

    If this is a one time job and data model is complex you can try to create simple console app in c# using IOM with connection on both Aras instances.

    Do a get and merge logic and error handling. Than you can run in debug mode to catch errors and solve them on the fly.
    I have done similar cases in the past and it turned out to be a good choice.

    However any technology can do the job but at the and it depends on which technology you are most familiar with.

  • Hi Jan,

    sounds like a promising idea! I assume you use AML in your console app. Are you able to preserve the created_on/modified_on properties this way?

    I have done some similar approach to duplicate a List item with AML. Most properties like "id" were preserved, but unfortunately not the ones related to created.../modified... 

    @Nathan:  Regarding the SQL approach. Note that the following is just based on some test and could be wrong. The most basic query to transfer one database entry to another database looks like this. It requires that the database can access the second database (same server/linked server)

    INSERT INTO [FirstDatabase].innovator.[USER] ([KEYED_NAME],[TEAM_ID],[CLASSIFICATION],[CELL],...)
    SELECT [KEYED_NAME],[TEAM_ID],[CLASSIFICATION],[CELL],...
    FROM [SecondDatabase].innovator.[USER] WHERE keyed_name = 'MyTestUser'

    The above query will create a user entry, BUT not a valid user item, cause the Alias item is missing. 
    It´s important to add all properties in the query in correct order, otherwise there is the risk that certain properties will be mixed up. Happened to me, even if the two databases where identical and based on the same Innovator database.

    The solution has a couple of pros&cons.

    Pro #1: Speed and powerrr!!!
    Con #1: It´s obviously super dangerous. AML protects your database and will prevent dangerous queries. SQL will not. 

    Pro #2: Data is copied 1:1
    Con #2: Only one table at once can be filled with INSERT INTO. Regarding the List sample mentioned above, I would have to make two separate calls for all affected tables. 

    Pro #3: Data can be edited on the fly
    Con #3: You really need to understand SQL and the data model of Innovator, otherwise you will destroy your db with a probability of 300% minimum .

    Pro #4: Script can be prepared
    Con #4: Can be used for plain data tables, but probably not for editing Innovator client itself (changes would have no effect).

    But I haven´t done much testing yet. If somebody remembers old Top Gear episodes. INSERT INTO is equivalent to the "everything can be fixed with a hammer" approach of Clarkson. So I wouldn´t call it the perfect way to address the topic :D

  • Angela,


    When I need to preserve properties like created_on/modified_on the only way is applySQL. However I use SQL strictly only for updating these properties. So I usualy do logic in folowing steps:
    1. merge item with AML
    2. get id of applied item
    3. apply an SQL update statement just for these 2 properties

    I believe that such a combination is the safest one.