BCP and PowerShell

Sometimes you need to extract a large number of tables into some other format. I’ve written about BCP earlier as a quick option to move data around, but what if you need a delimited text file or a way to repeat the calls without too much trouble across a set of servers? I had this come up recently and wrote up a combination of PowerShell, the sqlserver module, and the bcp.exe tool to allow for a variety of extract types and also, importantly, ensure that we get header data with those extracts in case we need them to pull in to another process. This is doable without the header piece, but many processes (and humans) expect some sort of header data. Thus – a slightly expanded process.

I’ve included the full script below, but will try to walk through the sections. First, we import the sqlserver module so we can get header information and define our server, export folder, database, and a set of tables to export with “schema.tablename” as the expected format. We then create the export folder if it doesn’t exist and start looping through the table objects.

$serverName = "localhost"
$ExportFolder = "C:\temp\$serverName"
$databaseName = "AdventureWorks"
$tablesToExport = 'Person.Address','Person.AddressType','Person.BusinessEntity','Person.BusinessEntityAddress','Person.BusinessEntityContact','Person.ContactType','Person.CountryRegion','Person.EmailAddress','Person.Password','Person.Person','Person.PersonPhone','Person.PhoneNumberType','Person.StateProvince'

New-item -ItemType Directory -Path $ExportFolder -Force

foreach ($table in $tablesToExport) {

As we loop through the tables, we want to indicate what table we’re exporting as well as run a query to get the column names from the INFORMATION_SCHEMA.COLUMNS view, ordered by the ORDINAL_POSITION so the column names will match that of the file extract.

We then define our variables for a temporary output path (for the BCP data extract), a more permanent output file to which we’ll load the header data, then append the BCP data, and define the delimiter so it will match that used by BCP.

    $TempOutputFile = Join-Path -Path $ExportFolder -ChildPath "$table_temp.csv"
    $OutputFile = Join-Path -Path $ExportFolder -ChildPath "$table.csv"
    $delimiter = ","

We then run our BCP command, which assumes that the BCP executable is in the path. This exports the entire table to the temporary output file using the specified database, character format, the specified text delimiter, points to the server, uses Trusted windows authentication, we specify a row delimiter of CRLF, and a code page of UTF-8. The “-TrustServerCertificate” parameter is used mostly because many on-prem SQL Servers do not have anything other than the self-signed certificate and newer versions of MS tools expect a certificate. That parameter can be adjusted as needed. We then Invoke-Expression to run the BCP command. If you have larger tables, it may be wise to use the “-b ####” parameter to specify a larger or smaller batch size.

    $bcpCommand = "bcp $table out '$TempOutputFile' -d $databaseName -c -t ',' -S $serverName -T -TrustServerCertificate -r '`r`n' -C 65001"
    Invoke-Expression $bcpCommand

Now we want to combine the data. We start by using “Invoke-Sqlcmd” to get the header data, pull out just the COLUMN_NAME, and then pass it into a delimited string using the specified delimiter. We then create the final OutputFile with that header row, and Get the Content of the BCP data to append to that final output file. When done, we remove the temporary file.

    $headers = (Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query $query -TrustServerCertificate |
        Select-Object -ExpandProperty COLUMN_NAME) -join $delimiter
    Set-Content -Path $OutputFile -Value $headers -Force #write header data
    Get-Content $TempOutputFile | Add-Content -Path $OutputFile #append data
    Remove-Item $TempOutputFile -Force

The full code in the gist below includes other examples, though it would likely suffice to just change the delimiter and use a variable for the BCP command. This was just some quick code to show the examples of the different formats before the end-user settles on a specific format for their use-case.