Zendesk Connector
Documentation
Version: 9
Documentation
Endpoint

Upsert Organization - Create or Update


Name

upsert_organization

Description

Create or Update an organization in (Upsert) [API reference]

Related Tables

Organizations

Parameters

Parameter Label Required Options Description
There are no parameters

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Raw Description
id DT_I8 bigint False
name DT_WSTR nvarchar(500) 500 False
notes DT_WSTR nvarchar(4000) 4000 False
created_at DT_DBTIMESTAMP datetime False
details DT_WSTR nvarchar(4000) 4000 False
domain_names DT_WSTR nvarchar(144) 144 False
domain_names_1 DT_WSTR nvarchar(255) 255 False
domain_names_2 DT_WSTR nvarchar(255) 255 False
domain_names_3 DT_WSTR nvarchar(255) 255 False
external_id DT_WSTR nvarchar(500) 500 False
group_id DT_I8 bigint False
shared_comments DT_BOOL bit False
shared_tickets DT_BOOL bit False
tags DT_WSTR nvarchar(144) 144 False
tags_1 DT_WSTR nvarchar(500) 500 False
tags_2 DT_WSTR nvarchar(500) 500 False
tags_3 DT_WSTR nvarchar(500) 500 False
tags_4 DT_WSTR nvarchar(500) 500 False
tags_5 DT_WSTR nvarchar(500) 500 False
updated_at DT_DBTIMESTAMP datetime False
url DT_WSTR nvarchar(500) 500 False
organization_field_dt_[$parent.key$] DT_WSTR nvarchar(4000) 4000 False
organization_field_[$parent.key$] DT_WSTR nvarchar(4000) 4000 False
If the column you are looking for is missing, consider customizing Zendesk Connector.

Input Columns

Label Data Type (SSIS) Data Type (SQL) Length Raw Description
id DT_I8 bigint False
name DT_WSTR nvarchar(500) 500 False
notes DT_WSTR nvarchar(4000) 4000 False
external_id DT_WSTR nvarchar(500) 500 False
group_id DT_I8 bigint False
details DT_WSTR nvarchar(4000) 4000 False
tags DT_WSTR nvarchar(4000) 4000 True
organization_fields DT_WSTR nvarchar(4000) 4000 True
domain_names DT_WSTR nvarchar(1000) 1000 True
shared_comments DT_BOOL bit False
shared_tickets 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:

Upsert rows into Organizations table using API Destination

This Endpoint belongs to Organizations table, therefore you cannot work with it directly. Use this table and table-operation pair instead:

Zendesk
Organizations
Upsert
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Upsert Organization (Create or Update)

Creates an organization if the organization does not already exist, or updates an existing organization identified by e-mail address or external ID.

UPSERT INTO Organizations
(
	 name
	--id or external_id can be supplied for UPSERT
	,id 
	--or--
	,external_id
	 
	,group_id 
	,tags
	,details
	,notes
	,organization_fields
	,domain_names
	,shared_tickets
	,shared_comments
)
VALUES(
	'Abc Inc'
	,1234567 --id
	--or--
	,'zcrm_1558554000052161270'  --external_id
	
	,114094762733  
	,'["paid","trial","solved"]' 
	,'some details'
	,'some notes'
	,'{"startdate": "1981-01-23", "revenue": 12000000.50, "somenumber": 1235678}'
	,'["aaa.com", "bbb.com"]'
	,'false'
	,'false'
)

Bulk Import Mode for Users or Organizations

This example shows how to upsert in bulk mode from external data and import into Zendesk. We used Microsoft SQL Server as source system in below example with static value query (just one row) but it can be any number of rows.

UPSERT INTO Organizations
SOURCE('MSSQL' --ODBC
, 'Data Source=localhost;Initial Catalog=test;Integrated Security=true'

--For bulk input, map columns in External Query (Must use alias column name to match INSERT command Input Column names - see help file)
--If parameter value not same for all input rows then you can prefix some column with $$ to map as parameter (i.e. $$MyParam1)
--'ODBC', 'Driver={ZappySys CSV Driver};DataPath=C:\AccountsToInsert.csv'
--'ODBC', 'DSN=MyDSN'
--'OLEDB', 'Provider=SQLNCLI11;Server=localhost,1433;Database=tempdb;Trusted_Connection=yes;'

, 'select
      21863188631451 as id
    , ''Abc Inc'' as name
	,''zcrm_1558554000052161270'' as external_id
	,114094762733  as group_id
	,''["paid","trial","solved"]'' as tags
	,''some details'' as details
	,''some notes'' as notes
	,''{"startdate": "1981-01-23", "revenue": 12000000.50, "somenumber": 1235678}'' as organization_fields
	,''["aaa.com", "bbb.com"]'' domain_names
	,''false'' as shared_tickets
	,''false'' as shared_comments
')

upsert_organization endpoint belongs to Organizations table(s), and can therefore be used via those table(s).

SQL Server

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

Upsert Organization (Create or Update)

Creates an organization if the organization does not already exist, or updates an existing organization identified by e-mail address or external ID.

DECLARE @MyQuery NVARCHAR(MAX) = 'UPSERT INTO Organizations
(
	 name
	--id or external_id can be supplied for UPSERT
	,id 
	--or--
	,external_id
	 
	,group_id 
	,tags
	,details
	,notes
	,organization_fields
	,domain_names
	,shared_tickets
	,shared_comments
)
VALUES(
	''Abc Inc''
	,1234567 --id
	--or--
	,''zcrm_1558554000052161270''  --external_id
	
	,114094762733  
	,''["paid","trial","solved"]'' 
	,''some details''
	,''some notes''
	,''{"startdate": "1981-01-23", "revenue": 12000000.50, "somenumber": 1235678}''
	,''["aaa.com", "bbb.com"]''
	,''false''
	,''false''
)';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

Bulk Import Mode for Users or Organizations

This example shows how to upsert in bulk mode from external data and import into Zendesk. We used Microsoft SQL Server as source system in below example with static value query (just one row) but it can be any number of rows.

DECLARE @MyQuery NVARCHAR(MAX) = 'UPSERT INTO Organizations
SOURCE(''MSSQL'' --ODBC
, ''Data Source=localhost;Initial Catalog=test;Integrated Security=true''

--For bulk input, map columns in External Query (Must use alias column name to match INSERT command Input Column names - see help file)
--If parameter value not same for all input rows then you can prefix some column with $$ to map as parameter (i.e. $$MyParam1)
--''ODBC'', ''Driver={ZappySys CSV Driver};DataPath=C:\AccountsToInsert.csv''
--''ODBC'', ''DSN=MyDSN''
--''OLEDB'', ''Provider=SQLNCLI11;Server=localhost,1433;Database=tempdb;Trusted_Connection=yes;''

, ''select
      21863188631451 as id
    , ''''Abc Inc'''' as name
	,''''zcrm_1558554000052161270'''' as external_id
	,114094762733  as group_id
	,''''["paid","trial","solved"]'''' as tags
	,''''some details'''' as details
	,''''some notes'''' as notes
	,''''{"startdate": "1981-01-23", "revenue": 12000000.50, "somenumber": 1235678}'''' as organization_fields
	,''''["aaa.com", "bbb.com"]'''' domain_names
	,''''false'''' as shared_tickets
	,''''false'''' as shared_comments
'')';

EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY];

upsert_organization endpoint belongs to Organizations table(s), and can therefore be used via those table(s).