In my case I have 3 ODBC system DSNs that I need to setup as part of an install script for an application server. I want to automate the build of different environments.
So in the example below I am creating 3 System DSNs for a Test environment. They are called DB1_Test, DB2_Test and DB3_Test
I create an array $ODBCDSNsToCreate so I can go through a foreach loop to create them
To explain the Add-OdbcDsn cmdlet a bit , I am create 32 bit odbc DSNs, so have to use the -32-bit parameter. If you do not specify 32 bit , it will default to 64 bit.
For the -SetPropertyValue parameter It is an array to say what server to connect to, the description of the ODBC connection (which I add the environment variable into) and the database I want to connect to on the SQL server.
Write-host "Creating System DSNs" -ForegroundColor Green
#ODBC DSNs to create based on environment
$InstallEnvironment = "Test"
SqlServerToConnectTo = "Server1"
$ODBCDSNsToCreate = @("DB1_$InstallEnvironment","DB2_$InstallEnvironment",DB3_$InstallEnvironment")
#Create ODBC System DSNs
foreach ($dsn in $ODBCDSNsToCreate)
Write-host "Creating $DSN ODBC DSN"
Add-OdbcDsn -Name $dsn -DsnType "System" -platform 32-bit -DriverName "SQL Server" -SetPropertyValue @("Server=$SqlServerToConnectTo","Description=Application $InstallEnvironment", "Database=$dsn")