How to fetch data with ODBC DSN connections using Powershell

[NadiaB]

This article describes basic steps on how to test a connection with ODBC DSN in Powershell.

Also see How to fetch data via ODBC DSN without using Qlik Products

Environment:

• Microsoft Windows Server 2012 and above

Resolution:

1. Configure a DSN connection using the ODBC Data Source Administrator
2. Open PowerShell  ISE
3. Use the following code and change values accordingly:

Get-Date
$dnsName= "DSNNAME"
$user="USERID"
$password="PASSWORD"
$csvPath= "C:\<path>\<filename>.csv"
$sqlQuery= "select * from DB.dbo.Table"

$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=$dnsName;uid=$user;pwd=$password;"#"dns=$dnsName;uid=sa;pwd=Password123!;"
$conn.open()
$command =$conn.CreateCommand();
$command.CommandText=$sqlQuery

$dataAdapter = New-Object System.Data.Odbc.OdbcDataAdapter $command

$dataTable = new-object "System.Data.DataTable"
$dataAdapter.Fill($dataTable)
$conn.close()

$dataTable | Export-csv -Path $csvPath -NoTypeInformation
Get-Date
Where:
DSNNAME is the name was configured in the ODBC Data Source Administrator tool
USERID is the user id will be use to connect to the database
PASSWORD is the password for the provided user id

• Click the "Run Script" button or press F5, verify the execution was completed successfully and open the path where the file was generated.

Use as reference both dates to verify how long it took to complete the query from the server/computer from where are you testing.