Moving Data Between SQL Servers with Powershell

Scripting data is a pain, especially if you do not want the entire contents of a table. OpenRowset is verbose and clunky. And working with linked servers may not be the seventh layer of hell, but you’re going to smell some smoke. What I really want is to pass some parameters to a function and be done with it. Hello, PowerShell. Here’s how it works:

Create a test table on two different servers

CREATE TABLE EmailAddress
(
     AddressID INT NOT NULL,
     EmailAddress VARCHAR(100) NOT NULL
) ;

Create some test data on one server.

WITH email AS
(
     SELECT 1 AS id, 'bgates@hotmail.com' AS EmailAddress
     UNION ALL
     SELECT 2, 'mzuckerberg@facebook.com' 
)
INSERT INTO EmailAddress
SELECT id,EmailAddress FROM email 

Here’s the function

function Move-Data 
{

Param
(
$sourceServerName ,
$sourceDatabaseName,
$destinationServerName,
$destinationDatabaseName,
$destinationTableName,
$query
)

$dataTable = Invoke-Sqlcmd -Query $query -Database $sourceDatabaseName -ServerInstance $sourceServerName ;

$connectionString = "Data Source=$destinationServerName;Integrated Security=true;Initial Catalog=$destinationDatabaseName;" ;
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString ;
$bulkCopy.DestinationTableName = "$destinationTableName" ;
$bulkCopy.WriteToServer($dataTable) ;
}

Call the function

$query = "SELECT AddressID,EmailAddress FROM EmailAddresses WHERE AddressID = 1" ;
Move-Data server1 MyDB server2 MyDB EmailAddress $query

Dynamically Importing Data from Excel Using PowerShell

Since I spend a lot of time with ETL (Extraction, Transform, Load) processes, one of the most common tasks I face is importing data from an Excel spreadsheet into SQL Server. For many reasons, I’ve never been particularly happy with bcp, BULK INSERT or SSIS for this task. Recently I started experimenting with PowerShell for ETL and have been happy with the results.

Using PowerShell, I came across a simple way to accomplish a task for which I’ve never found a satisfactory solution: dynamically creating a table based on the columns in an Excel spreadsheet. Utilizing the powerful SMO assemblies, and the .NET DataTable data structure, the following script reads the structure of an Excel spreadsheet, generates a table in SQL Server and imports the data into the table.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO.SqlDataType') | Out-Null

# You can very easily convert this into a function / shell script by using parameters or $args[i] for these variables
$serverName = 'testserver';
$query = 'select * from [Sheet1$]';
$databaseName = "ExcelImport" ;
$tableName = "ExelSheet" ;
$filepath = "c:\workbook.xlsx";

# ACE works well for Excel 2007 or greater.  You need to use JET 4.0 for Excel 2003.  Use ACE whenever possible.
$connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"

# Instantiate some objects which will be needed
$serverSMO = New-Object Microsoft.SqlServer.Management.Smo.Server($serverName)
$db = $serverSMO.Databases[$databaseName];

$newTable = New-Object Microsoft.SqlServer.Management.Smo.Table ;
$newTable.Parent = $db ;
$newTable.Name = $tableName ;

$conn = New-Object System.Data.OleDb.OleDbConnection($connectionString)
$conn.open()

$cmd = New-Object System.Data.OleDb.OleDbCommand($query,$conn) 
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter($cmd) 
$dataTable = New-Object System.Data.DataTable 

$dataAdapter.fill($dataTable)
$conn.close()

# Drop the table if it exists
if($db.Tables.Contains($tableName).Equals($true))
{
	($db.Tables[$tableName]).Drop()
}

# Iterate the columns in the DataTable object and add dynamically named columns to the SqlServer Table object.	
foreach($col in $dataTable.Columns)
{
	$sqlDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Varchar
	$dataType = New-Object Microsoft.SqlServer.Management.Smo.DataType($sqlDataType);
	$dataType.MaximumLength = 8000;
	$newColumn = New-Object Microsoft.SqlServer.Management.Smo.Column($newTable,$col.ColumnName,$dataType);
	$newColumn.DataType = $dataType;
	$newTable.Columns.Add($newColumn);
}
$newTable.Create();

$connectionString = "Data Source=$serverName;Integrated Security=true;Initial Catalog=$databaseName;"
$bc = New-Object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bc.DestinationTableName = "$tableName"
$bc.WriteToServer($dataTable)

Resources

ACE – Microsoft Access Database Engine : This is required for using the ACE driver if you do not have Office installed.