How to forward engineer SQL scripts from Microsoft Visio

maxresdefault (1)Back in the early noughties, Microsoft Visio used to fully support both reverse and forward engineering of database entity relationship diagrams: you could choose to hook up Visio to an existing database and generate an ERD automatically, or alternatively create an ERD manually in Visio and then pipe out the results as a SQL script of DDL statements. Sadly, when Microsoft released Visio 2010, they discontinued the forward engineering component, and with later releases they have done away with reverse engineering as well. If, like me, Visio is often the only licensed product available on your computer to use for making ERDs, it can be rather frustrating to go through the process of setting out your diagram only to then have to manually write the SQL to create the physical tables. To make matters worse, versions of Visio earlier than 2010 no longer function on modern Windows operating systems like Windows 10.

However, all is not lost: if you are able to get a copy of Microsoft Visio 2010 (which is available through the Microsoft Visual Studio Subscription service) you can not only continue to use its existing reverse engineering functionality – you can also bolt on a bit of freeware to enable forward engineering as well! Alberto Ferrari created a handy C# project that utilises existing Visio libraries to add an extra ‘Forward Engineer’ menu within Visio 2010, and you can freely download that project here.

The story doesn’t end here, however. The project is hosted on a now-defunct code sharing platform called CodePlex, and somewhere along the line the original setup.exe file that you need in order to install the Forward Engineer plugin has gone astray. This means that when you download the archive from CodePlex, you only have access to the original source code. In the steps below, I will explain what you need to do in order to re-generate the setup file and thus be able to install the Forward Engineer plugin for Microsoft Visio 2010…

  1. Ensure that you have installed Microsoft Visual Studio 2010 with Service Pack 1. Again, if you have a Microsoft Visual Studio Subscription, you can easily get hold of this.
  2. Download the ForwardEngineer zip file from the CodePlex archive. In order preserve the project for future prosperity, I have attached it to my blog as well (just in case CodePlex itself disappears all together!)
  3. Extract the zip to a temporary folder, and then navigate inside that folder to the subdirectory called ‘sourceCode’ and unzip the zip file inside (which is also called sourceCode.zip)
  4. Now navigate into the unzipped directory to a subfolder called v1 and drill down again into Visio.ForwardEngineer
  5. In here you should find a Visual Studio solution file called Visio.ForwardEngineer.sln which you can open in Visual Studio 2010 (ignore any messages about “going offline”, just click OK)Annotation 2020-04-22 171135
  6. In the Solution Explorer pane, right click on the Visio.ForwardEngineer project and choose ‘Properties’Annotation 2020-04-22 155142
  7. Select ‘Signing’ and click on the ‘Create Test Certificate…’ buttonAnnotation 2020-04-22 155142
  8. Put in a dummy password (it’s not important what you use)
  9. Now make sure that you select the certificate you have just created in the ‘Choose a strong name key file’ dropdown instead of the default ForwardEngineer.pfxAnnotation 2020-04-22 155142
  10. From the Solution Explorer, expand the ‘References’ section of the ForwardEngineer project and look for MODELENGLib (it will have a small yellow exclamation mark next to it)Annotation 2020-04-22 171135
  11. Right click on it and choose ‘Remove’
  12. Minimise Visual Studio 2010 and open a new File Explorer window
  13. Navigate to the following directory: C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin and confirm that you can see the TlbImp.exe file in that directoryAnnotation 2020-04-22 171135

     

    (if you are unable to find this directory on your computer, you will need to install the Microsoft Windows SDK for Windows 7 and .NET Framework 4 pack, which you can get here) 

  14. Next, navigate in File Explorer to the following directory: C:\Program Files (x86)\Microsoft Office\Office14 and confirm that you can see the MODELENG.DLL file in that directoryAnnotation 2020-04-22 155142
  15. Right click on that file and choose ‘Copy’
  16. Navigate in File Explorer to C:\Temp (create this directory if it doesn’t already exist)
  17. Once in the temporary directory, paste your copy of MODELENG.DLL
  18. Rename the file to MODELENG_ORIG.DLL (Right click > Rename)
  19. Open a new Command Prompt window (press ‘Start’ and type ‘cmd’ to find it)
  20. In the Command Prompt window, type cd “c:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin” and hit Enter
  21. Now type the following command and hit Enter:
    
    TlbImp.exe C:\Temp\MODELENG_ORIG.DLL /noclassmembers /out:C:\Temp\MODELENG.DLL
    

    You should see the following output confirming ‘Type library imported to C:\Temp\MODELENG.DLL’:
    Annotation 2020-04-22 171135

  22. Now return to the ForwardEngineer solution in Visual Studio 2010
  23. From Solution Explorer, right click on the ‘References’ section underneath the Visio.ForwardEngineer project and choose to ‘Add Reference…’Annotation 2020-04-22 171135
  24. Click on the ‘Browse’ tab at the top of the window, navigate to C:\Temp, select the MODELENG.DLL file (not the MODELENG_ORIG.DLL file!) then click OKAnnotation 2020-04-22 171135
  25. You should now see MODELENG.DLL sitting happily in the References tree of the Solution Explorer without any yellow exclamation mark next to itAnnotation 2020-04-22 171135
  26. Right click on the Visio.ForwardEngineer project in Solution Explorer and select ‘Rebuild’. The compile will instantly fail, but this is OK, because you’ll need the error message to direct you to the line of code to update (see next step)Annotation 2020-04-22 171135
  27. From the Error List in the bottom pane of Visual Studio, double click on the first error in the list; this will open a file called Visio.ForwardEngineer.DataModel.ModelTransformerAnnotation 2020-04-22 171135

     

  28. Amend the second line of the file to change MODELENGLib (which is underlined with a red wriggly line) to just MODELENG
  29. Click on the ‘Save All’ icon on the ribbon bar at the top, and then repeat step 26 to rebuild the project a second time
  30. This time around, you should get a nice, clean message in the Output pane that says “Rebuild All: 1 succeeded, 0 failed, 0 skipped”Annotation 2020-04-22 171135
  31. Go back to File Explorer and navigate to the folder where you originally unzipped the ForwardEngineer project. Go to the following subfolder: \sourceCode\v1\Visio.ForwardEngineer\Visio.ForwardEngineer\obj\Debug
  32. Select the files from the Debug folder and copy themAnnotation 2020-04-22 171135
  33. Navigate to the following subfolder: C:\Users\954531\Downloads\ForwardEngineer\sourceCode\sourceCode\v1\Visio.ForwardEngineer\Visio.ForwardEngineer\obj\Release
  34. Paste the files into this folderAnnotation 2020-04-22 171135
  35. Now return to Visual Studio, locate the Visio.ForwardEngineer.Setup project in Solution Explorer, right click it and select ‘Rebuild’Annotation 2020-04-22 171135
  36. This time you should receive a nice message in the Output pane indicating “Rebuild All: 2 succeeded, 0 failed, 0 skipped”Annotation 2020-04-22 171135
  37. Now return to File Explorer and navigate to: C:\Users\954531\Downloads\ForwardEngineer\sourceCode\sourceCode\v1\Visio.ForwardEngineer\Visio.ForwardEngineer.Setup\Debug
  38. In this directory you will find two files: setup.exe and a Visio.ForwardEngineer.Setup.msiAnnotation 2020-04-22 171135

     

  39. Double click on setup.exe to install the Forward Engineer plugin for Microsoft Visio 2010!

Once you have installed the plugin, you will now find that when you open Visio 2010, you have an additional menu on the ribbon bar:

Annotation 2020-04-22 171135

Now, when you create a Database Model Diagram, you can use the ‘Forward Engineer’ button to generate a SQL script with all of the DDL commands to physically create the data model in a SQL Server database.

A big thank you from me to Alberto Ferrari for originally creating this excellent feature for Visio. Microsoft should definitely hire him, if they haven’t already!

One comment

  1. Jiangwei Wang · · Reply

    Colin, Thanks for this article! Wonder if you may attach the downloadable setup.exe and Visio.ForwardEngineer.Setup.msi files. I wanted to go through the steps but I don’t have VS installed on my machine so I’m not able to get the installation files. Really appreciate if you may help! Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: