This blog post describes how you can run a macro to reverse engineer a database and call it from the Windows Task Scheduler.
When you use ER/Studio Data Architect, you can easily create a new data model based on an existing database by using the Reverse Engineer Wizard from the User Interface.
In the last step of the wizard, you can save the settings to a file for later reuse.
If you have previously saved such a file, you can load it with the Wizard Quick Launch to get back all your previous settings.
So, it’s very easy to configure the reverse engineer once, and to manually replay it when we need it.
Would it be possible to automatize this reverse engineer? Not directly, but we can run an ER/Studio Data Architect macro from command line, and this macro could reverse engineer our database. So here is the macro I coded to achieve it:
'TITLE: wReverseEng.BAS 'DESCRIPTION: This macro reverse engineers a DB into a timestamped data model file Const PROJECT_FILENAME = "<YOUR_PATH><FILENAME>" Const QUICK_LAUNCH_PATH = "%appdata%\Idera\ERStudio\XML\<QUICK_LAUNCH_FILENAME>.rvo" 'ER/Studio Variables Dim rvo As ReverseEngineer Dim diag As Diagram Dim sProjectPath$ Option Explicit Sub Main Debug.Clear ' Timestamp & extension added sProjectPath = PROJECT_FILENAME & Format(Now, "_yyyy-mm-dd_HH-mm-ss") & ".dm1" ' Create the Reverse Engineer Object Set rvo = DiagramManager.ReverseEngineerObject ' Load the settings from the Quick Launch save rvo.QuickLaunchFile = QUICK_LAUNCH_PATH ' Reverse engineer the database Set diag = rvo.DoReverseEngineer ' Save the project diag.SaveFile(sProjectPath) ' Close the newly created project DiagramManager.CloseDiagram(diag.FileName) End Sub
To use the above macro, you need to update the constants to match the path to your DM1 file and to your Quick Launch settings.
Then to automatize it, we can schedule a call to this macro using the Windows Task Scheduler.
Launch the Task Scheduler and Create a Basic Task…
Define when you want the task to start:
Choose the Action, Start a program:
Click the Browse… button and select the ER/Studio Data Architect application. By default: “C:\Program Files\IDERA\ERStudio Data Architect <VERSION>\ ERSTUDIO.exe”
Add arguments: -m “<PATH TO THE ABOVE EDITED MACRO FILE SAVED IN YOUR ENVIRONMENT>”
Review the summary and click the Finish button to create your scheduled task.
Finally, the task will run when it’s scheduled and your project files will be generated.
In this blog post, we used:
- the ER/Studio Data Architect Reverse Engineer Wizard
- to save its settings with the Quick Launch
- the ER/Studio Data Architect COM automation interface through a macro
- to reverse engineer a database using saved settings
- the Windows Task Scheduler