Items in API Script

Feeds are composed of items, but in API Script items themselves are used for much more than the individual parts of a feed. They are also used to represent inputs to operations.

Declare Items

In API Script items are created, named, and given attribute values through the api:set keyword:

<api:set item="input" attr="mask" value="*.txt" />

The line above sets the "mask" attribute to the value "*.txt" on the item named "input". In this case, the input item is like a variable in API Script.

However, an item named "input" is never declared. Instead, the item is created the first time you try to set an attribute on it. In the example above, if the input item did not already exist, it would be created and the mask attribute would be set on it.

Select Attribute Values

To reference an attribute, use the syntax item.attribute (e.g., "input.mask"). To query an attribute value, surround the attribute name in square brackets ([]). This instructs the interpreter that you want to evaluate the string instead of interpreting it as a string literal.

For example, consider the following code snippet:

<api:set item="item1" attr="attr1" value="value1"/>
<api:set item="item1" attr="attr2" value="item1.attr1"/>
<api:set item="item1" attr="attr3" value="[item1.attr1]"/>

The results are the following:

item1.attr1 gets assigned the literal string value "value1".
item1.attr2 gets assigned the literal string value "item1.attr1".
item1.attr3 gets assigned the value "value1", because the string "[item1.attr1]" gets evaluated at run time as the attr1 attribute of item1.

Note that you can use a backslash to escape square brackets in string literals.

Default Item

In API Script there is always an implicit, unnamed item on an internal stack of items, the default item. In the following two cases, the default item is commonly used to make scripts shorter and easier to write:

Calling operations: The default item is the item passed by default to the operation called by your script if no other item is specified. This means that you can use api:set to write attributes to the default unnamed item and those attributes will be passed as input to the next operation called in the script. This is one way to provide an operation's required parameters.
Processing the current item in the output of an operation or script: If you do not specify a variable name for the result of an operation, then inside the api:call block that invokes the operation, the default item will refer to the current item produced by the operation.

Manipulate the default item by simply omitting the item attribute of an API Script keyword:

<api:set attr="path" value="." />

Named Items

In addition to items declared within the script, several built-in items are available in the scope of a script. Built-in, or special, items are available in API Script that provide an interface for accessing the connection string and the SQL query. These special items are useful for mapping inputs to data processing operations.

The following sections detail the special items.

Script Inputs (_input)

The input for a script can be read from the _input item. The SQL statement provides the input for table and stored procedure scehmas: In a SELECT statement, the _input item contains the columns or pseudo columns specified in the WHERE clause.

When you read values from the default item in a script, you are reading values from _input; likewise, attributes that you write to the default item are passed as parameters to operations along with the input to the script. Only the variables defined in the info block or in the script will be available in the _input item.

Note that inside an api:call block _input is no longer the default item and you must reference it by name if you need access to it.

Script Outputs (_out[n])

The current item in the feed produced by the api:call keyword can be accessed through the default item or a named special item, "_outX", where X is the level of nesting of api:call keywords. For example, if you are inside a single api:call keyword, the item's name will be "_out1". If you are inside three levels of nested api:call keywords, then it will be _out3.

Connection String Properties (_connection)

The _connection item has the connection properties of the component. The component does not perform any validation of the connection string properties. It is left to the schema author to decide which properties are required, how they are used, etc.

In addition to providing access to the connection properties, the _connection item can be used to store pieces of data in a connection. For example, it may be necessary to store session tokens that can be reused while a connection lasts. You can use the api:set keyword to store any values, as shown in the code example below:

<api:set attr="_connection._token" value="[oauth.connection_token]"/>

Note: You can set only the attributes that start with the _ symbol. This is done so that the connection properties set by the user cannot be overriden.

SQL Clauses (_query)

The _query item has the following attributes that describe the query that was issued to the component:


query	The SQL statement. For example: SELECT Id, Name FROM Accounts WHERE City LIKE '%New%' AND COUNTRY = 'US' GROUP BY CreatedDate ORDER BY Name LIMIT 10,50;
selectcolumns	A comma-separated list of the columns in the SELECT clause. For example, the Id and Name columns in the example. If "" is specified in the SELECT clause, the value of [_query.selectcolumns] is "".
table	The table name. For example, Accounts in the example.
isjoin	Whether the query is a join.
jointable	The table in the JOIN clause.
criteria	The WHERE clause. For example, the following WHERE clause in the example: City LIKE '%New%' AND COUNTRY = 'US'
orderby	The ORDER BY clause. For example, Name in the example.
groupby	The GROUP BY clause. For example, CreatedDate in the example.
limit	The limit specified in the LIMIT or TOP clauses of the SELECT statement. For example, 50 in the example.
offset	The offset specified in the LIMIT or TOP clauses of the SELECT statement. For example, 10 in the example.
insertselect	The SELECT statement nested in an INSERT statement.
updateselect	The SELECT statement nested in an UPDATE statement.
upsertselect	The SELECT statement nested in an UPSERT statement.
deleteselect	The SELECT statement nested in a DELETE statement.
bulkoperationcolumns	The columns of the table the bulk operation modifies, separated by commas. For example, consider the following query: INSERT INTO Account(account_name, account_type) SELECT customer_name, customer_type FROM Customer#TEMP [_query.bulkoperationcolumns] returns the following: [account_name], [account_type]
temptablecolumns	The columns selected from the temp table in a bulk operation, separated by commas. For example, consider the following query: DELETE FROM Account WHERE EXISTS SELECT customer_name, customer_type FROM Customer#TEMP [_query.temptablecolumns] returns the following: [customer_name], [customer_type]
nullupdates	The columns of an UPDATE statement, separated by commas, that contain a null value or a null parameter value.
isschemaonly	Whether the query retrieves only schema information.

SSIS Components for JSON