SSIS Components for Monday

Build 24.0.9062

Board

An example board.

Table-Specific Information

Note: This is an example board. Column names are retrieved dynamically from the API, and might be different from the examples provided.

SELECT

The 本製品 uses the Monday API to process some of the filters. The 本製品 processes other filters client-side within the 本製品. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria that refers to other columns will cause inconsistent data.

Note that multiple filters are supported server-side provided they are all connected with either only AND or only OR logical operators.

  • ItemId supports the '=', 'IN', operators.
  • Item supports the '=', 'IN', '!=', 'NOT IN', 'CONTAINS' operators.
  • Text supports the '=', 'IN', '!=', 'NOT IN', 'CONTAINS' operators.
  • Check supports the '=', '!=', 'IS TRUE', 'IS FALSE', 'IS NOT TRUE', 'IS NOT FALSE' operators.
  • BoardRelationDisplayValue supports the '=', '!=' operators when comparing with an empty string.
  • BoardRelationLinkedItemIds supports the '=', '!=' operators when comparing with an empty string.
  • TagsText supports the '=', '!=' operators when comparing with an empty string.
  • TagsIds supports the '=', '!=' operators when comparing with an empty string.
  • CountryCode supports the '=', 'IN', '!=', 'NOT IN', 'IS NULL', 'IS NOT NULL' operators.
  • CountryName supports the '=', 'CONTAINS', 'IS NULL', 'IS NOT NULL' operators.
  • DependentOnDisplayValue supports the '=', '!=' operators when comparing with a string containing only one pair of square brackets '[]'.
  • DependentOnLinkedItemsIds supports the '=', '!=' operators when comparing with an empty string.
  • EmailLabel supports the '=', 'IN', '!=', 'NOT IN', 'CONTAINS', 'IS NULL', 'IS NOT NULL' operators.
  • Hour supports the '=', 'IN', '!=', 'NOT IN' operators for time values between 04:00 inclusive and 23:59 inclusive, and the 'IS NULL', 'IS NOT NULL' operators.
  • Date supports the '=', 'IN', '!=', 'NOT IN', '>', '>=', '<', '<=', 'IS NULL', 'IS NOT NULL' operators.
  • Datetime supports the 'IS NULL', 'IS NOT NULL' operators.
  • TimelineFrom supports the 'IS NULL', 'IS NOT NULL' operators.
  • TimelineTo supports the 'IS NULL', 'IS NOT NULL' operators.
  • WeekStartDate supports the 'IS NULL', 'IS NOT NULL' operators.
  • WeekEndDate supports the 'IS NULL', 'IS NOT NULL' operators.
  • LinkUrlText supports the '=', 'IN', '!=', 'NOT IN', 'CONTAINS', 'IS NULL', 'IS NOT NULL' operators.
  • Numbers supports the '=', '!=', 'IN', 'NOT IN', '>', '>=', '<', '<=', 'IS NULL', 'IS NOT NULL' operators.
  • PeopleText supports the '=', '!=' operators when comparing with an empty string.
  • PeopleValue supports the 'IS NULL', 'IS NOT NULL' operators.
  • DropdownText supports the '=', '!=' operators when comparing with an empty string.
  • DropdownValue supports the 'IS NULL', 'IS NOT NULL' operators.
  • PhoneCountryShortName supports the '=', 'IN', '!=', 'NOT IN', 'IS NULL', 'IS NOT NULL' operators.
  • Phone supports the '=', 'CONTAINS', 'IS NULL', 'IS NOT NULL' operators.
  • Rating supports the '=', 'IN', '!=', 'NOT IN', 'IS NULL', 'IS NOT NULL' operators.
  • StatusIndex supports the '=', 'IN', '!=', 'NOT IN', 'IS NULL', 'IS NOT NULL' operators.
  • StatusLabel supports the '=', 'IN', '!=', 'NOT IN', 'IS NULL', 'IS NOT NULL' operators.
  • LastUpdated supports the '=', 'IN', '!=', 'NOT IN' operators.
  • TimeTrackingRunning supports the '=', '!=', 'IS TRUE', 'IS FALSE', 'IS NOT TRUE', 'IS NOT FALSE' operators.
  • VoteVoterIds supports the '=', '!=' operators when comparing with an empty string.

The following columns are sorted server-side when including an ORDER BY clause:

  • CreatedAt supports 'ASC NULLS FIRST', 'DESC NULLS LAST'.
  • UpdatedAt supports 'ASC NULLS FIRST', 'DESC NULLS LAST'.
  • Item supports 'ASC NULLS FIRST', 'DESC NULLS LAST'.
  • BoardRelationDisplayValue supports 'ASC NULLS FIRST', 'DESC NULLS LAST'.
  • Check supports 'ASC NULLS FIRST', 'DESC NULLS LAST'.
  • ColorPickerColor supports 'ASC NULLS FIRST', 'DESC NULLS LAST'.
  • CountryName supports 'ASC NULLS FIRST', 'DESC NULLS LAST'.
  • DependentOnDisplayValue supports 'ASC NULLS FIRST', 'DESC NULLS LAST'.
  • EmailLabel supports 'ASC NULLS FIRST', 'DESC NULLS LAST'.
  • Numbers supports 'ASC NULLS LAST', 'DESC NULLS FIRST'.
  • LinkUrlText supports 'ASC NULLS FIRST', 'DESC NULLS LAST'.
  • Phone supports 'ASC NULLS FIRST', 'DESC NULLS LAST'.
  • Rating supports 'ASC NULLS FIRST', 'DESC NULLS LAST'.
  • TimeTrackingDuration supports 'ASC NULLS FIRST', 'DESC NULLS LAST'.

The following queries are processed server side: 

SELECT * FROM Board
SELECT * FROM Board WHERE ItemId = '5465844671'
SELECT * FROM Board WHERE ItemId IN ('5465844671','5465844679')
SELECT * FROM Board WHERE Item = 'Item 2'
SELECT * FROM Board WHERE Item != 'Item 2'
SELECT * FROM Board WHERE Item NOT IN ('Item 1','Item 2')
SELECT * FROM Board WHERE Item Contains 'Item'
SELECT * FROM Board WHERE CONTAINS(Item,'Item')
SELECT * FROM Board WHERE NOT CONTAINS(Item,'Item')
SELECT * FROM Board WHERE Check = True
SELECT * FROM Board WHERE Check != True
SELECT * FROM Board WHERE Check IS TRUE
SELECT * FROM Board WHERE Check IS NOT TRUE
SELECT * FROM Board WHERE Check = False
SELECT * FROM Board WHERE Check != False
SELECT * FROM Board WHERE Check IS FALSE
SELECT * FROM Board WHERE Check IS NOT FALSE
SELECT * FROM Board WHERE BoardRelationLinkedItemIds = ''
SELECT * FROM Board WHERE BoardRelationLinkedItemIds != ''
SELECT * FROM Board WHERE BoardRelationDisplayValue = ''
SELECT * FROM Board WHERE BoardRelationDisplayValue != ''
SELECT * FROM Board WHERE CountryCode = 'US'
SELECT * FROM Board WHERE CountryCode != 'US'
SELECT * FROM Board WHERE CountryCode IN ('US','UK')
SELECT * FROM Board WHERE CountryCode NOT IN ('US','UK')
SELECT * FROM Board WHERE CountryCode IS NULL
SELECT * FROM Board WHERE CountryCode IS NOT NULL
SELECT * FROM Board WHERE DependentOnDisplayValue = '[]'
SELECT * FROM Board WHERE DependentOnDisplayValue != '[]'
SELECT * FROM Board WHERE Hour='07:32:00'
SELECT * FROM Board WHERE Numbers IN (12, 55)
SELECT * FROM Board WHERE Numbers = 10
SELECT * FROM Board WHERE Numbers != 10
SELECT * FROM Board WHERE Numbers > 10
SELECT * FROM Board WHERE Numbers >= 10
SELECT * FROM Board WHERE Numbers < 10
SELECT * FROM Board WHERE Numbers <= 10
SELECT * FROM Board WHERE Text='test' AND Numbers=10
SELECT * FROM Board WHERE Text='test' OR Numbers=10
SELECT * FROM Board ORDER BY Item
SELECT * FROM Board ORDER BY CreatedAt
SELECT * FROM Board ORDER BY UpdatedAt
SELECT * FROM Board ORDER BY Numbers ASC NULLS LAST

INSERT

The following inputs can be used in INSERT statements:

GroupId, ItemName, CreateLabelsIfMissing, BoardId, BoardRelationLinkedItemIds, Checkbox, CountryCode, CountryName, DateDate, DateDatetime, DependencyLinkedItemsIds, DropdownText, DropdownValue, Email, EmailLabel, Hour, LinkUrl, LinkUrlText, LocationLat, LocationLng, LocationAddress, LongText, Numbers, PeopleValue, PhoneCountryShortName, Phone, Rating, StatusIndex, StatusLabel, TagsIds, Text, TimelineFrom, TimelineTo, WeekStartDate, WeekEndDate, WorldClock 

INSERT INTO Board (Item,GroupId,Check) VALUES ('Test','topics',true)
INSERT INTO Board (Item,GroupId,CountryCode,CountryName) VALUES ('Test','topics','US','United States')
INSERT INTO Board (Item,GroupId,Date) VALUES ('Test','topics','2023-11-16')
INSERT INTO Board (Item,GroupId,Datetime) VALUES ('Test','topics','2023-11-16 09:00:00.0-05:00')
INSERT INTO Board (Item,GroupId,DependentLinkedItemsIds) VALUES ('Test','topics','[5561036960, 5561037791]')
INSERT INTO Board (Item,GroupId,DropdownText) VALUES ('Test','topics','label1,label2')
INSERT INTO Board (Item,GroupId,DropdownText,CreateLabelsIfMissing) VALUES ('Test','topics','label3,label4','true')
INSERT INTO Board (Item,GroupId,DropdownValue) VALUES ('Test','topics','{"ids":[1,2]}')
INSERT INTO Board (Item,GroupId,Email,EmailLabel) VALUES ('Test','topics','[email protected]','Mail')
INSERT INTO Board (Item,GroupId,Hour) VALUES ('Test','topics','09:00:00')
INSERT INTO Board (Item,GroupId,LinkUrl,LinkUrlText) VALUES ('Test','topics','https://www.test.com/', 'TestLink')
INSERT INTO Board (Item,GroupId,LocationLat,LocationLng,LocationAddress) VALUES ('Test','topics',29.9772962,31.1324955,'Giza Pyramid Complex')
INSERT INTO Board (Item,GroupId,LongText) VALUES ('Test','topics','TestText')
INSERT INTO Board (Item,GroupId,Numbers) VALUES ('Test','topics','-10')
INSERT INTO Board (Item,GroupId,PeopleValue) VALUES ('Test','topics','{"personsAndTeams":[{"id":51688059,"kind":"person"},{"id":51085546,"kind":"person"}]}')
INSERT INTO Board (Item,GroupId,PhoneCountryShortName,Phone) VALUES ('Test','topics','US','12345678900')
INSERT INTO Board (Item,GroupId,Rating) VALUES ('Test','topics','5')
INSERT INTO Board (Item,GroupId,StatusIndex) VALUES ('Test','topics','2')
INSERT INTO Board (Item,GroupId,StatusIndex,StatusLabel,CreateLabelsIfMissing) VALUES ('Test','topics','15','NewLabel','true')
INSERT INTO Board (Item,GroupId,StatusLabel) VALUES ('Test','topics','Working on it')
INSERT INTO Board (Item,GroupId,TagsIds) VALUES ('Test','topics','[20826704,20826705]')
INSERT INTO Board (Item,GroupId,BoardLinkedItemIds) VALUES ('Test','topics','[5561037791,5561036960]')
INSERT INTO Board (Item,GroupId,Text) VALUES ('Test','topics','TestText')
INSERT INTO Board (Item,GroupId,TimelineFrom,TimelineTo) VALUES ('Test','topics','2023-11-01','2023-12-04')
INSERT INTO Board (Item,GroupId,WeekStartDate,WeekEndDate) VALUES ('Test','topics','2023-11-05','2023-11-11')
INSERT INTO Board (Item,GroupId,World_Clock) VALUES ('Test','topics','Africa/Algiers')
INSERT INTO Board (Item, RelativeTo, PositionRelativeMethod) VALUES ('new', '7161109809', 'before_at')

UPDATE

The following inputs can be used in UPDATE statements:

CreateLabelsIfMissing, BoardId, BoardRelationLinkedItemIds, Checkbox, CountryCode, CountryName, DateDate, DateDatetime, DependencyLinkedItemsIds, DropdownText, DropdownValue, Email, EmailLabel, Hour, LinkUrl, LinkUrlText, LocationLat, LocationLng, LocationAddress, LongText, Numbers, PeopleValue, PhoneCountryShortName, Phone, Rating, StatusIndex, StatusLabel, TagsIds, Text, TimelineFrom, TimelineTo, WeekStartDate, WeekEndDate, WorldClock, GroupId, State

Note that GroupId, State and column values cannot be updated in the same statement.

Also note that certain types require specifying all related columns to update the object's value (this includes Email, Link, Location, Phone, Timeline, Week). 

UPDATE Board SET GroupId='new_group' WHERE ItemId='5562458608'
UPDATE Board SET State='archived' WHERE ItemId='5562458608'
UPDATE Board SET Check = false WHERE ItemId='5561890829'
UPDATE Board SET CountryCode='US',CountryName='United States' WHERE ItemId='5561890829'
UPDATE Board SET CountryCode=NULL,CountryName=NULL WHERE ItemId='5561890829'
UPDATE Board SET Date='2023-11-25' WHERE ItemId='5561890829'
UPDATE Board SET Date=NULL WHERE ItemId='5561890829'
UPDATE Board SET Datetime='2023-11-23 04:00:00.0' WHERE ItemId='5561890829'
UPDATE Board SET DependentLinkedItemsIds='[5562459384, 5562458774]' WHERE ItemId='5561890829'
UPDATE Board SET DependentLinkedItemsIds=NULL WHERE ItemId='5561890829'
UPDATE Board SET DropdownText='Label1,Label2' WHERE ItemId='5561890829'
UPDATE Board SET DropdownText='Label6,Label7',CreateLabelsIfMissing='true' WHERE ItemId='5561890829'
UPDATE Board SET DropdownValue='{"ids":[2,3]}' WHERE ItemId='5561890829'
UPDATE Board SET DropdownValue=NULL WHERE ItemId='5561890829'
UPDATE Board SET Email='[email protected]',EmailLabel='NewMail' WHERE ItemId='5561890829'
UPDATE Board SET Email=NULL,EmailLabel=NULL WHERE ItemId='5561890829'
UPDATE Board SET Hour='21:00:00' WHERE ItemId='5561890829'
UPDATE Board SET Hour=NULL WHERE ItemId='5561890829'
UPDATE Board SET LinkUrl='https://www.test.com',LinkUrlText='TestText' WHERE ItemId='5561890829'
UPDATE Board SET LinkUrl=NULL,LinkUrlText=NULL WHERE ItemId='5561890829'
UPDATE Board SET LocationAddress=NULL,LocationLat=NULL,LocationLng=NULL WHERE ItemId='5561890829'
UPDATE Board SET LocationLat='82',LocationLng='135',LocationAddress='TestLocation' WHERE ItemId='5561890829'
UPDATE Board SET LongText='changed text' WHERE ItemId='5561890829'
UPDATE Board SET LongText=NULL WHERE ItemId='5561890829'
UPDATE Board SET Numbers=10 WHERE ItemId='5561890829'
UPDATE Board SET Numbers=NULL WHERE ItemId='5561890829'
UPDATE Board SET PeopleValue='{"personsAndTeams":[{"id":51085546,"kind":"person"},{"id":961021,"kind":"team"}]}' WHERE ItemId='5561890829'
UPDATE Board SET PeopleValue=NULL WHERE ItemId='5561890829'
UPDATE Board SET Phone=NULL,PhoneCountryShortName=NULL WHERE ItemId='5561890829'
UPDATE Board SET PhoneCountryShortName='US',Phone='16102347959' WHERE ItemId='5561890829'
UPDATE Board SET Rating='1' WHERE ItemId='5561890829'
UPDATE Board SET Rating=NULL WHERE ItemId='5561890829'
UPDATE Board SET StatusIndex='2' WHERE ItemId='5561890829'
UPDATE Board SET StatusIndex=NULL WHERE ItemId='5561890829'
UPDATE Board SET StatusLabel='NewLabel',StatusIndex='159',CreateLabelsIfMissing='true' WHERE ItemId='5561890829'
UPDATE Board SET StatusLabel='Working on it' WHERE ItemId='5561890829'
UPDATE Board SET TagsIds='[20826705,20833783]' WHERE ItemId='5561890829'
UPDATE Board SET TagsIds=NULL WHERE ItemId='5561890829'
UPDATE Board SET BoardLinkedItemIds = NULL WHERE ItemId='5561890829'
UPDATE Board SET BoardLinkedItemIds='[5562459384, 5561890829, 5562458774]' WHERE ItemId='5561890829'
UPDATE Board SET Text='changedText' WHERE ItemId='5561890829'
UPDATE Board SET Text=NULL WHERE ItemId='5561890829'
UPDATE Board SET TimelineFrom='2023-01-01', TimelineTo='2023-10-10' WHERE ItemId='5561890829'
UPDATE Board SET TimelineFrom=NULL, TimelineTo=NULL WHERE ItemId='5561890829'
UPDATE Board SET WeekStartDate='2023-11-13', WeekEndDate='2023-11-19' WHERE ItemId='5561890829'
UPDATE Board SET WeekStartDate=NULL, WeekEndDate=NULL WHERE ItemId='5561890829'
UPDATE Board SET WorldClock='America/New York' WHERE ItemId='5561890829'
UPDATE Board SET WorldClock=NULL WHERE ItemId='5561890829'

DELETE

You can delete items by specifying the ItemId. 

DELETE FROM Board WHERE ItemId='5561890829'

Columns

Name Type ReadOnly References Description
ItemId [KEY] String False

The item's unique identifier.

CreatorId String False

The item's creator unique identifier.

CreatedAt Datetime False

The item's create date.

UpdatedAt Datetime False

The item's last updated date.

State String False

The item's state (all, active, archived, deleted).

RelativeLink String False

The item's relative path.

ItemEmail String False

The item's email.

Url String False

The item's link.

AssetsIds String False

A comma separated list of the items' assets/files unique identifiers.

GroupId String False

The title of the group that contains this item.

GroupTitle String False

The title of the group that contains this item.

Item String False

The item's name.

BoardRelationDisplayValue String False

A string representing all the names of the linked items, separated by commas.

BoardRelationLinkedItemIds String False

The linked item IDs.

ButtonLabel String False

The button's label.

ButtonColor String False

The button's color in hex value.

ButtonValue String False

The column's value in json format.

Check Bool False

The column's boolean value.

ColorPickerColor String False

The color in hex value.

ColorPickerValue String False

The column's value in json format.

CountryCode String False

The country's two-letter code.

CountryName String False

The country's name.

DateIcon String False

The string representation of selected icon.

Date Date False

The column's date value.

Datetime Datetime False

The column's datetime value.

DependencyDisplayValue String False

Choose the item your task will be dependent on. If the “dependent on” item’s date is changing, the other dates will adjust automatically A string representing all the names of the linked items, separated by commas.

DependencyLinkedItemsIds String False

Choose the item your task will be dependent on. If the “dependent on” item’s date is changing, the other dates will adjust automatically The linked items ids.

DocFileId String False

The file's unique identifier.

DocFileCreatorId String False

The ID of user who created the file.

DocFileObjectId String False

The associated board or object's unique identifier.

DocFileUrl String False

The file's url.

DocFileDocId String False

The unique identifier of the document associated with the file.

DropdownText String False

The column's textual value in string form.

DropdownValue String False

The column's value in json format.

Email String False

The column's email value.

EmailLabel String False

The column's text value. It can be the same as email when user didn't enter any text.

FileText String False

The column's textual value in string form.

FileValue String False

The column's value in json format.

Hour Time False

The column's time value.

IntegrationIssueId String False

ID of the issue.

IntegrationEntityId String False

ID of the entity.

IntegrationIssueApiUrl String False

URL of the issue.

IntegrationValue String False

The column's value in json format.

LastUpdated String False

ID of the user who updated the item.

LinkUrl String False

Url.

LinkUrlText String False

Url text.

LocationLat Double False

Latitude.

LocationLng Double False

Longitude.

LocationPlaceId String False

Place ID of the location.

LocationAddress String False

Address.

LocationCity String False

City.

LocationCityShort String False

City.

LocationCountry String False

Country.

LocationCountryShort String False

Country short name (e.g. PE for Peru).

LocationStreet String False

Street.

LocationStreetNumber String False

Number of building in the street.

LocationStreetNumberShort String False

Short number of building in the street.

LocationStreetShort String False

Street.

LongText String False

Long text.

MirrorDisplayValue String False

The display value of the mirrored items' column.

MirroredItems String False

The mirrored items.

Numbers Double False

Number.

PeopleText String False

The column's textual value in string form.

PeopleValue String False

The column's value in json format.

PhoneCountryShortName String False

ISO-2 country code.

Phone String False

Phone number.

Rating Int False

Rating value.

StatusIndex Int False

The index of the status in the board.

StatusIsDone Bool False

Whether the status is done.

StatusLabel String False

The label of the status.

StatusUpdateId String False

The ID of an update attached to the status.

StatusLabelStyleColor String False

The style of the status label. The label's color in hex format.

StatusLabelStyleBorder String False

The style of the status label. The label's border color in hex format.

TagsText String False

The column's textual value in string form.

TagsIds String False

Tag IDs.

Text String False

The column's textual value in string form.

TimeTrackingRunning Bool False

Whether the time tracker is running.

TimeTrackingStartedAt Datetime False

The date when the time tracker was started.

TimeTrackingDuration Int False

Total duration of the time tracker in seconds.

TimelineFrom Date False

The start date of the timeline.

TimelineTo Date False

The end date of the timeline.

TimelineVisualizationType String False

The visualization type for the timeline.

VoteCount Int False

The total number of votes.

VoterIds String False

A list of IDs of users who voted.

WeekStartDate Date False

The start date of the week.

WeekEndDate Date False

The end date of the week.

WorldClock String False

Timezone.

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

Name Type Description
RelativeTo String

The item to set the position next to.

PositionRelativeMethod String

The position relative method to another item (before_at / after_at).

CreateLabelsIfMissing Bool

Create Status/Dropdown labels if they're missing. Requires permission to change board structure.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9062