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:
'#Language "WWB-COM" ''MACRO TITLE: wReverseEng ' MACRO VERSION: 1.0 ' 'This macro reverse engineers a DB into a timestamped data model file ' ' Requirements: ' You must update the constants below to use the correct files ' A quick launch file previously saved from the Reverse Engineer Wizard ' '------------------------------------------------------------------------------ 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 Dim path$() Dim folder$ Dim att% On Error GoTo eQLP GetAttr(QUICK_LAUNCH_PATH) On Error GoTo eFolder ' Extract folder path = Split(PROJECT_FILENAME, "\") ReDim Preserve path(UBound(path) - 1) ' Check if folder is available folder = Join(path, "\") att = GetAttr(folder) ' folder NA If ((att <> vbDirectory) And (att <> vbDirectory + vbHidden)) Then Debug.Print "Folder '" & folder & "' doesn't exist." & vbCrLf & "Try to create it..." ' Create the folder MkDir folder End If ' folder available att = -1 att = GetAttr(folder) If ((att = vbDirectory) Or (att = vbDirectory + vbHidden)) Then ' 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 & ERDA DiagramManager.CloseDiagram(diag.FileName) End If Exit Sub eQLP: If Err.Number = 10051 Then Debug.Print "'" & QUICK_LAUNCH_PATH & "' doesn't exist." End If Exit Sub eFolder: If Err.Number = 10051 Then Debug.Print "'" & folder & "' doesn't exist." Resume Next ElseIf Err.Number = 10102 Then Debug.Print Err.Description & vbCrLf & folder End If 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.
Summary
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
If you want to reverse engineer a list of databases, I’d suggest to check this other post.
Bonus
A short video which shows how to create a macro from a script in ER/Studio Data Architect: