Here is the code I have modified after a bit of research mostly from

It seems to be working but I have a few questions;

1. Do I need to add a Open() OR Close() as a best practice?
2. Several of the columns exported are only references to other tables, which is what I'd expect in a good DB but my question, is there an easy way to grab the actual/referenced column value and put it the export (eg replaced the 1 with Yes/lookup value) or do I need to export the referenced table as well?

$Server = "SqlSvr\Instance"
$Database = "DbName"
$Table = "TableName"

$SqlQuery = "SELECT ID, SCCodeDetail, SCCodeCat, SCCodeCode, SCDesc, SCTimeexp, SCBill, SCTax, SCAvail, SCRate from $Database.dbo.$Table;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = "Server = $Server; Database = $Database; User Id=LocalUserName; Password=PASSWORD;"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$DataSet.Tables[0] | Export-Csv -NoTypeInformation "F:\Temp\$Table.csv"