EndPoint Read Tickets for Organization

Name

get_tickets_by_org

Description

Get all tickets for specified organization_id [API reference]

Parameters

Parameter	Label	Required	Options	Description
organization_id	Organization ID	YES
PageSize	Records Per Page (Max 100)	NO

Output Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Raw
id	`DT_I8`	`bigint`		`False`
recipient	`DT_WSTR`	`nvarchar(255)`	255	`False`
subject	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
status	`DT_WSTR`	`nvarchar(28)`	28	`False`
type	`DT_WSTR`	`nvarchar(255)`	255	`False`
priority	`DT_WSTR`	`nvarchar(24)`	24	`False`
description	`DT_NTEXT`	`nvarchar(MAX)`		`False`
external_id	`DT_WSTR`	`nvarchar(255)`	255	`False`
created_at	`DT_DBTIMESTAMP`	`datetime`		`False`
updated_at	`DT_DBTIMESTAMP`	`datetime`		`False`
raw_subject	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
url	`DT_WSTR`	`nvarchar(500)`	500	`False`
comment_count	`DT_I8`	`bigint`		`False`
requester_id	`DT_I8`	`bigint`		`False`
submitter_id	`DT_I8`	`bigint`		`False`
assignee_id	`DT_I8`	`bigint`		`False`
organization_id	`DT_I8`	`bigint`		`False`
group_id	`DT_I8`	`bigint`		`False`
collaborator_ids	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
collaborator_ids_1	`DT_WSTR`	`nvarchar(20)`	20	`False`
collaborator_ids_2	`DT_WSTR`	`nvarchar(20)`	20	`False`
collaborator_ids_3	`DT_WSTR`	`nvarchar(20)`	20	`False`
follower_ids	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
follower_ids_1	`DT_WSTR`	`nvarchar(20)`	20	`False`
follower_ids_2	`DT_WSTR`	`nvarchar(20)`	20	`False`
follower_ids_3	`DT_WSTR`	`nvarchar(20)`	20	`False`
email_cc_ids	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
email_cc_ids_1	`DT_WSTR`	`nvarchar(20)`	20	`False`
email_cc_ids_2	`DT_WSTR`	`nvarchar(20)`	20	`False`
email_cc_ids_3	`DT_WSTR`	`nvarchar(20)`	20	`False`
forum_topic_id	`DT_WSTR`	`nvarchar(255)`	255	`False`
problem_id	`DT_WSTR`	`nvarchar(255)`	255	`False`
has_incidents	`DT_BOOL`	`bit`		`False`
is_public	`DT_BOOL`	`bit`		`False`
due_at	`DT_DBTIMESTAMP`	`datetime`		`False`
tags	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
tags_1	`DT_WSTR`	`nvarchar(50)`	50	`False`
tags_2	`DT_WSTR`	`nvarchar(50)`	50	`False`
tags_3	`DT_WSTR`	`nvarchar(50)`	50	`False`
tags_4	`DT_WSTR`	`nvarchar(50)`	50	`False`
tags_5	`DT_WSTR`	`nvarchar(50)`	50	`False`
custom_fields	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
custom_fields_1_id	`DT_WSTR`	`nvarchar(20)`	20	`False`
custom_fields_1_value	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
custom_fields_2_id	`DT_WSTR`	`nvarchar(20)`	20	`False`
custom_fields_2_value	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
custom_fields_3_id	`DT_WSTR`	`nvarchar(20)`	20	`False`
custom_fields_3_value	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
custom_fields_4_id	`DT_WSTR`	`nvarchar(20)`	20	`False`
custom_fields_4_value	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
custom_fields_5_id	`DT_WSTR`	`nvarchar(20)`	20	`False`
custom_fields_5_value	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
via_followup_source_id	`DT_I8`	`bigint`		`False`
satisfaction_rating_id	`DT_I8`	`bigint`		`False`
satisfaction_rating_score	`DT_WSTR`	`nvarchar(20)`	20	`False`
satisfaction_rating_comment	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
sharing_agreement_ids	`DT_WSTR`	`nvarchar(255)`	255	`False`
fields	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
followup_ids	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
brand_id	`DT_I8`	`bigint`		`False`
allow_channelback	`DT_BOOL`	`bit`		`False`
allow_attachments	`DT_BOOL`	`bit`		`False`
via_channel	`DT_WSTR`	`nvarchar(100)`	100	`False`
via_source_rel	`DT_WSTR`	`nvarchar(255)`	255	`False`
via_source_from_address	`DT_WSTR`	`nvarchar(500)`	500	`False`
via_source_from_name	`DT_WSTR`	`nvarchar(500)`	500	`False`
via_source_to_name	`DT_WSTR`	`nvarchar(500)`	500	`False`
via_source_to_address	`DT_WSTR`	`nvarchar(500)`	500	`False`
via_source_from_ticket_id	`DT_I8`	`bigint`		`False`
via_source_from_subject	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
via_source_from_channel	`DT_WSTR`	`nvarchar(50)`	50	`False`
custom_field_[$parent.id$]	`DT_WSTR`	`nvarchar(4000)`	4000	`False`

Input Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Raw
subject	`DT_WSTR`	`nvarchar(1012)`	1012	`False`
recipient	`DT_WSTR`	`nvarchar(255)`	255	`False`
status	`DT_WSTR`	`nvarchar(28)`	28	`False`
type	`DT_WSTR`	`nvarchar(255)`	255	`False`
priority	`DT_WSTR`	`nvarchar(24)`	24	`False`
comment_body	`DT_NTEXT`	`nvarchar(MAX)`		`False`
comment_body_html	`DT_NTEXT`	`nvarchar(MAX)`		`False`
comment_public	`DT_BOOL`	`bit`		`False`
comment_author_id	`DT_I8`	`bigint`		`False`
tags	`DT_WSTR`	`nvarchar(1000)`	1000	`True`
custom_fields	`DT_WSTR`	`nvarchar(1000)`	1000	`True`
external_id	`DT_WSTR`	`nvarchar(255)`	255	`False`
requester_name	`DT_WSTR`	`nvarchar(255)`	255	`False`
requester_email	`DT_WSTR`	`nvarchar(255)`	255	`False`
requester_id	`DT_I8`	`bigint`		`False`
submitter_id	`DT_I8`	`bigint`		`False`
assignee_id	`DT_I8`	`bigint`		`False`
organization_id	`DT_I8`	`bigint`		`False`
group_id	`DT_I8`	`bigint`		`False`
collaborator_ids	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
follower_ids	`DT_WSTR`	`nvarchar(1000)`	1000	`True`
email_cc_ids	`DT_WSTR`	`nvarchar(1000)`	1000	`True`
forum_topic_id	`DT_WSTR`	`nvarchar(255)`	255	`False`
problem_id	`DT_WSTR`	`nvarchar(255)`	255	`False`
is_public	`DT_BOOL`	`bit`		`False`
due_at	`DT_DBTIMESTAMP`	`datetime`		`False`
sharing_agreement_ids	`DT_WSTR`	`nvarchar(255)`	255	`True`
fields	`DT_WSTR`	`nvarchar(1000)`	1000	`True`
followup_ids	`DT_WSTR`	`nvarchar(1000)`	1000	`True`
brand_id	`DT_I8`	`bigint`		`False`
allow_channelback	`DT_BOOL`	`bit`		`False`
allow_attachments	`DT_BOOL`	`bit`		`False`

Required columns that you need to supply are bolded.

Examples

SSIS

Use Zendesk Connector in API Source component to read data or in API Destination component to read/write data:

Read Tickets for Organization using API Source

Zendesk

Read Tickets for Organization

SSIS API Source - Read from table or endpoint

Read Tickets for Organization using API Destination

Zendesk

Read Tickets for Organization

SSIS API Destination - Access table or endpoint

ODBC application

Use these SQL queries in your ODBC application data source:

Get all tickets for specific organization

Read tickets for a specified organization_id (company).

SELECT * FROM get_tickets_by_org
WITH(
	  organization_id='12'
)

SQL Server

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

Get all tickets for specific organization

Read tickets for a specified organization_id (company).

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_tickets_by_org
WITH(
	  organization_id=''12''
)';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_ZENDESK_IN_DATA_GATEWAY];