Bulk load filter - MSSQL

Parameters

None of the parameters are required.  If no parameter is specified, this filter will be invoked for every row of every bulk load.

The parameters that can take multiple values can be separated by commas or by newlines.

SQL patterns

The values(s) or regular expression(s) for the SQL command for the bulk load, which usually looks like: 

insert bulk dbo.DestinationTable ([id] Int, [col1] NVarChar(100) COLLATE SQL_Latin1_General_CP1_CI_AS, etc...

You can figure out the text of bulk load commands by using a request logger, which will log the text of all SQL command.

Bulk load commands are different than normal SQL commands, and are normally issued only by Microsoft's SqlBulkCopy class.

Column patterns

Zero or more name=regular expression entries specifying for which values of which columns this filter should get invoked. In addition to all the usual regular expression syntax, if a regular expression contains equal signs, they must be escaped with a backslash (e.g. \=).

For example, when inserting a set of rows, with the column pattern:

country=(GR)|(MX)

the filter would only get invoked for rows having a value of 'GR' or 'MX' for the country column.

And/or

If more than one column pattern is specified, this determines whether they must all be true (and) or any one of them (or).

Client IPs

A list of IP addresses (IP4 and/or IP6) and/or regular expressions for IP addresses. If specified, only requests from matching IP addresses will cause execution of this filter.

Example: 

12.34.56.78
1234:5678:90ab::01
regex:98\.76\..*
regex:9876:5432:.*

Users

A list of user names and/or regular expressions for user names. If specified, only calls from these users will cause execution of the filter.

Example

We can change the values inserted in a bulk load with:

if (context.packet.country === 'GR') {

    context.packet.remove(); // Remove this row -- do not insert it

}

else {

    context.packet.description += " not in Greece";

}