Read from a SQL Server table

---

This page describes how to set up your data pipeline to read data from a Microsoft SQL Server table.

Before you begin

1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

3. Make sure that billing is enabled for your Google Cloud project.

4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

5. Make sure that billing is enabled for your Google Cloud project.

6. Enable the Cloud Data Fusion, BigQuery, Cloud Storage, and Dataproc APIs.

   Enable the APIs

7. Create a Cloud Data Fusion instance.
8. Your SQL Server database must accept connections from Cloud Data Fusion. For security reasons, use a private Cloud Data Fusion instance.

Open your Cloud Data Fusion instance

1. In the Google Cloud console, go to the Cloud Data Fusion Instances page.

   Go to Instances

2. In the Actions column for the instance, click View instance to open the instance in Cloud Data Fusion.

Store your SQL Server password as a secure key

Add your SQL Server password as a secure key in your Cloud Data Fusion instance.

1. From Cloud Data Fusion, click System Admin.

2. Click the Configuration tab.

3. Click Make HTTP Calls.

   

4. Select PUT.

5. In the path field, enter namespaces/NAMESPACE_ID/securekeys/password .

6. In the Body field, enter {"data":"password"}. Replace password with your SQL Server password.

7. Click Send.

   

The Response must have status code 200 to continue.

Get the JDBC driver for SQL Server

You can get the driver from the Hub or in the Pipeline Studio in Cloud Data Fusion.

Hub

1. In the Cloud Data Fusion UI, click Hub.

2. In the search bar, enter SQL Server JDBC Driver and select the driver.

3. Click Download. Follow the download steps shown.

4. Click Deploy. Upload the JAR file from the previous step.

5. Click Finish.

Pipeline Studio

1. Go to Microsoft.com.

2. Choose your download and click Download.

3. In Cloud Data Fusion, click Menu and go to the Pipeline Studio page.

4. Click Add.

5. For the driver, click Upload.

6. Select the JAR file, located in the jre7 folder.

7. Click Next.

8. To configure the driver, enter a Name and Class name.

9. Click Finish.

Deploy the SQL Server Plugin

1. In Cloud Data Fusion, click Hub.

2. In the search bar, enter SQL Server Plugins.

3. Click SQL server plugins.

4. Click Deploy.

5. Click Finish.

6. Click Create a pipeline.

Connect to SQL Server

You can connect to SQL Server from Cloud Data Fusion in Wrangler or the Pipeline Studio.

Wrangler

1. In Cloud Data Fusion, click Menu and go to the Wrangler page.

2. Click Add connection.

   An Add connection window opens.

3. Click SQL Server to verify that the driver is installed.

   

4. Enter details in the required connection fields. In the Password field, select the secure key you stored previously. It ensures that your password is retrieved using Cloud KMS.

   

5. To check that a connection can be established with the database, click Test connection.

6. Click Add connection.

After your SQL Server database is connected and you've created a pipeline that reads from your SQL Server table, you can apply transformations and write your output to a sink.

Pipeline Studio

1. Open your Cloud Data Fusion instance and go to the Pipeline Studio page.

2. Expand the Source menu and click SQL Server.

   

3. On the SQL Server node, click Properties.

   

4. In the Reference name field, enter a name that identifies your SQL Server source.

5. In the Database field, enter the name of the database to connect to.

6. In the Import query field, enter the query to run. For example, SELECT * FROM table WHERE $CONDITIONS.

7. Click Validate.

8. Click close .

After your SQL Server database is connected and you've created a pipeline that reads from your SQL Server table, add any desired transformations and write your output to a sink.

What's next

• Learn how to read data from multiple SQL Server tables.
• Learn more about Cloud Data Fusion.
• Follow one of the tutorials.