Script the creation of ODBC DSNs
Automate the creation of ODBC data source names (DSNs) with a PowerShell script.
The aim of this script is to apply a complete set of DSNs on a server, regardless of the previous state. The script begins by deleting existing DSNs from the server; after that it creates the DSNs anew.
To avoid deleting absolutely every DSN on the server a wildcard delete is performed using the standard project key taken from the naming convention. In my naming convention the first part of the data source name identifies the data warehouse project. If there aren’t a lot of warehouses then DWH_ is a good prefix, with DSNs following this pattern
# DWH_<data-source>_<environment> DWH_crm_DEV
This allows the delete statement to use wildcard of DSN*.
DsnType and Platform
ODBC data sources are created in the User or System DsnType. Entirely separate data sources are also created for the 32-bit and 64-bit platforms (each of which has its own ODBC manager snapin interface). Therefore both the DsnType and Platform normally have to be specified when removing DSNs. Platform can be omitted when when adding DSNs since the default platform will be used, but in this case being explicit is the best option.
In data warehouse projects, DSNs will usually be 64-bit Platform and System DsnType.
PowerShell script
The important parts here are the deletion statement and the naming conventions. The Add-OdbcDsn statements are standard, and depend on the driver used.
# run PowerShell as administrator
# Port defaults to 443
# 64-bit Platform. System DsnType.
# Delete all existing System DSNs with name like "DWH*"
Remove-OdbcDsn -Name "DWH*" -Platform "64-bit" -DsnType "System"
# CRM data sources
Add-OdbcDsn -Name "DWH_crm_DEV" -DriverName "SQL Server Native Client 11.0" -DsnType "System" -Platform "64-bit" -SetPropertyValue @("Server=MyServer", "Trusted_Connection=Yes", "Database=CRM_01")
Add-OdbcDsn -Name "DWH_crm_UAT" -DriverName "SQL Server Native Client 11.0" -DsnType "System" -Platform "64-bit" -SetPropertyValue @("Server=MyServer", "Trusted_Connection=Yes", "Database=CRM_01_UAT")
Handling non-standard drivers
Several ODBC values are set using the PropertyValue array. The contents of this array are well documented for the common drivers. For non-standard ODBC drivers an approach to understanding the PropertyValue array is to create a working ODBC DSN using the user interface then investigate it using:
Get-OdbcDsn -Name DWH_crm_UAT | Format-List -Property *
This will return all the key=value pairs of PropertyValue – but often truncated as below:
PropertyValue : {DBQ=C:\Program Files (x86)\CodeChargeStudio5\Examples\Intranet\Intranet.mdb, DriverId=25, SafeTransactions=0, ImplicitCommitSync=...}
Alternatively, this snippet of code provided by thecliguy will iterate all properties of each DSN entry and output their full values:
Get-OdbcDsn | Format-List -Property Name, @{N='PropertyValue';E={($_.PropertyValue | Out-String).Trim()}}
The keys may not always match the names displayed in the ODBC DSN editor form, so trial and error may be necessary to trace a value entered into the interface to its key=value representation.
References
with thanks to thecliguy
A note on the ODBC icon
The windows 10 ODBC Data Source Administrator icon is an Excel or table-like grid, the standard symbol for data, with a rather wide briefcase in front of it. I suppose this is intended to indicate portability, although the grid doesn’t look like it would fit the briefcase.
A different icon appears when creating a new data source. All the data signifiers are there: a yellow database cylinder, an Excel-like grid and a page of 1s and 0s. The database cylinder is plumbed into a network using normal pipework and brass connectors. It’s connected data.
In XP the aspirations were greater for ODBC: the Administrator icon was an Excel grid with a globe in front of it promising direct connectivity with data across the world, in contrast to the rather cumbersome current process involving a briefcase.