Reference

Endpoint Get Table Columns

Name

get_table_columns

Description

Get a list of columns of a table [API reference]

Parameters

Parameter	Required	Options
Name: TableName Label: TableName Table name	YES

Output Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length
name	`DT_WSTR`	`nvarchar(128)`	128
element	`DT_WSTR`	`nvarchar(128)`	128
label	`DT_WSTR`	`nvarchar(96)`	96
internal_type	`DT_WSTR`	`nvarchar(128)`	128
max_length	`DT_I8`	`bigint`
column_type	`DT_WSTR`	`nvarchar(128)`	128
type	`DT_WSTR`	`nvarchar(60)`	60
filterable	`DT_BOOL`	`bit`
reference_display_field	`DT_WSTR`	`nvarchar(60)`	60
reference_attributes_display_field	`DT_WSTR`	`nvarchar(60)`	60
reference_attributes_ref_ac_columns	`DT_WSTR`	`nvarchar(108)`	108
reference_attributes_ref_ac_columns_search	`DT_WSTR`	`nvarchar(80)`	80
reference_attributes_ref_ac_display_value	`DT_BOOL`	`bit`
reference_attributes_ref_auto_completer	`DT_WSTR`	`nvarchar(80)`	80
canmatch	`DT_BOOL`	`bit`
cangroup	`DT_BOOL`	`bit`
max_unit	`DT_WSTR`	`nvarchar(80)`	80
mandatory	`DT_BOOL`	`bit`
reference	`DT_WSTR`	`nvarchar(100)`	100
default	`DT_WSTR`	`nvarchar(255)`	255
base_type	`DT_WSTR`	`nvarchar(32)`	32
read_only	`DT_BOOL`	`bit`
hint	`DT_WSTR`	`nvarchar(388)`	388
attributes	`DT_WSTR`	`nvarchar(4000)`	4000
cansort	`DT_BOOL`	`bit`
multitext	`DT_BOOL`	`bit`
choice_type	`DT_I8`	`bigint`
choices	`DT_NTEXT`	`nvarchar(MAX)`

If the column you are looking for is missing, consider customizing ServiceNow Connector.

Input Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

Examples

SSIS

Use ServiceNow Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

API Source - ServiceNow

Read and write ServiceNow data effortlessly. Integrate, manage, and automate incidents, tasks, attachments, and records — almost no coding required.

ServiceNow

Get Table Columns

Required Parameters
TableName	Fill-in the parameter...
Filter	Fill-in the parameter...
Optional Parameters
EnablePivot	True
ElementScopeSeparator	_
Date Time Value Handling

SSIS API Source - Read from table or endpoint

API Destination

API Destination - ServiceNow

Read and write ServiceNow data effortlessly. Integrate, manage, and automate incidents, tasks, attachments, and records — almost no coding required.

ServiceNow

Get Table Columns

Required Parameters
TableName	Fill-in the parameter...
Filter	Fill-in the parameter...
Optional Parameters
EnablePivot	True
ElementScopeSeparator	_
Date Time Value Handling

SSIS API Destination - Access table or endpoint

ODBC application

Use these SQL queries in your ODBC application data source:

Read display value of a reference field

Gets related record(s) from a reference field. You need to know the referenced table name for that field, then query by <code>sys_id</code> (Primary Key).

--STEP#1 returns incident number and related company (sys_id)
SELECT number,company FROM incident

--STEP#2 find related table for company field
select name,reference from get_table_columns WITH(TableName='incident') order by 1

--STEP#3 query related table with sys_id returned in step#1
select sys_id, name  from core_company where sys_id='31bea3d53790200044e0bfc8bcbe5dec'

Read columns of a table

Gets column metadata for a specific table.

SELECT * FROM get_table_columns WITH(TableName='TableNameGoesHere')

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Read display value of a reference field

Gets related record(s) from a reference field. You need to know the referenced table name for that field, then query by <code>sys_id</code> (Primary Key).

DECLARE @MyQuery NVARCHAR(MAX) = '--STEP#1 returns incident number and related company (sys_id)
SELECT number,company FROM incident

--STEP#2 find related table for company field
select name,reference from get_table_columns WITH(TableName=''incident'') order by 1

--STEP#3 query related table with sys_id returned in step#1
select sys_id, name  from core_company where sys_id=''31bea3d53790200044e0bfc8bcbe5dec''';

EXEC (@MyQuery) AT [LS_TO_SERVICENOW_IN_GATEWAY];

Read columns of a table

Gets column metadata for a specific table.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_table_columns WITH(TableName=''TableNameGoesHere'')';

EXEC (@MyQuery) AT [LS_TO_SERVICENOW_IN_GATEWAY];