Problem
Many times our users ask how to automate installing ODBC PowerPack and also create ODBC DSN from command line or any setup script then you can use steps describe in this topic.
Solution
To automate installing ODBC Powerpack from command line follow these steps
Now lets look at how to automate creation of ODBC DSN.
Step-1: Create desired DSN manually as a template. Steps described here
Step-2: open regedit.exe (you can type regedit on command line)
Step-3: Navigate to ODBC DSN node like below.
NOTE: If you like to create DSN which can be used by Service Account or any user on the machine then create System DSN (if 64bit process use 64bit else use 32bit)
For User DSN (32/64-bit both) go here: Computer\HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\
For System DSN (64-bit) go here:
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
For System DSN (32-bit) go here:
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBC.INI
Step-4: Right click on the DSN node you like to export and click export. Save to local path e.g. c:\temp\my-dsn.reg
It creates a registry file like below if you open in text editor. Feel free to edit.
if you double click this file it imports back to same location where you exported from. So becareful if you edit and double click. It only changes that one node only which you exported.
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\My-DSN]
"CustomObjects"="{\"StoredProc\":{},\"VirtTable\":{},\"RecentObjects\":{\"ExecutedQueries\":[]}}"
"Driver"="C:\\Program Files (x86)\\ZappySys\\ODBC PowerPack\\ApiDriver86\\ZappySys.Odbc.Api.dll"
"Settings"="{\"Connection\":\"ServiceUrl=https://services.odata.org/V3/Northwind/Northwind.svc;ConfigFile=;Provider=OData;AuthName=Http;UserName=;ClientId=;RefreshTokenFilePath=;Password=;ClientSecret=;AccessToken=;RefreshToken=;Parameters=;UseEmbeddedConfig=False;TrustedDomains=;RetryMode=None;RetryCountMax=3;RetryWaitTimeMs=2000;RetryMultiplyWaitTime=False;RetryMessageList=;RetryStatusCodeList=;ConnectionStringFile=;RetainSameConnection=False;UseClientCertificate=False;CertificatePath=;CertificateStorageType=CurrentUser;CertificateStoreName=My;CertificateThumbprint=;CertificatePassword=;UseProxy=False;ProxyUrl=;UseProxyCreds=False;ProxyUserName=;DoNotUseDefaultProxy=False;ProxyPassword=\",\"ColumnMetas\":[],\"CachingMode\":1,\"CacheEntryTtl\":60,\"LogSize\":50}"
Step-6: Now its time to automate importing this file.
For very simple example try below command on command line
Call reg import from DOS batch file
reg import c:\temp\my-odbc-dsn.reg
Call reg import from PowerShell script file
powershell.exe -ExecutionPolicy Unrestricted -File "C:\Scripts\Restore-DSN.ps1"
in the Restore-DSN.ps1 you can have something like below
® import "c:\temp\my-odbc-dsn.reg"
Video Tutorial - How to create ODBC DSN for API Driver
Conclusion
In this post you saw how easy it is to automate creation of DSN for ZappySys ODBC Drivers or any 3rd party ODBC Driver DSN. If you encounter any challenges or have specific use cases, please contact our support team via chat or ticket.