Data migration from MS SQL server into Data tables and Things

[avaidya1]

Hi All,

We are developing a project in TW. We need to migrate the data from existing system into the target Data table and Things in new system.

As there is no direct access to underlying table(s) in TW, owe are trying via Import/Export menu.

To know the format we exported the data of sample Datatable , but its coming in encrypted format.

úŒschemaVersionB940„buildCb159„start#‹major# f@7‹min  `4‡revi= ð @0ûúƒ ....

How  can we proceed with the same ?

What is the preferred way in ThingWorx for the same ?

Best Regards,

Aniruddha

[supandey]

Hi Aniruddha, is this a hard requirement to move the entire MS SQL Server DB data to ThingWorx? I mean you can always add your existing MS SQL Server as an external data store to ThingWorx via RDBMS connector this way you can continue using your current setup and can still access all the data in ThingWorx via the SQL Scripts.

If you really want to get all the data out from MS SQL Server to ThingWorx's own persistence provider, i think you still can do what I mentioned above and then via the SQL Scripts you can query the data and then write them to the DataTable. Obviously this would also require you to model your Data within ThingWorx for e.g. by creating DataShapes and DataTable.

Hope this helps.

[avaidya1]

Hi Sushant,

Thanks for reply.

Connecting MS SQL server as external Data store to ThingWorx is not feasible, as it is production DB.

We can export data from the MS SQL server using a tool/script to a file, but how to  import the same into the TW?

As mentioned above seems data for import has to be in a encrypted format, can it be in plain text format? if yes can you give sample data file for a dummy datatable with (name(str), age(int), dob(datetime)) with 1/2 records for import.

Thanks and Regards,

Aniruddha

[supandey]

Aniruddha, just to clarify this existing old system which you are trying to export and then import to ThingWorx is that also ThingWorx?

[avaidya1]

No, it is Dot Net system using MS SQL server

[supandey]

In that case as you can already see that there is no 1:1 mapping in the data model or the logical model so as i mentioned in my 1st reply you will have to map them by Modeling the data in the ThingWorx.

[avaidya1]

Hi Sushant,

To Model/export SQL data (table) into  equivalent TW Data shape, via TW import from file,

I am looking for sample of the data file format, is it a JSON format or some thing else ?

can you provide a sample it will help.

[supandey]

Aniruddha, as to the import it's working with JSON and XML via the Import option with ThingWorx. You can already see the sample if you export any entity out from the ThingWorx one with Data and another without data.

But regardless of that I hope you see that there is no straight forward way of simply exporting entire SQL schema and importing that into ThingWorx. You will have to model your data by creating entities like DataTable, etc. And then instead of doing export and import, i'd say you can use the mirror system from your existing production SQL Server which is then connected to ThingWorx as external data store via the JDBC extension and then query the table(s) at once this way you can write to the already modeled entities within ThingWorx.

It would be even more straight forward if you can simple use that mirrored DB connected to ThingWorx as external data store.

[avaidya1]

Hi Sushant,

Thanks for reply. As you suggested above, already exported a sample Datatable (Entity) with data. To my surprise the data was in encrypted format. Hence impossible to generate from MS SQL DB (unless I know the encryption method and key of TW), hence the question is it possible to import file with data in plain text or it is always encrypted? if Always encrypted what is Encryption method and key to use? ( Refer my first mail)

Br,

Aniruddha