For the ODX server, a data source provider is available for copying data using Azure Data Factory if the data is on Azure SQL and the ODX data storage is be on Azure Data Lake Gen2.
To use the data source provider, you need the following:
- An Azure Data Factory on version 2 ("V2").
- A data source on Azure SQL Database or Azure SQL Management Instance
- An ODX data storage on Azure Data Lake Gen2
Before you add the data source, make sure you have the following information from your Azure tenant ready.
- Azure Tenant ID
Connection to Azure Data Factory
- Azure Data Factory Name, Resource Group and Subscription ID
- Azure AD (App Registration) Application ID (Client ID) and Application Key (Client Secret)
- (Optional) A name for the folder Azure Data Factory uses for the pipelines and datasets it creates. Defaults to the GUID of the data source in the ODX.
Connection to Azure SQL
- Data source (server host)
- Database name
- Username and password (SQL Server Authentication)
Adding the Data Source
To add the data source, follow the steps below.
- Open your ODX server in a tab
- Right click Data Sources and click Add Data Source...
- On the first page, enter a Name and (optional) Description for your data source and click Next
- On the Provider page, select the Azure Data Factory - SQL DB data source provider and click Next
- On the Connection Info page, enter the required information and click Next. See the previous section for information on the individual settings.
- On the Data page, click Let me select the tables if you do not wish to copy all tables from the source. On the next page, you can then select the tables you want to use. Otherwise, just click Next.
- On the Next Step page, click No, I'll do that later if you do not wish to add a task after adding the data source. Click Finish. The Add Task wizard will appear so you can add a task to the data source.