0

I need to copy some data from an azure SQl DB instance into my azure data warehouse.

The most obvious way to do this is to use Elastic Query, similar to the below:

 CREATE EXTERNAL DATA SOURCE SqlDb WITH
(TYPE = RDBMS,
LOCATION = 'myServer.database.windows.net',
DATABASE_NAME = 'MyAzureSqlDb',
CREDENTIAL = MyLogin,
) ;

Which appear to give the below error:

Msg 103010, Level 16, State 1, Line 7 Parse error at line: 2, column: 13: Incorrect syntax near 'RDBMS'.

Which I'm assuming means elastic query isn't supported. Are there any other easy methods to copy data from SqlDb -> SqlDw ? It's sounding like the only alternative is to get an ETL tool to do it.

0

As per Microsoft documentation here You can copy data from Azure SQL Data Warehouse to any supported sink data store. And you can copy data from any supported source data store to Azure SQL Data Warehouse. For a list of data stores that are supported as sources or sinks by Copy Activity, see the Supported data stores and formats table.

Specifically, this Azure SQL Data Warehouse connector supports these functions:

  • Copy data by using SQL authentication and Azure Active Directory (Azure AD) Application token authentication with a service principal or Managed Service Identity (MSI).
  • As a source, retrieve data by using a SQL query or stored procedure.
  • As a sink, load data by using PolyBase or a bulk insert. We recommend PolyBase for better copy performance.

Important :- Note that PolyBase supports only SQL authentication but not Azure AD authentication.

If you copy data by using Azure Data Factory Integration Runtime, configure an Azure SQL server firewall so that Azure services can access the server. If you copy data by using a self-hosted integration runtime, configure the Azure SQL server firewall to allow the appropriate IP range. This range includes the machine's IP that is used to connect to Azure SQL Database.

You can create a pipeline with the copy activity by using one of the following tools or SDKs. Select a link to go to a tutorial with step-by-step instructions to create a pipeline with a copy activity.

  1. Copy Data tool
  2. Azure portal
  3. .NET SDK
  4. Python SDK
  5. Azure PowerShell
  6. REST API
  7. Azure Resource Manager template.

For your further ref here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.