Introduction:

Importing data from flat files into SQL Server can be a time-consuming and manual process. However, by using PowerShell, we can automate this process and minimize ETL costs. In this article, we will discuss how to automate the data import process using PowerShell and SQL Server.

Step 1: Creating a Staging Table

Before importing the flat file data into a table, we need to create a staging table. The staging table will accept all the data from the file, and we can later filter the results. We assume that the flat files have columns delimited by a character (e.g., comma) and the column names listed in the first line of the file. The name of the file will be the name of the staging table.

Here is an example of PowerShell code to automatically create a staging table:

$location = "C:\files\"
$file = "savingsrate"
$extension = ".txt"
$full = $location + $file + $extension

$all = Get-Content $full
$columns = $all[0]
$columns = $columns.Replace(" ","")
$columns = $columns.Replace(",","] VARCHAR(100), [")
$table = "CREATE TABLE " + $file + "([" + $columns + "] VARCHAR(100))"

$connection = New-Object System.Data.SqlClient.SqlConnection
$buildTable = New-Object System.Data.SqlClient.SqlCommand
$connection.ConnectionString = "Data Source=OURSERVER\OURINSTANCE;Database=StagingDatabase;integrated security=true"
$buildTable.CommandText = $table
$buildTable.Connection = $connection

$connection.Open()
$buildTable.ExecuteNonQuery()
$connection.Close()

Step 2: Importing Data into the Staging Table

Once the staging table is created, we can import the data from the flat file into the table. There are several methods to do this, but one of the most reliable methods is using the BULK INSERT command. Here is an example of PowerShell code to import the data:

Function AutoImportCommaFlatFiles($location, $file, $extension, $server, $database)
{
    $full = $location + $file + $extension
    $all = Get-Content $full
    $columns = $all[0]
    $columns = $columns.Replace(" ","")
    $columns = $columns.Replace(",","] VARCHAR(100), [")
    $table = "CREATE TABLE " + $file + "([" + $columns + "] VARCHAR(100))"

    $connection = New-Object System.Data.SqlClient.SqlConnection
    $buildTable = New-Object System.Data.SqlClient.SqlCommand
    $insertData = New-Object System.Data.SqlClient.SqlCommand
    $connection.ConnectionString = "Data Source=" + $server + ";Database=" + $database + ";integrated security=true"
    $buildTable.CommandText = $table
    $buildTable.Connection = $connection

    $x = 0
    $insertData.CommandText = "EXECUTE stp_CommaBulkInsert @1,@2"
    $insertData.Parameters.Add("@1", $full)
    $insertData.Parameters.Add("@2", $file)
    $insertData.Connection = $connection

    $connection.Open()
    $buildTable.ExecuteNonQuery()
    $connection.Close()

    $x = 1
    if ($x = 1)
    {
        $connection.Open()
        $insertData.ExecuteNonQuery()
        $connection.Close()
    }
}

AutoImportCommaFlatFiles -location "C:\files\" -file "savingsrate" -extension ".txt" -server "OURSERVER\OURINSTANCE" -database "StagingDatabase"

Step 3: Check the Imported Data

After importing the data, we can verify the results by querying the staging table. Here is an example:

SELECT * FROM StagingDatabase.dbo.savingsrate

Conclusion:

Automating the data import process in SQL Server using PowerShell can greatly reduce ETL costs and save time. By creating a staging table and using PowerShell scripts, we can easily import data from flat files into SQL Server. This method is reliable and can be used for multiple files with similar formats. With automation, we can focus on analyzing and reporting the data rather than spending time on manual data imports.

Watch the video version of this tip here.