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

Allows the reading and writing of incidents, attachments, and more in ServiceNow without coding! The ZappySys ServiceNow API Connector provides easy-to-use, high-performance API access.

ServiceNow

Get Table Columns

Required Parameters
TableName	Fill-in the parameter...
Filter	Fill-in the parameter...
Optional Parameters
EnablePivot	True
ElementScopeSeparator	_

SSIS API Source - Read from table or endpoint

API Destination

API Destination - ServiceNow

Allows the reading and writing of incidents, attachments, and more in ServiceNow without coding! The ZappySys ServiceNow API Connector provides easy-to-use, high-performance API access.

ServiceNow

Get Table Columns

Required Parameters
TableName	Fill-in the parameter...
Filter	Fill-in the parameter...
Optional Parameters
EnablePivot	True
ElementScopeSeparator	_

SSIS API Destination - Access table or endpoint

ODBC application

Use these SQL queries in your ODBC application data source:

Get the display value of a reference field

This example shows how to get related record(s) from the reference field. Basically, you need to know referenced table name for that referenced field. Then you can query by sys_id (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 order by 1 WITH(TableName='incident')

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

Get columns of a specific table

SELECT * FROM get_table_columns
where name = 'TableNameGoesHere'

SQL Server

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

Get the display value of a reference field

This example shows how to get related record(s) from the reference field. Basically, you need to know referenced table name for that referenced field. Then you can query by sys_id (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 order by 1 WITH(TableName=''incident'')

--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];

Get columns of a specific table

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_table_columns
where name = ''TableNameGoesHere''';

EXEC (@MyQuery) AT [LS_TO_SERVICENOW_IN_GATEWAY];