Data Source - SQL Server

Updated on 07 Dec 2022

Print
Share
Dark
Light
PDF

Article Summary

Share feedback

Thanks for sharing your feedback!

Note

This is a machine-translated version of the original Japanese article.
Please understand that some of the information contained on this page may be inaccurate.

summary

This is a help page for setting up data transfer from SQLServer.

Supported Protocols

Data Transfer (Embulk)
Using embulk-input-sqlserver

constraint

The SELECT privilege of the object to be transferred must be granted to the user of the connection information used for the transfer settings. (db_datareader authority, etc.)
For more information, please refer to the official documentation.
In order to use the table list and schema list suggestion functions, the following permissions must be granted to the SQL Server connection information used in the transfer settings.
- The table list is obtained from.sys.databases The permissions described in the following manual are required.
  https://docs.microsoft.com/ja-jp/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-ver16#permissions
- The schema list is obtained from the Information Schema View. The permissions described in the following manual are required.
  https://docs.microsoft.com/ja-jp/sql/relational-databases/system-information-schema-views/system-information-schema-views-transact-sql?view=sql-server-ver16#permissions
If the above permissions are not granted, the suggestion function will fail, but if the permission to access data is granted, data preview and transfer jobs can be executed.

Setting items

STEP1 Basic settings

Item	namerequireddefault		valuecontent
SQL Server connection information	Yes	-	From the connection information registered in advance, select the one that has the necessary permissions for this transfer setting.
Database name	Yes	-	Specify the name of the database where the data you want to retrieve is stored.
schema	Yes	-	Specify the schema name where the data you want to retrieve is stored.
Transfer Method	Yes	-	Specify the transfer method using query or differential transfer (transfer only incremental data from the previous transfer).
query	No	-	Input is required when specifying a transfer using Query as the transfer method. Describe the query you want to run. Custom variables can also be used to dynamically determine the setting value during trocco data transfer.
table	No	-	This input is required when you specify differential transfer (transfer only incremental data from the previous transfer) as the transfer method. Specify the table from which you want to retrieve data.
Columns that determine incremental data	No	-	You can enter this if the transfer method is set to Delta Transfer. The values of the columns specified here are stored in the last transferred record each time they are transferred. During the second and subsequent transfers, only records whose target column of incremental data is greater than or equal to the value at the time of the previous transfer are transferred. Specify the name of the unique Auto Incremnt ID column, for example. If you want to specify multiple columns, separate them with commas. If not specified, the primary key is used.
Last Transferred Record	No	-	You can enter this if the transfer method is set to Delta Transfer. During differential transfers, data newer than the value specified here is transferred. If not specified, transfer from the beginning. Unless you have a specific reason to do so, do not change this value. Data duplication may occur.
Default time zone	Yes	UTC	If the column is of type date/time/datetime, specify the time zone in which the time should be.

STEP2 Advanced settings

Item name	default	value content
The number of records that the cursor processes at one time	10000	You can specify the number of rows to retrieve at one time.
Connection Timeout (seconds)	300	You can specify the timeout in seconds before the driver connects. If it is set to 0, it will be 15 seconds, which is the default for SQL Server.
Socket Timeout (seconds)	1800	You can specify the timeout in seconds before the query runs. If it is set to 0, there is no timeout.

Was this article helpful?

What's Next

Data Source - MongoDB

Table of contents

summary
Supported Protocols
constraint
Setting items