Export SQL results to csv

ExportSQLResults.ps1

# Runs the SQL command and returns a data table
function Run-Query($query, $database, $server){

	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source = $server;Initial Catalog=$database;Integrated Security = True")
	$SqlConnection.Open()
	
	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand($query,$SqlConnection)
	
	$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SqlCmd)
	
	$DataSet = New-Object System.Data.DataSet
	$nRecs = $SqlAdapter.Fill($DataSet)
	$nRecs | Out-Null
	
	$objTable = $DataSet.Tables[0]
	
	$SqlConnection.Close()
	
	return $objTable
}

# Runs the SQL scripts in the input directory and exports the results to csv
function Export-SqlToCsv($inputDirectory, $outputDirectory, $server, $database){
	
	$scripts = Get-ChildItem -Path $inputDirectory | where { $_.Name.EndsWith('.sql') }
	
	if((Test-Path $outputDirectory) -eq 0){
		New-Item -ItemType Directory -Force -Path $outputDirectory | Out-Null
	}

	foreach($script in $scripts){
	
		$query = Get-Content $script.FullName
		
		$fileName = $script.Name.Split('.')[0] + '.csv'
		
		$outPutFile = Join-Path -path $outPutDir -childpath $fileName
		
		$dataTable = Run-Query $query $database $server
			
		$dataTable | Export-CSV $outPutFile -notype
	}
}

$Database = "Database"
$Server = "SqlInstance"
$ScriptsDir = 'C:\Scripts\SQL'
$OutPutDir = 'C:\Data'

Export-SqlToCsv $ScriptsDir $OutPutDir $Server $Database