hybrid-copy-powershell.md

title	description	ms.subservice	ms.topic	ms.author	author	ms.custom	ms.date
Copy data from on-premises to Azure using PowerShell	This PowerShell script copies data from a SQL Server database to another an Azure Blob Storage.	data-movement	article	jianleishen	jianleishen	devx-track-azurepowershell	01/05/2024

Use PowerShell to create a data factory pipeline to copy data from SQL Server to Azure

This sample PowerShell script creates a pipeline in Azure Data Factory that copies data from a SQL Server database to an Azure Blob Storage.

[!INCLUDE updated-for-az]

[!INCLUDE sample-powershell-install]

Prerequisites

• SQL Server. You use a SQL Server database as a source data store in this sample.
• Azure Storage account. You use Azure blob storage as a destination/sink data store in this sample. if you don't have an Azure storage account, see the Create a storage account article for steps to create one.
• Self-hosted integration runtime. Download MSI file from the download center and run it to install a self-hosted integration runtime on your machine.

Create sample database in SQL Server

1. In the SQL Server database, create a table named emp by using the following SQL script:

     CREATE TABLE dbo.emp
     (
         ID int IDENTITY(1,1) NOT NULL,
         FirstName varchar(50),
         LastName varchar(50),
         CONSTRAINT PK_emp PRIMARY KEY (ID)
     )
     GO

2. Insert some sample data into the table:

     INSERT INTO emp VALUES ('John', 'Doe')
     INSERT INTO emp VALUES ('Jane', 'Doe')

Sample script

Important

This script creates JSON files that define Data Factory entities (linked service, dataset, and pipeline) on your hard drive in the c:\ folder.

[!code-powershellmain]

Clean up deployment

After you run the sample script, you can use the following command to remove the resource group and all resources associated with it:

Remove-AzResourceGroup -ResourceGroupName $resourceGroupName

To remove the data factory from the resource group, run the following command:

Remove-AzDataFactoryV2 -Name $dataFactoryName -ResourceGroupName $resourceGroupName

Script explanation

This script uses the following commands:

Command	Notes
New-AzResourceGroup	Creates a resource group in which all resources are stored.
Set-AzDataFactoryV2	Create a data factory.
New-AzDataFactoryV2LinkedServiceEncryptCredential	Encrypts credentials in a linked service and generates a new linked service definition with the encrypted credential.
Set-AzDataFactoryV2LinkedService	Creates a linked service in the data factory. A linked service links a data store or compute to a data factory.
Set-AzDataFactoryV2Dataset	Creates a dataset in the data factory. A dataset represents input/output for an activity in a pipeline.
Set-AzDataFactoryV2Pipeline	Creates a pipeline in the data factory. A pipeline contains one or more activities that performs a certain operation. In this pipeline, a copy activity copies data from one location to another location in an Azure Blob Storage.
Invoke-AzDataFactoryV2Pipeline	Creates a run for the pipeline. In other words, runs the pipeline.
Get-AzDataFactoryV2ActivityRun	Gets details about the run of the activity (activity run) in the pipeline.
Remove-AzResourceGroup	Deletes a resource group including all nested resources.

Related content

For more information on the Azure PowerShell, see Azure PowerShell documentation.

Additional Azure Data Factory PowerShell script samples can be found in the Azure Data Factory PowerShell samples.