Scheduled dataset tag to database

What is the best way to insert information from dataset tag to a database on a scheduled basis.

This is a dataset from an external system that is parsed together into a dataset tag. I want to run a weekly update to a database.

You can set up a scheduled script in the newer versions under the Gateway Events part of your project to run once weekly. From there you can read your dataset tag, iterate through the rows and run your appropriate SQL.

Would this be something system.db.refresh would be used for?

Scheduled Gateway event scripts are not available in 7.9. Consider creating an expression tag that goes true at specified times, then use that to trigger a gateway tag change script.

That function is to refresh a property. Not useful for what I think you're tryng to do.

Are all of these going to be inserts, or are there some updates, as well?

1 Like

It would be updates and inserts. It is an employee database with roles that will get updated and adding new employees would be an insert.

Have you looked at the User Management component?

I don't think this would work the way we handle our employee information, but maybe I am missing something. Our ERP system is our main source of roles and users. I am attempting to pull the information from the ERP which is put into a dataset tag and push it into a database to use though other Ignition instances and other software.

Is this a separate user system than the one that clients use to login into the Vision client? If it is not and these users are the same usernames/passwords people use to login Ignition, then you're gateway is already interfacing with it via a User Source and @JordanCClark 's suggestion of a User Management component should work.

However, if its not, then do what he suggested - setup a tag change event on an expression tag that turns true at the moment you want it to run.

Then you'll want to iterate through this dataset (examples how - Datasets - Ignition User Manual 7.9 - Ignition Documentation) and per row run your INSERT or UPDATE query.

Copying from the documents it would be something like

ds = system.tag.read("your/path/to/DatasetTag").value
for row in range(ds.getRowCount()):
    # your logic here

I might suggest using PyDataSets as then you can unpack in the for loop and do something like

ds = system.dataset.toPyDataSet(system.tag.read("your/path/to/DatasetTag").value)
for (column1, column2, column3) in ds:
    # your logic here with column1,2,3 etc

You may find some inspiration from this thread.

An example window comparing two datasets.

If you look further odwn about six posts or so, you' see a script that can be used in that window to check for adds / changes / deletions.