Microsoft SQL Server - Upload File to Table from Workflows

Overview

Upload one or more CSV files to any table in Microsoft SQL Server. With the file data, you can: - Append Data - Add the contents of your file to the end of the table. - Replace Data - Write over the entire contents of table with the contents of your file.

Column names are inferred from the header row of your CSV file. Column names must not be null or be duplicate values.

If the table already exists, the header values are matched to the table column names. The header names must match the existing column names in your table, otherwise the Vessel will error.

Data is inserted into the table by using multiple INSERT statements.

In all instances, if the table name does not already exist, a new table will be created with datatypes inferred from the CSV contents.

Note: This Task cannot be used to upload a local file from your computer.

Recommended Setup:

A Task built with this Template should typically run after a Task that either downloads a file to Workflows or generates a file with code.

Variables

Name	Reference	Type	Required	Default	Options	Description
Host	MSSQL_HOST	Alphanumeric	✅	None	-	The domain or the IP address of the database you want to connect to.
Port	MSSQL_PORT	Integer	✅	1433	-	Number for the database port to connect to. Defaults to 1433.
Username	MSSQL_USERNAME	Alphanumeric	✅	None	-	Name of the user to connect to the database with.
Password	MSSQL_PASSWORD	Password	➖	None	-	Password associated to the provided username.
Database	MSSQL_DATABASE	Alphanumeric	✅	None	-	Name of the database in the Microsoft SQL Server to connect to.
Extra URL Parameters	MSSQL_URL_PARAMETERS	Alphanumeric	➖	None	-	Extra parameters that will be placed at the end of the connection string, after the "?". Must be separated by "&".
Workflows Folder Name	MSSQL_SOURCE_FOLDER_NAME	Alphanumeric	➖	None	-	Folder where the file to upload can be found. Leaving blank will search in the current working directory.
Workflows File Name Match Type	MSSQL_SOURCE_FILE_NAME_MATCH_TYPE	Select	✅	exact_match	Exact Match: exact_match Regex Match: regex_match	Determines if the text in "File Name" will match exactly to a single file, or use regex to match to multiple files.
Workflows File Name	MSSQL_SOURCE_FILE_NAME	Alphanumeric	✅	None	-	The file name that contains the data you want uploaded.
Table Name	MSSQL_TABLE_NAME	Alphanumeric	✅	None	-	Name of the table where you want data inserted. If the table doesn't already exist, it will be created.
Insertion Method	MSSQL_INSERT_METHOD	Select	✅	append	Append Data: append Replace Data: replace	Determines how the data in your file will be added into the target table.

YAML

Below is the YAML template

YAML


source:
  template: Microsoft SQL Server - Upload File to Table from Workflows
  inputs:
    MSSQL_HOST:
    MSSQL_PORT: '1433'
    MSSQL_USERNAME:
    MSSQL_PASSWORD:
    MSSQL_DATABASE:
    MSSQL_URL_PARAMETERS:
    MSSQL_SOURCE_FOLDER_NAME:
    MSSQL_SOURCE_FILE_NAME_MATCH_TYPE: exact_match
    MSSQL_SOURCE_FILE_NAME:
    MSSQL_TABLE_NAME:
    MSSQL_INSERT_METHOD: append
  type: TEMPLATE
guardrails:
  retry_count: 1
  retry_wait: 0h0m0s
  runtime_cutoff: 1h0m0s
  exclude_exit_code_ranges:
    - 10
    - 11
    - 14
    - 102
    - 103
    - 104
    - 105