Establishing a Connection

Create a connection to Asana by navigating to the Connections page in the Sync App application and selecting the corresponding icon in the Add Connections panel. If the Asana icon is not available, click the Add More icon to download and install the Asana connector from the CData site.

Required properties are listed under the Settings tab. The Advanced tab lists connection properties that are not typically required.

Connecting to Asana

You can optionally set the following to refine the data returned from Asana.

WorkspaceId: Set this to the globally unique identifier (gid) associated with your Asana Workspace to only return projects from the specified Workspace. To get your Workspace Id, navigate to https://app.asana.com/api/1.0/workspaces while logged into Asana. This displays a JSON object containing your Workspace name and Id.
ProjectId: Set this to the globally unique identifier (gid) associated with your Asana Project to only return data mapped under the specified Project. Project Ids can be found in the URL of your project's Overview page. This will be the numbers directly after /0/.

Authenticating to Asana

Asana uses the OAuth authentication standard.

User Accounts (OAuth)

AuthScheme must be set to OAuth in all user account flows.

Web Applications

When connecting via a Web application, you need to register a custom OAuth app with Asana. You can then use the Sync App to get and manage the OAuth token values. See Creating a Custom OAuth App for more information.

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

OAuthClientId: Set this to the client Id in your app settings.
OAuthClientSecret: Set this to the client secret in your app settings.

Then call stored procedures to complete the OAuth exchange:

Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the Redirect URI you specified in your app settings. The stored procedure returns the URL to the OAuth endpoint.
Navigate to the URL that the stored procedure returned in Step 1. Log in, and authorize the web application. After authenticating, the browser redirects you to the redirect URI. There will be a parameter called code appended to the redirect URI. Note the value of this parameter.
Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the code parameter in the query string of the redirect URI.

After you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the Sync App automatically refresh the OAuth access token, set the following on the first data connection.

InitiateOAuth: Set this to REFRESH.
OAuthClientId: Set this to the client Id in your app settings.
OAuthClientSecret: Set this to the client secret in your app settings.
OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
OAuthSettingsLocation: Set this to the path where you want the Sync App to save the OAuth values, which persist across connections.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token. Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

OAuthClientId: Set this to the client Id in your app settings.
OAuthClientSecret: Set this to the client secret in your app settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Advanced Features

This section details a selection of advanced features of the Asana Sync App.

User Defined Views

The Sync App allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

The Sync App offloads as much of the SELECT statement processing as possible to Asana and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

Logging

See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.

SSL Configuration

Customizing the SSL Configuration

By default, the Sync App attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

Other Proxies

Set the following properties:

To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
To tunnel the connection, set FirewallType to TUNNEL.
To authenticate, specify FirewallUser and FirewallPassword.
To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

Data Model

Overview

This section shows the available API objects and provides more information on executing SQL to Asana APIs.

Key Features

The Sync App models Asana entities as relational views, allowing you to write SQL to query Asana data.
Stored procedures allow you to execute operations to Asana, including retrieving the access token and keeping it refreshed in OAuth 2.0.
Live connectivity to these objects means any changes to your Asana account are immediately reflected when using the Sync App.
IncludeCustomFields connection property allows you to retrieve custom fields for Tasks view. Set this property to True, to enable this feature.

Views

Views describes the available views. Views are statically defined to model Projects, Users, Workspaces, and more.

Stored Procedures

Stored Procedures are function-like interfaces to Asana. Stored procedures allow you to execute operations to Asana, including downloading documents and moving envelopes.

Tables

The Sync App models the data in Asana into a list of tables that can be queried using standard SQL statements.

Generally, querying Asana tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

Asana Connector for CData Sync Tables


Name	Description
Attachments	To Delete and Query from the Attachments in the tasks.
PortfolioItems	To Create, delete, and Query Items in the Portfolio.
PortfolioMembership	To Create, Delete and Query memberships in the Portfolio.
Portfolios	To Create, update, delete, and query from Portfolios table.
ProjectMembership	To Create, Delete and Query memberships in the Project.
Projects	To Create, Update, Delete and Query the Projects table.
ProjectStatuses	To Create, Delete and Query from ProjectStatuses table. A project status is an update on the progress of a particular project, and is sent out to all project followers when created.
Sections	To Create, Update, Delete, and Query from Sections table. A section is a subdivision of a project that groups tasks together.
Stories	To Create, Update, Delete and Query from stories table. A story represents an activity associated with an object in the Asana system. Stories are generated by the system whenever users take actions such as creating or assigning tasks, or moving tasks between projects.
SubTasks	To Create, update, delete, and query from SubTasks table.
Tags	To Create, Update and Query from Tags table. A tag is a label that can be attached to any task in Asana. It exists in a single workspace.
Tasks	To Create, Update, Delete and Query from Tasks table.
TeamMembership	To represents a users connection to a team.
Teams	A team is used to group related projects and people together within an organization. Each project in an organization is associated with a team.
WorkspaceMembership	To Create, Delete and Query memberships in the Workspace.
Workspaces	To Update and Query from Workspaces table. A workspace is the highest-level organizational unit in Asana. All projects and tasks have an associated workspace.

Attachments

To Delete and Query from the Attachments in the tasks.

Table Specific Information

Select

The Sync App will use the Asana API to process WHERE clause conditions built with the following column and operator. The TaskId or Id is required to make a request and the rest of the filter is executed client side within the Sync App.

Id supports the '=' comparison.
TaskId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Attachments WHERE Id = '1134618934491707'

SELECT * FROM Attachments WHERE TaskId = '1128092964672854'

Delete

Following is an example of how to delete from Attachments table:

DELETE FROM Attachments WHERE Id = '1134618934491707'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the attachment.
Name	String	True		The name of the file.
Host	String	True		The service hosting the attachment. Valid values are asana, dropbox, gdrive and box.
CreatedAt	Datetime	True		The time at which this attachment was uploaded.
ViewURL	String	True		The URL where the attachment can be viewed, which may be friendlier to users in a browser than just directing them to a raw file.
DownloadURL	String	True		May be null if the attachment is hosted by box. If present, this URL may only be valid for 1 hour from the time of retrieval. You should avoid persisting this URL somewhere and just refresh it on demand to ensure you do not keep stale URLs.
TaskId	String	True	Tasks.Id	The task this attachment is attached to.
ResourceType	String	True		The resource type of this resource.

PortfolioItems

To Create, delete, and Query Items in the Portfolio.

Table Specific Information

Select

The Sync App will use the Asana API to process WHERE clause conditions built with the following column and operator. The PortfolioId is required to make a request and rest of the filter is executed client side within the Sync App.

PortfolioId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM PortfolioItems WHERE PortfolioId = '1173026173301164'

Insert

ProjectId and PortfolioId is a mandatory column for inserting into PortfolioItems table. For example:

INSERT INTO PortfolioItems (ProjectId, PortfolioId) VALUES ('1173023668354420', '1173026173301164')

Delete

Following is an example of how to delete from PortfolioItems table:

DELETE FROM PortfolioItems WHERE ProjectId = '1173023668354420' AND PortfolioId = '1173026173301164'

Columns


Name	Type	ReadOnly	References	Description
Id	String	True		Globally unique ID of the Portfolio Item.
Name	String	True		Name of the Item.
PortfolioId [KEY]	String	True	Portfolios.Id	Globally unique ID of the portfolio.
OwnerId	String	True		Globally unique ID of the Owner.
StartOn	Date	True		The day on which work for this project begins, or null if the project has no start date.
DueOn	Date	True		The day on which this project is due. This takes a date with format YYYY-MM-DD.
ProjectId [KEY]	String	True	Projects.Id	Globally unique ID of the Project. This column is a Pseudocolmn and its used for INSERT and UPDATE.

PortfolioMembership

To Create, Delete and Query memberships in the Portfolio.

Table Specific Information

Select

The Sync App will use the Asana API to process WHERE clause conditions built with the following column and operator. The [WorkspaceId AND UserId] OR PortfolioId OR Id is required to make a request and rest of the filter is executed client side within the Sync App.

Id supports the '=' comparison.
WorkspaceId supports the '=' comparison.
UserId supports the '=' comparison.
PortfolioId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM PortfolioMembership WHERE WorkspaceId='1172686716695308' AND UserId='1172686741877880'

SELECT * FROM PortfolioMembership WHERE PortfolioId='1172684730399265'

SELECT * FROM PortfolioMembership WHERE Id='1172684730399266'

Insert

PortfolioId and UserId is a mandatory column for inserting into PortfolioMembership table. For example:

INSERT INTO PortfolioMembership (PortfolioId, UserId)  VALUES ('1172684730399265', '1172686741877880')

Delete

Following is an example of how to delete from PortfolioMembership table:

DELETE FROM PortfolioMembership WHERE PortfolioId='1172684730399265' AND UserId='1172686741877880'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the portfoolio membership.
PortfolioId [KEY]	String	True	Portfolios.Id	The portfolio the user is a member of.
PortfolioName	String	True		The name of the portfolio.
UserId [KEY]	String	True	Users.Id	Globally unique ID of the user.
UserName	String	True		The users name.
ResourceType	String	True		The resource type of this resource.
WorkspaceId	String	True	Workspaces.Id	Globally unique ID of the workspace. This column is a Pseudocolmn and its used in SELECT Query.

Portfolios

To Create, update, delete, and query from Portfolios table.

Table Specific Information

Select

The Sync App will use the Asana API to process WHERE clause conditions built with the following column and operator. The [WorkspaceId AND OwnerId] OR Id is required to make a request and rest of the filter is executed client side within the Sync App.

Id supports the '=' comparison.
WorkspaceId supports the '=' comparison.
OwnerId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Portfolios WHERE WorkspaceId = '1172686716695308' AND OwnerId = '1172686741877880'

SELECT * FROM Portfolios WHERE Id = '1172684730399265'

Insert

WorkspaceId is a mandatory column for inserting into Portfolios table. For example:

INSERT INTO Portfolios (Name, Color, WorkspaceId)  VALUES ('Test', 'dark-pink','1172686716695308'

Update

Following is an example of how to update a Portfolios table:

"UPDATE Portfolios SET Name = 'IT-Test' WHERE Id = '1173026173301164'

Delete

Following is an example of how to delete from Portfolios table:

DELETE FROM Portfolios WHERE Id = '1172684730399265'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the portfolio.
Name	String	False		Name of the portfolio.
Color	String	False		Color of the portfolio. The allowed values are dark-pink, dark-green, dark-blue, dark-red, dark-teal, dark-brown, dark-orange, dark-purple, dark-warm-gray, light-pink, light-green, light-blue, light-red, light-teal, light-brown, light-orange, light-purple, light-warm-gray.
CreatedAt	Datetime	True		The time at which this portfolio was created.
CreatorId	String	True		Globally unique ID of the user created this portfolio.
CreatorName	String	True		The users name.
Members	String	False		Array of users who are members of this portfolio.
OwnerId	String	False	Users.Id	Globally unique ID of the Owner.
OwnerName	String	True		The Owners Name.
ResourceType	String	True		The base type of this resource.
WorkspaceId	String	False	Workspaces.Id	The workspace or organization that the portfolio belongs to.
WorkspaceName	String	True		Name of the workspace.

ProjectMembership

To Create, Delete and Query memberships in the Project.

Table Specific Information

Select

The Sync App will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

Id supports the '=' comparison.
ProjectId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM ProjectMembership WHERE Id = '1127092449876445'

SELECT * FROM ProjectMembership WHERE ProjectId = '1127092449876444'

Insert

UserId and ProjectId is a mandatory column for inserting into ProjectMembership table. For example:

INSERT INTO ProjectMembership (UserId, ProjectId) VALUES ('1161963899354167','1128092964672848')

Delete

Following is an example of how to delete from ProjectMembership table:

DELETE FROM ProjectMembership WHERE UserId = '1161963899354167' AND ProjectId = '1128092964672848'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the project membership.
ProjectId [KEY]	String	False	Projects.Id	The project the user is a member of.
WriteAccess	String	True		Whether the user has full access to the project or has comment-only access.
UserId [KEY]	String	False	Users.Id	The user in the membership.
ResourceType	String	True		The resource type of this resource.

Projects

To Create, Update, Delete and Query the Projects table.

Table Specific Information

Select

The Sync App will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

Id supports the '=' comparison.
workspaceId supports the '=' comparison.
TaskId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM projects WHERE workspaceId = '1126938837961830'

SELECT * FROM projects WHERE Id = '1128092864672888'

SELECT * FROM projects WHERE TaskId = '1130092964672468'

Insert

WorkspaceId or TeamId is a mandatory column for inserting into Projects table. For example:

INSERT INTO Projects (Name, Color, StartOn, WorkspaceId, DueOn, public, UserId) values ('ProjectName', 'dark-pink', '2020-03-01', '1126938837961830', '2020-04-30', false, '1130062547672468')

Update

Following is an example of how to update a Projects table:

UPDATE Projects SET TeamId = '1129514033997890' WHERE Id = '1128092864672888'

Delete

Following is an example of how to delete from Projects table:

DELETE FROM Projects WHERE Id = '1128092864672888'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the project.
Name	String	False		Name of the project. This is generally a short sentence fragment that fits on a line in the UI for maximum readability. However, it can be longer.
Color	String	False		Color of the project. Must be either null or one of: dark-pink, dark-green, dark-blue, dark-red, dark-teal, dark-brown, dark-orange, dark-purple, dark-warm-gray, light-pink, light-green, light-blue, light-red, light-teal, light-yellow, light-orange, light-purple, light-warm-gray. The allowed values are dark-pink, dark-green, dark-blue, dark-red, dark-teal, dark-brown, dark-orange, dark-purple, dark-warm-gray, light-pink, light-green, light-blue, light-red, light-teal, light-brown, light-orange, light-purple, light-warm-gray, green, yellow, red.
CreatedAt	Datetime	True		The time at which this project was created.
ModifiedAt	Datetime	True		The time at which this project was last modified.
StartOn	Date	False		The day on which work for this project begins, or null if the project has no start date. This takes a date with YYYY-MM-DD format. Note: due_on must be present in the request when setting or unsetting the start_on parameter.
DueOn	Date	False		The day on which this project is due. This takes a date with format YYYY-MM-DD.
DefaultView	String	False		The default view of a project. The allowed values are list, board, calendar, timeline.
Followers	String	False		Followers are a subset of members who receive all notifications for a project.
Archived	Boolean	False		True if the project is archived, false if not. Archived projects do not show in the UI by default and may be treated differently for queries.
UserId	String	False	Users.Id	The current owner of the project.
TeamId	String	False	Teams.Id	The team that this project is shared with. This field only exists for projects in organizations.
WorkspaceId	String	True	Workspaces.Id	The workspace or organization this project is associated with. Once created, projects cannot be moved to a different workspace. This attribute can only be specified at creation time.
Public	Boolean	False		True if the project is public to the organization. If false, do not share this project with other users in this organization without explicitly checking to see if they have access.
Notes	String	False		More detailed, free-form textual information associated with the project.
HTMLNotes	String	False		The notes of the project with formatting as HTML.
IsTemplate	Boolean	False		Determines if the project is a template.
Layout	String	True		The layout (board or list view) of a project.
Members	String	True		Array of users who are members of this project.
SectionMigrationStatus	String	True		The section migration status of this project.
TaskId	String	True		Globally unique ID of the Task.

ProjectStatuses

To Create, Delete and Query from ProjectStatuses table. A project status is an update on the progress of a particular project, and is sent out to all project followers when created.

Table Specific Information

Select

The Sync App will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

Id supports the '=' comparison.
ProjectId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM ProjectStatuses WHERE Id = '1127092449876445'

SELECT * FROM ProjectStatuses WHERE ProjectId = '1127092449876444'

Insert

ProjectId is a mandatory column for inserting into ProjectStatuses table. For example:

INSERT INTO projectstatuses (Color, Title, Text, ProjectId) VALUES ('red', 'Status1', 'StatusNotes','1127092449876444')

Delete

Following is an example of how to delete from ProjectStatuses table:

DELETE FROM ProjectStatuses WHERE Id = '1127092449876445'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the project status update.
Color	String	True		The color associated with the status update. The allowed values are green, yellow, red.
Title	String	True		The title of the project status update.
Text	String	True		The text content of the status update.
CreatedAt	Datetime	True		The time at which the status update was created.
CreatedBy	String	True		The creator of the status update.
ProjectId	String	True	Projects.Id	Globally unique ID of the project.
ResourceType	String	True		The resource type of this resource. The value for this resource is always project_status.
HTMLText	String	True		The notes of the project with formatting as HTML.

Sections

To Create, Update, Delete, and Query from Sections table. A section is a subdivision of a project that groups tasks together.

Table Specific Information

Select

The Sync App will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

Id supports the '=' comparison.
ProjectId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Sections WHERE Id = '1127092449876457'

SELECT * FROM Sections WHERE ProjectId = '1128092964672848'

Insert

ProjectId is a mandatory column for inserting into Sections table. For example:

INSERT INTO Sections (Name, ProjectId) VALUES ('SectionName', '1128092964672848')

Update

Following is an example of how to update a Sections table:

UPDATE Sections SET Name='IT-Pending' WHERE Id='1127092449876457'

Delete

Following is an example of how to delete from Sections table:

DELETE FROM Sections WHERE Id = '1127092449876457'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the section.
Name	String	False		The name of the section.
CreatedAt	Datetime	True		The time at which the section was created.
ResourceType	String	True		The resource type of this resource.
ProjectId	String	False	Projects.Id	The section under the project.

Stories

To Create, Update, Delete and Query from stories table. A story represents an activity associated with an object in the Asana system. Stories are generated by the system whenever users take actions such as creating or assigning tasks, or moving tasks between projects.

Table Specific Information

Select

The Sync App will use the Asana API to process WHERE clause conditions built with the following column and operator. The TaskId or Id is required to make a request and rest of the filter is executed client side within the Sync App.

Id supports the '=' comparison.
TaskId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Stories WHERE Id = '1126938691750986'

SELECT * FROM Stories WHERE TaskId = '1129514033997892'

Insert

TaskId is a mandatory column for inserting into Stories table. For example:

INSERT INTO Stories (Text, TaskId, IsPinned) VALUES ('InProgress', '1129514033997892', false)

Update

Following is an example of how to update a Stories table:

UPDATE Stories SET Text = 'story line' WHERE Id = '1126938691750986'

Delete

Following is an example of how to delete from Stories table:

DELETE FROM Stories WHERE Id = '1126938691750986'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the story.
Text	String	False		Formatted text for the story or comment.
CreatedAt	Datetime	True		The time at which this story was created.
creatorId	String	True		The user who created the story.
creatorName	String	False	Users.Name	The user who created the story.
Source	String	True		The component of the Asana product the user used to create the story.
TaskId	String	False	Tasks.Id	The story under the task.
Liked	Boolean	True		True if the story is liked by the authorized user, false if not.
IsPinned	Boolean	False		This field is only present on comment and attachment stories.
IsEdited	Boolean	True		Whether the text of the story has been edited after creation.
ResourceType	String	True		The resource type of this resource.
ResourceSubType	String	True		The type of story. This provides fine-grained information about what triggered the story creation.
HtmlText	String	True		HTML formatted text for a comment.
DuplicateTaskOf	String	True		The task is the basic object around which many operations in Asana are centered.
DependencyTaskId	String	True		The task is the basic object around which many operations in Asana are centered.
NumberofLikes	Integer	True		The number of users who have liked this story.
Likes	String	True		Array of likes for users who have liked this story.

SubTasks

To Create, update, delete, and query from SubTasks table.

Table Specific Information

Select

The Sync App will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

ParentTaskId supports the '=' comparison.

For example, the following query is processed on server side and it is a required filter:

SELECT * FROM SubTasks WHERE ParentTaskId = '1127092449876457'

A simple SELECT query will only return the first level of subtasks.

SELECT * FROM SubTasks

There may be up to five levels of subtasks below a task. (Note, Asana does not recommend using sub-subtasks). If additional levels of subtasks are needed, a subquery can be used:

SELECT * FROM SubTasks WHERE ParentTaskId IN (SELECT Id FROM SubTasks)

When executing a query without a ParentTaskId filter, due to the Asana API design, subtasks are only retrieved on a per task basis. The Sync App will first query the workspace to retrieve a list of projects. A separate request will then be made for each project to retrieve all the applicable tasks. Another request will be made for each task to retrieve any subtasks associated with the task. In theory, this is similar to executing the following query:

SELECT * FROM SubTasks WHERE ParentTaskId IN (SELECT Id FROM Tasks WHERE ProjectId IN (SELECT Id FROM Projects))

Due to the number of required requests, performance may be impacted.

Insert

ParentTaskId is a mandatory column for inserting into Projects table. For example:

INSERT INTO SubTasks (ParentTaskId, Name, Assignee, AssigneeStatus, startOn, DueOn, Completed, ProjectId, Notes, ApprovalStatus, Followers) VALUES ('1167713816822372', 'subtask1', '1167715005778471', 'later', '2020-03-25', '2020-04-01',false,1167713816822364, 'SubTask Notes', 'changes_requested', '1167715005778471')

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the Subtask.
ParentTaskId	String	True		Globally unique ID of the task.
Name	String	False		Name of the Subtask.
Assignee	String	False		User to which this subtask is assigned.
AssigneeStatus	String	False		Scheduling status of this subtask for the user it is assigned to. The allowed values are today, upcoming, later, new, inbox.
CreatedAt	Datetime	True		The time at which this subtask was created.
ModifiedAt	Datetime	True		The time at which this subtask was last modified.
startOn	Date	False		The day on which work begins for the task , or null if the task has no start date. This takes a date with YYYY-MM-DD format.
DueAt	Datetime	False		Date and time on which this subtask is due, or null if the subtask has no due time.
DueOn	Date	False		Date on which this task is due, or null if the task has no due date. This takes a date with YYYY-MM-DD format.
Completed	Boolean	False		True if the subtask is currently marked complete, false if not.
CompletedAt	Datetime	True		The time at which this subtask was completed, or null if the subtask is incomplete.
completed_by	String	True		The user who completed this task.
ProjectId	String	False	Projects.Id	Globally unique ID of the Project.
WorkspaceId	String	False	Workspaces.Id	Globally unique ID of the Workspace.
Notes	String	False		More detailed, free-form textual information associated with the subtask.
ApprovalStatus	String	False		Reflects the approval status of this task. The allowed values are pending, approved, rejected, changes_requested.
ResourceSubType	String	False		The subtype of this resource. Different subtypes retain many of the same fields and behavior, but may render differently in Asana or represent resources with different semantic meaning. The allowed values are default_task, milestone, section, approval.
HtmlNotes	String	False		The notes of the text with formatting as HTML.
Dependencies	String	True		Array of resources referencing tasks that this task depends on.
Memberships	String	True		Array of projects this task is associated with and the section it is in.
Dependents	String	True		Array of resources referencing tasks that depend on this task.
Liked	Boolean	False		True if the task is liked by the authorized user, false if not.
NumberofLikes	Integer	True		The number of users who have liked this Task.
LikedBy	String	True		Array of likes for users who have liked this task.
ResourceType	String	True		The base type of this resource.
IsRenderedAsSeparator	Boolean	True		In some contexts tasks can be rendered as a visual separator.
Followers	String	False		Array of users following this task.
Tags	String	False		Array of tags associated with this task.

Tasks

To Create, Update, Delete and Query from Tasks table.

Table Specific Information

Select

The Sync App will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

Id supports the '=' comparison.
ProjectId supports the '=' comparison.
Assignee supports the '=' comparison.
TagId supports the '=' comparison.
SectionId supports the '=' comparison.
UserTaskListId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Tasks WHERE Id = '1127092449876457'

SELECT * FROM Tasks WHERE ProjectId = '1128092964672848'

SELECT * FROM Tasks WHERE Assignee = '1126938691750986' AND WorkspaceId = '1126938837961830'

SELECT * FROM Tasks WHERE Assignee = '1126938691750986'

SELECT * FROM tasks WHERE TagId = '1129517083601713'

SELECT * FROM tasks WHERE SectionId = '1127092449876457'

SELECT * FROM tasks WHERE UserTaskListId = '1126938837961837'

When executing a query without one of the above filters, due to the Asana API design, tasks must be retrieved per project. The Sync App will first query the workspace to retrieve a list of projects. A separate request will then be made for each project to retrieve all the applicable tasks. In theory, this is similar to executing the following query:

SELECT * FROM Tasks WHERE ProjectId IN (SELECT Id FROM Projects)

Due to the number of required requests, performance may be impacted. Note, tasks may also be orphaned and not belong to a project. The Sync App will not be able to retrieve orphaned tasks.

Insert

ProjectId is a mandatory column for inserting into Tasks table. For example:

INSERT INTO Tasks (Name, Assignee, AssigneeStatus, startOn, DueOn, Completed, ProjectId, Notes, ApprovalStatus, Followers) VALUES ('Task1', '1167715005778471', 'later', '2020-03-25', '2020-04-01',false,1167713816822364, 'TaskNotes', 'changes_requested', '1167715005778471')

Update

Following is an example of how to update a Tasks table:

UPDATE Tasks SET Browser='ie' WHERE Id = '1167713816822371'

Delete

Following is an example of how to delete from Tasks table:

DELETE FROM Tasks WHERE Id = '1167713816822371'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the task.
Name	String	False		Name of the task.
Assignee	String	False		User to which this task is assigned.
AssigneeStatus	String	False		Scheduling status of this task for the user it is assigned to. The allowed values are today, upcoming, later, new, inbox.
CreatedAt	Datetime	True		The time at which this task was created.
ModifiedAt	Datetime	True		The time at which this task was last modified.
startOn	Date	False		Date and time on which this task is due, or null if the task has no due time.
DueAt	Datetime	False		Date and time on which this task is due, or null if the task has no due time.
DueOn	Date	False		Date on which this task is due, or null if the task has no due date. This takes a date with YYYY-MM-DD format.
Completed	Boolean	False		True if the task is currently marked complete, false if not.
CompletedAt	Datetime	True		The time at which this task was completed, or null if the task is incomplete.
CompletedBy	String	True		The user who completed this task.
ProjectId	String	False	Projects.Id	Projectid of this task is associated with.
Projects	String	False		Projects of this task is associated with.
WorkspaceId	String	False	Workspaces.Id	Globally unique ID of the Workspace.
Notes	String	False		More detailed, free-form textual information associated with the task.
NumberofSubTasks	Integer	True		The number of subtasks on this task.
ApprovalStatus	String	False		Reflects the approval status of this task. The allowed values are pending, approved, rejected, changes_requested.
ResourceSubType	String	False		The subtype of this resource. Different subtypes retain many of the same fields and behavior, but may render differently in Asana or represent resources with different semantic meaning. The allowed values are default_task, milestone, section, approval.
HtmlNotes	String	False		The notes of the text with formatting as HTML.
Memberships	String	True		Array of projects this task is associated with and the section it is in.
Dependencies	String	True		Array of resources referencing tasks that this task depends on.
Dependents	String	True		Array of resources referencing tasks that depend on this task.
Liked	Boolean	True		True if the task is liked by the authorized user, false if not.
NumberofLikes	Integer	True		The number of users who have liked this Task.
LikedBy	String	True		Array of likes for users who have liked this task.
ResourceType	String	True		The base type of this resource.
IsRenderedAsSeparator	Boolean	True		In some contexts tasks can be rendered as a visual separator.
Followers	String	True		Array of users following this task.
Tags	String	False		Array of tags associated with this task.
SectionId	String	True	Sections.Id	PseudoColumn: Globally unique ID of the section.
UserTaskListId	String	True	WorkspaceMembership.UserTaskListId	PseudoColumn: A user task list represents the tasks assigned to a particular user.
TagId	String	True	Tags.Id	PseudoColumn: Globally unique ID of the tag.

TeamMembership

To represents a users connection to a team.

Table Specific Information

Select

The Sync App will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

Id supports the '=' comparison.
UserId supports the '=' comparison.
WorkspaceId supports the '=' comparison.
TeamId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM TeamMembership WHERE UserId = '1126938691750986' AND WorkspaceId = '1126938837961830'

SELECT * FROM TeamMembership WHERE TeamId = '1126938837961832'

SELECT * FROM TeamMembership WHERE Id = '1126938837961832'

Insert

UserId and TeamId is a mandatory column for inserting into TeamMembership table. For example:

INSERT INTO TeamMembership (UserId, TeamId) VALUES ('1161963899354167','1126938837961832')

Delete

Following is an example of how to delete from TeamMembership table:

DELETE FROM TeamMembership WHERE UserId = '1161963899354167' AND TeamId = '1126938837961832'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique identifier of the resource, as a string.
userId	String	True		Globally unique ID of the user.
userName	String	True		The users name.
TeamId	String	True		Globally unique ID of the team.
TeamName	String	True		The name of the team.
IsGuest	Boolean	True		Describes if the user is a guest in the team.
ResourceType	String	True		The base type of this resource.
WorkspaceId	String	True		Globally unique ID of the workspace.

Teams

A team is used to group related projects and people together within an organization. Each project in an organization is associated with a team.

Table Specific Information

Select

The Sync App will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

Id supports the '=' comparison.
WorkspaceId supports the '=' comparison.
UserId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Teams WHERE Id = '1126938837961832'

SELECT * FROM Teams WHERE WorkspaceId = '1126938837961830'

SELECT * FROM Teams WHERE UserId = '1126938691750986' AND workspaceid = '1126938837961830'

Insert

WorkspaceId is a mandatory column for inserting into Teams table.In the WorkspaceId only the organisation ID can be used to create teams. Teams can not be created for workspaces which are not part of a organisation. For example:

INSERT INTO Teams (Name,Description,HTMLDescrption,WorkspaceId) Values ('Salessssk','sales developers should be members of this team.','<body> <em>All</em> developers should be members of this team.</body>','1126938837961830')

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the team.
Name	String	True		The name of the team.
Description	String	True		The description of the team.
WorkspaceId	String	True	Workspaces.Id	The workspace or organization this team is associated with.
HTMLDescrption	String	True		The description of the team with formatting as HTML.
ResourceType	String	True		The resource type of this resource.
UserId	String	True	Users.Id	Globally unique ID of the user.

WorkspaceMembership

To Create, Delete and Query memberships in the Workspace.

Table Specific Information

Select

The Sync App will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

Id supports the '=' comparison.
UserId supports the '=' comparison.
WorkspaceId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM WorkspaceMembership WHERE WorkspaceId = '1126938837961830'

SELECT * FROM WorkspaceMembership WHERE UserId = '1126938691750986'

SELECT * FROM WorkspaceMembership WHERE Id = '1126938837961834'

Insert

UserId and WorkspaceId is a mandatory column for inserting into WorkspaceMembership table. For example:

INSERT INTO WorkspaceMembership (UserId, WorkspaceId) VALUES ('1161963899354167','1126938837961830')

Delete

Following is an example of how to delete from WorkspaceMembership table:

DELETE FROM WorkspaceMembership WHERE UserId = '1161963899354167' AND WorkspaceId = '1126938837961830'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the membership.
userId [KEY]	String	True		Globally unique ID of the user.
userName	String	True		The user`s name.
WorkspaceId [KEY]	String	True		Globally unique ID of the workspace.
WorkspaceName	String	True		The Workspace name.
IsActive	Boolean	True		Reflects if this user still a member of the workspace.
IsAdmin	Boolean	True		Reflects if this user is an admin of the workspace.
IsGuest	Boolean	True		Reflects if this user is a guest of the workspace.
UserTaskListId	String	True		A user task list represents the tasks assigned to a particular user.
ResourceType	String	True		The base type of this resource.

Workspaces

To Update and Query from Workspaces table. A workspace is the highest-level organizational unit in Asana. All projects and tasks have an associated workspace.

Table Specific Information

Select

The Sync App will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

Id supports the '=' comparison.
UserId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Workspaces WHERE Id = '1126938837961835'

SELECT * FROM Workspaces WHERE UserId = '1126938691750986'

Update

Following is an example of how to update a Workspaces table:

UPDATE Workspaces SET Name='IT-Eng' WHERE Id='1126938837961835'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Globally unique ID of the workspace.
Name	String	False		The name of the workspace.
ResourceType	String	True		The resource type of this resource.
IsOrganization	Boolean	True		Whether the workspace is an organization.
EmailDomains	String	True		Whether the workspace is an organization.
UserId	String	True	Users.Id	Globally unique ID of the user.

Views

Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure is available to update the data if such functionality is applicable to the data source.

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard.

Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.

Asana Connector for CData Sync Views


Name	Description
CustomFields	To view the Custom Fields in the project.
Events	To view the events in projects and tasks.
ProjectFollowers	To view the Project follower details.
ProjectTemplates	TO get ProjectTemplates in the Team or workspace.
TaskStatuses	Get task count of a project.
Users	To view the users details.
UserTaskList	Generated schema file.

CustomFields

To view the Custom Fields in the project.

Table Specific Information

Select

The Sync App uses the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side in the Sync App.

ProjectId supports the '=' comparison.

For example, the following query is processed server side:

SELECT * FROM CustomFields WHERE ProjectId = '1126938691750986'

Columns


Name	Type	References	Description
Id	String		Globally unique identifier of the resource.
Name	String		The name of the custom field.
Description	String		The description of the custom field.
Type	String		The type of the custom field. It must be one of the given values.
ProjectId	String		Globally unique Id of the project.
ProjectName	String		Name of the project.
IsImportant	Boolean		Name of the project.
Format	String		The format of the custom field.
TextValue	String		The value of a text custom field.
NumberValue	Double		The value of a number custom field.
Precision	Integer		Only relevant for custom fields of type 'Number'. This field specifies the number of digits after the decimal to round to.
CurrencyCode	String		The currency code for this custom field. The value is null if the format is not currency.
EnumOptions	String		Array of projects this task is associated with and the section it is in.
CreatedBy	String		Globally unique user identifier.
CreatedByName	String		The user's name.
IsGlobaltoWorkspace	Boolean		A flag that specifies whether this custom field is available to every container in the workspace.
HasNotificationsEnabled	Boolean		A flag that specifies whether a follower of a task with this field should receive inbox notifications about changes in this field.

Events

To view the events in projects and tasks.

Table Specific Information

Select

The Sync App will use the Asana API to process WHERE clause conditions built with the following column and operator. The resource id will be either Project or Task Id. The rest of the filter is executed client side within the Sync App.

ResourceId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Events WHERE ResourceId = '1128092964672854'

Columns


Name	Type	References	Description
ResourceId [KEY]	String		The resource GID for the events were requested for.
ResourceType	String		The resource type for the events were requested for.
ResourceSubType	String		The resource sub type for the events were requested for.
ResourceName	String		The resource name for the events were requested for.
Parent	String		For added/removed events, the parent that resource was added to or removed from. null for other event types.
CreatedAt	Datetime		The timestamp when the event occurred.
UserId	String		The event may be triggered by a different user GID than the subscriber.
UserFullname	String		The event may be triggered by a different user name than the subscriber.
Action	String		The type of action taken that triggered the event.

ProjectFollowers

To view the Project follower details.

Columns


Name	Type	References	Description
ProjectId	String	Projects.Id	Globally unique ID of the Project.
UserId	String		Globally unique ID of the user.
UserName	String		The user`s name.
ResourceType	String		The resource type of this resource.
WorkspaceId	String	Workspaces.Id	The workspace this Project is associated with.

ProjectTemplates

TO get ProjectTemplates in the Team or workspace.

Columns


Name	Type	References	Description
Id [KEY]	String		Globally unique identifier of the resource, as a string.
Color	String		Color of the project template.
Description	String		Free-form textual information associated with the project template.
Html_description	String		The description of the project template with formatting as HTML.
Name	String		Name of the project template.
OwnerId	String		A user object. Globally unique identifier of the resource, as a string.
OwnerName	String		A user object. The base type of this resource.
OwnerResourceType	String		A user object. Read-only except when same user as requester. The name of the User.
IsPublic	Boolean		True if the project template is public to its team.
RequestedDates	String		Array of date variables in this project template. Calendar dates must be provided for these variables when instantiating a project.
ResourceType	String		The base type of this resource.
TeamId	String	Teams.Id	A team is used to group related projects and people together within an organization. Globally unique identifier of the resource, as a string.
TeamName	String		A team is used to group related projects and people together within an organization. The name of the team.
TeamResourceType	String		A team is used to group related projects and people together within an organization. The base type of this resource.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
WorkspaceId	String		The workspace Id to filter results on.

TaskStatuses

Get task count of a project.

Table Specific Information

Select

The Sync App will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

ProjectId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM TaskStatuses WHERE ProjectId = '1136437449978157'

Columns


Name	Type	References	Description
ProjectId	String	Projects.Id	Globally unique ID of the project.
NumberofTasks	Integer		The number of tasks in a project.
CompletedTasks	Integer		The number of completed tasks in a project.
IncompleteTasks	Integer		The number of incomplete tasks in a project.
NumberofMilestones	Integer		The number of milestones in a project.
CompletedMilestones	Integer		The number of completed milestones in a project.
InCompleteMilestones	Integer		The number of incomplete milestones in a project.

Users

To view the users details.

Table Specific Information

Select

The Sync App will use the Asana API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

Id supports the '=' comparison.
WorkspaceId supports the '=' comparison.
TeamId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM Users WHERE Id = '1126938691750986'

SELECT * FROM Users WHERE WorkspaceId = '1126938837961835'

SELECT * FROM Users WHERE TeamId = '1129514033997892'

Columns


Name	Type	References	Description
Id [KEY]	String		Globally unique ID of the user.
Name	String		The user`s name.
Email	String		The user`s email address.
ResourceType	String		The resource type of this resource.
WorkspaceId	String	Workspaces.Id	The User access in workspaces.
TeamId	String	Teams.Id	Globally unique ID of the team.

UserTaskList

Generated schema file.

Table Specific Information

Select

The Sync App will use the Asana API to process WHERE clause conditions built with the following column and operator. The [UserId and workspaceId] or Id is required to make a request and the rest of the filter is executed client side within the Sync App.

Id supports the '=' comparison.
UserId supports the '=' comparison.
workspaceId supports the '=' comparison.

For example, the following queries are processed server side:

SELECT * FROM UserTaskList WHERE Id = '1126938837961837'

SELECT * FROM UserTaskList WHERE UserId = '1126938691750986' AND workspaceId = '1126938837961830'

Columns


Name	Type	References	Description
Id [KEY]	String	WorkspaceMembership.UserTaskListId	Globally unique ID of the user task list.
Name	String		The name of the user task list.
UserId	String	Users.Id	The owner of the user task list.
workspaceId	String	Workspaces.Id	The workspace in which the user task list is located.
ResourceType	String		The resource type of this resource.

Connection String Options

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Establishing a Connection.

Connection

Property	Description
ProjectId	The globally unique identifier (gid) associated with your Asana Project.
WorkspaceId	The globally unique identifier (gid) associated with your Asana Workspace.
IncludeCustomFields	Custom fields may be added to the Tasks, SubTasks, and PortfolioItems tables.

OAuth

Property	Description
OAuthClientId	The client Id assigned when you register your application with an OAuth authorization server.
OAuthClientSecret	The client secret assigned when you register your application with an OAuth authorization server.

SSL

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

Firewall

Property	Description
FirewallType	The protocol used by a proxy-based firewall.
FirewallServer	The name or IP address of a proxy-based firewall.
FirewallPort	The TCP port for a proxy-based firewall.
FirewallUser	The user name to use to authenticate with a proxy-based firewall.
FirewallPassword	A password used to authenticate to a proxy-based firewall.

Proxy

Property	Description
ProxyAutoDetect	This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
ProxyServer	The hostname or IP address of a proxy to route HTTP traffic through.
ProxyPort	The TCP port the ProxyServer proxy is running on.
ProxyAuthScheme	The authentication type to use to authenticate to the ProxyServer proxy.
ProxyUser	A user name to be used to authenticate to the ProxyServer proxy.
ProxyPassword	A password to be used to authenticate to the ProxyServer proxy.
ProxySSLType	The SSL type to use when connecting to the ProxyServer proxy.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Logging

Property	Description
LogModules	Core modules to be included in the log file.

Schema

Property	Description
Location	A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Tables	This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Views	Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Miscellaneous

Property	Description
MaxRows	Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
Other	These hidden properties are used only in specific use cases.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews	A filepath pointing to the JSON configuration file containing your custom views.

Connection

This section provides a complete list of the Connection properties you can configure in the connection string for this provider.

Property	Description
ProjectId	The globally unique identifier (gid) associated with your Asana Project.
WorkspaceId	The globally unique identifier (gid) associated with your Asana Workspace.
IncludeCustomFields	Custom fields may be added to the Tasks, SubTasks, and PortfolioItems tables.

ProjectId

The globally unique identifier (gid) associated with your Asana Project.

Remarks

Requests will return the data mapped under this project.

WorkspaceId

The globally unique identifier (gid) associated with your Asana Workspace.

Remarks

Requests will return the projects mapped under this WorkspaceId.

IncludeCustomFields

Custom fields may be added to the Tasks, SubTasks, and PortfolioItems tables.

Remarks

If set to TRUE, custom fields may be added to the Tasks, SubTasks, and PortfolioItems tables. The WorkspaceId should also be specified. Custom fields are related to a specific Workspace. If no WorkspaceId is specified, the first available WorkspaceId will be used.

OAuth

This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.

Property	Description
OAuthClientId	The client Id assigned when you register your application with an OAuth authorization server.
OAuthClientSecret	The client secret assigned when you register your application with an OAuth authorization server.

OAuthClientId

The client Id assigned when you register your application with an OAuth authorization server.

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.

OAuthClientSecret

The client secret assigned when you register your application with an OAuth authorization server.

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.

SSL

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

SSLServerCert

The certificate to be accepted from the server when connecting using TLS/SSL.

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:


Description	Example
A full PEM Certificate (example shortened for brevity)	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate	C:\cert.cer
The public key (example shortened for brevity)	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated)	ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated)	34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Firewall

This section provides a complete list of the Firewall properties you can configure in the connection string for this provider.

Property	Description
FirewallType	The protocol used by a proxy-based firewall.
FirewallServer	The name or IP address of a proxy-based firewall.
FirewallPort	The TCP port for a proxy-based firewall.
FirewallUser	The user name to use to authenticate with a proxy-based firewall.
FirewallPassword	A password used to authenticate to a proxy-based firewall.

FirewallType

The protocol used by a proxy-based firewall.

Remarks

This property specifies the protocol that the Sync App will use to tunnel traffic through the FirewallServer proxy. Note that by default, the Sync App connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.


Type	Default Port	Description
TUNNEL	80	When this is set, the Sync App opens a connection to Asana and traffic flows back and forth through the proxy.
SOCKS4	1080	When this is set, the Sync App sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted.
SOCKS5	1080	When this is set, the Sync App sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes.

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

FirewallServer

The name or IP address of a proxy-based firewall.

Remarks

This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.

Note that the Sync App uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

FirewallPort

The TCP port for a proxy-based firewall.

Remarks

This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.

FirewallUser

The user name to use to authenticate with a proxy-based firewall.

Remarks

The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.

FirewallPassword

A password used to authenticate to a proxy-based firewall.

Remarks

This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.

Proxy

This section provides a complete list of the Proxy properties you can configure in the connection string for this provider.

Property	Description
ProxyAutoDetect	This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
ProxyServer	The hostname or IP address of a proxy to route HTTP traffic through.
ProxyPort	The TCP port the ProxyServer proxy is running on.
ProxyAuthScheme	The authentication type to use to authenticate to the ProxyServer proxy.
ProxyUser	A user name to be used to authenticate to the ProxyServer proxy.
ProxyPassword	A password to be used to authenticate to the ProxyServer proxy.
ProxySSLType	The SSL type to use when connecting to the ProxyServer proxy.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

ProxyAutoDetect

This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

Remarks

This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.

ProxyServer

The hostname or IP address of a proxy to route HTTP traffic through.

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The Sync App can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the Sync App uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

ProxyPort

The TCP port the ProxyServer proxy is running on.

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.

ProxyAuthScheme

The authentication type to use to authenticate to the ProxyServer proxy.

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the Sync App will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

BASIC: The Sync App performs HTTP BASIC authentication.
DIGEST: The Sync App performs HTTP DIGEST authentication.
NEGOTIATE: The Sync App retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
PROPRIETARY: The Sync App does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

ProxyUser

A user name to be used to authenticate to the ProxyServer proxy.

Remarks

The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:

user@domain
domain\user

ProxyPassword

A password to be used to authenticate to the ProxyServer proxy.

Remarks

This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.

If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.

If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the Sync App uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

ProxySSLType

The SSL type to use when connecting to the ProxyServer proxy.

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:


AUTO	Default setting. If the URL is an HTTPS URL, the Sync App will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option.
ALWAYS	The connection is always SSL enabled.
NEVER	The connection is not SSL enabled.
TUNNEL	The connection is through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy.

ProxyExceptions

A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Remarks

The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.

Note that the Sync App uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

Logging

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

Property	Description
LogModules	Core modules to be included in the log file.

LogModules

Core modules to be included in the log file.

Remarks

Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.

See the Logging page for an overview.

Schema

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

Property	Description
Location	A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Tables	This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Views	Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Location

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Remarks

The path to a directory which contains the schema files for the Sync App (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is "%APPDATA%\\CData\\Asana Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:

BrowsableSchemas

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Tables

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the Sync App.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Views

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the Sync App.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Miscellaneous

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

Property	Description
MaxRows	Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
Other	These hidden properties are used only in specific use cases.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews	A filepath pointing to the JSON configuration file containing your custom views.

MaxRows

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Remarks

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Other

These hidden properties are used only in specific use cases.

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting


DefaultColumnSize	Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMT	Determines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename	Records the underlying socket data transfer to the specified file.

PseudoColumns

This property indicates whether or not to include pseudo columns as columns to the table.

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

Timeout

The value in seconds until the timeout error is thrown, canceling the operation.

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the Sync App throws an exception.

UserDefinedViews

A filepath pointing to the JSON configuration file containing your custom views.

Remarks

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The Sync App automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the Sync App.

This User Defined View configuration file is formatted as follows:

Each root element defines the name of a view.
Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
	"MyView": {
		"query": "SELECT * FROM projects WHERE MyColumn = 'value'"
	},
	"MyView2": {
		"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
	}
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\\Users\\yourusername\\Desktop\\tmp\\UserDefinedViews.json"