Load data into a Database Table

Activity	SQLBulkInsert
Description	Load data into a Database Table (From CSV file or from DbStore)

Input :

PropertyName	Type	Required	Default Value	Description
Source	DataSource	YES		The Data Source to load into the table
Destination	DataDestination	YES		the Data destination

DataSource

PropertyName	Type	Required	Default Value	Version	Description
connectionName	STRING	NO			name of the source connection (see DbStore)
query	Query	NO			the Query to execute on the connectionName
fileToken	URL	NO			A FileToken of a file to import
fileTokens	URL ARRAY	NO			And an array of file tokens to import
fileName	STRING	NO			Name of a CSV file located on the same server as the worker
separator	CHAR	NO			CSV File Separator
headers	BOOL	NO	FALSE		Indicate if the file contains headers
cultureName	STRING	NO	EN-us		Culture to use while converting numbers and dates
detectQuotedValues	BOOL	NO	TRUE		Ignore Quote around CSV value when we find them
deleteFileToken	BOOL	NO	FALSE		Delete the file token after loading into DbStore
nullString	STRING	NO	““	1.4.11	value that must be considered as NULL while importing CSV file (useful for PostgreSQL import with \N as NULL value)

DataDestination

PropertyName	Type	Required	Default Value	Description
connectionName	STRING	YES		name of the destination connection (see DbStore)
tableName	STRING	YES		Name of the Table
schemaName	STRING	NO		Name of the schema
Timeout	INT	NO	600	Timeout in seconds
TruncateFirst	BOOL	NO	FALSE	Truncate destination table before loading

Query

PropertyName	Type	Required	Default Value	Description
queryFileToken	URL	NO		File token of a file containing the query to execute
query	STRING	NO		SQL Query to execute

Output

Boolean : “true”

Example1: Load a CSV file into a table

Input

JSON

{
   "source" : {
       "fileToken":"file://local/extract.csv",
       "separator":',',
       "headers": true
   },
   "destination": {
      "connectionName": "dbStore1",
      "tableName": "table1",
      "schemaName" : "stg",
      "truncateFirst": true
   }
}

Output

JSON

true

Example2: Load a Data from a DbStore file into a table

Input

JSON

{
   "source" : {
       "connectionName":"dbStoreConnection1",
       "query": {
          "query" : "SELECT * FROM Employees"
       }
   },
   "destination": {
      "connectionName": "dbStoreConnection2",
      "tableName": "table1",
      "schemaName" : "stg",
      "truncateFirst": true
   }
}

Output

JSON

true