Chapter 5. Datasinks

Datasinks are the counterpart of datasources in ReportServer. Reports can be sent to any datasink defined, e.g. to a FTP or SFTP datasink. Reports may be sent directly to a datasink (via the SendTo button) or scheduled to be sent to a specific datasink. Multiple datasinks of the same type are allowed, analogously as datasources. Further, as with most other objects in ReportServer, datasinks are maintained in a hierarchical structure. The datasink management module can be found in the Administration module under Datasinks. The following object types can be created in the tree:

• Folder Serve to structure datasinks.
• Datasink Here various datasinks are optionally available which we will discuss in more detail in the following.

Datasinks will be configured in two steps. In datasink management, datasinks will be created and the basic settings made. For FTP datasinks, here, for instance, user name, password, default folder and FTP URL are stored. However, the specific configuration per use will be set at the point where the datasink will be used (this is mostly with the respective report). Here, for instance, for a FTP datasink, the specific folder can be set where the report should be stored. The default folder can be used or a new folder can be defined for the specific use.

Datasinks may either completely disabled, or disabled for a specific type, or scheduling may be disabled for this type. Disabling datasinks may be done in /fileserver/etc/datasinks/datasinks.cf. Details on this can be found in the Configuration Guide.

ReportServer supports the following datasinks.

• Email - SMTP
• Table datasinks (available in Enterprise Edition)
• SFTP
• FTPS
• FTP
• Samba - SMB/CIFS (available in Enterprise Edition)
• Amazon S3 (available in Enterprise Edition)
• SCP (available in Enterprise Edition)
• Local filesystem (available in Enterprise Edition)
• Printer (available in Enterprise Edition)
• OAuth2-authenticated datasinks:
  • Dropbox (available in Enterprise Edition)
  • OneDrive - SharePoint (O365) (available in Enterprise Edition)
  • Google Drive (available in Enterprise Edition)
  • Box (available in Enterprise Edition)

Email SMTP datasinks allow to send and/or schedule reports to a given SMTP server. Email SMTP datasink configuration can be made in the Administration area. Its permissions can be administrated analogous as other objects in ReportServer.

In order for ReportServer to be able to send mails you must specify the mail server settings. Make the following configurations in your Email SMTP datasink.

Setting up the SMTP server. Replace the values host, port, username, and password according to your SMTP server.

Host: mail.yourmailserver.com
Port: 25
Username: rs@yourmailserver.com
Password: passwordsecret
SSL: false
TLS enable: false
TLS require: false

If you are using SSL or TLS please also specify these values. Next, configure the sender name, email address and forceSender options. If the forceSender option is set to true, the emails will be sent using the given (generic) sender details. If set to false, the specific user sending the email will determine the sender details.

Sender: rs@yourmailserver.com
Sender name: ReportServer
Force sender: false
Encryption policy: allow_mixed

The encryption policy option controls whether or not mails have to be encrypted or whether it is ok to send mails unencrypted if a user's public key is not specified. Choose between strict and allow_mixed. Note that if you choose strict then mails to users that do not have public key registered with ReportServer will not receive any messages.

Note that you can specify a default datasink per datasink type in the /etc/datasinks/datasinks.cf configuration file. Details can be found in the Configuration Guide.

Table datasinks allow you to send and/or schedule dynamic list reports into to a given table in any datasource supported by ReportServer. This allows you to easily transfer data from MySQL to Oracle, for example. The destination table must exist and must be compatible with the dynamic list, i.e. the fields must exist, have the same name, and have compatible data types.

Table datasink configuration can be made in the Administration area. Its permissions can be administrated analogous as other objects in ReportServer.

In order to configure a table datasink, the following must be set:

• Datasource The datasource to send the data to.
• Destination table name The table name. This table must exist in the given datasource, be reachable, and compatible to the dynamic list transfering the data.
• ;-separated list of primary keys in destination table The destination table may of course contain one or more primary keys. These can be entered here, separating them by '';''. For example, if your destination table has one primary key ''ENTITY_ID'', you can enter ''ENTITY_ID''. If your destination table has two primary keys ''ENTITY_ID'' and ''ENTITY_ID2'', you can enter ''ENTITY_ID;ENTITY_ID2''.
• Copy primary keys If your destination primary keys are of autoincrement-type, their values should not be copied to the destination table, as your DB calculates the ids automatically. In this case, you should not set this checkbox. However, if you want to copy the primary key values, you should set this checkbox.
• Batch size The table is copied in batches for performance reasons. You may adapt the batch size here.

When you schedule a dynamic list to a table datasink, you should select ''Stream table'' as an export type.

Note that you can specify a default datasink per datasink type in the /etc/datasinks/datasinks.cf configuration file. Details can be found in the Configuration Guide.

SFTP datasinks allow to send and/or schedule reports to a SFTP server. SFTP datasink configuration can be made in the Administration area. Permissions to SFTP datasinks can be given analogously as to other objects in ReportServer.

The host can be entered using the following format:

sftp://sftpHost

Currently, ReportServer supports the following authentication methods:

• Username Password Authentication
• Public Key Authentication

All passwords and private keys are encrypted and saved into the ReportServer database. Note that the private key passphrase may be empty, but it is highly recommended to use a passphrase.

Further, note that for SFTP to work, you have to add your SFTP host to your /etc/ssh/known_hosts file (https://en.wikibooks.org/wiki/OpenSSH/Client_Configuration_Files#~/.ssh/known_hosts) in order to verify the identity of the remote host, thus protecting against impersonation or man-in-the-middle attacks. Its location can be configured in the /fileserver/etc/security/misc.cf file as described in the Configuration Guide. For manually adding a public key to the /etc/ssh/known_hosts file, check here: https://en.wikibooks.org/wiki/OpenSSH/Client_Configuration_Files#Manually_Adding_Public_Keys_to_~/.ssh/known_hosts.

Note that if the (extension) folder/folders does/do not exist, it/they is/are created. If the report already exists in the same path, it is overwritten.

FTPS datasinks allow to send and/or schedule reports to a FTPS server. FTPS datasink configuration can be made in the Administration area. Permissions to FTPS datasinks can be given analogously as to other objects in ReportServer.

The host can be entered using the following format:

ftps://sftpHost

Currently, ReportServer supports the following authentication method:

• Username Password Authentication

All passwords are encrypted and saved into the ReportServer database.

Note that if the (extension) folder/folders does/do not exist, it/they is/are created. If the report already exists in the same path, it is overwritten.

FTP datasinks allow to send and/or schedule reports to a FTP server. FTP datasink configuration can be made in the Administration area. Active and passive FTP modes are supported. Permissions to FTP datasinks can be given analogously as to other objects in ReportServer.

The host can be entered using the following format:

ftp://ftpHost

Note that if the (extension) folder/folders does/do not exist, it/they is/are created. If the report already exists in the same path, it is overwritten.

Samba - SMB/CIFS datasinks allow to send and/or schedule reports to a given Samba server using SMB/CIFS protocols. Samba - SMB/CIFS datasink configuration can be made in the Administration area. Permissions to local Samba - SMB/CIFS datasinks can be given analogously as to other objects in ReportServer.

The host can be entered using the following format:

smb://sambaHost

Note that the path must exist and must be writable by your Samba's user. If the report already exists in the same path, it is overwritten.

Amazon S3 datasinks allow to send and/or schedule reports to a given S3 bucket. The datasink configuration can be made in the Administration area. Permissions to local S3 datasinks can be given analogously as to other objects in ReportServer.

You can create an access and a secret key as described here: https://docs.aws.amazon.com/general/latest/gr/aws-sec-cred-types.html#access-keys-and-secret-access-keys. Further, the unique bucket name and region must be entered into the datasink definition.

If the report already exists in the same path, it is overwritten.

More information can be found here: https://docs.aws.amazon.com/en_en/AmazonS3/latest/userguide/Welcome.html.

SCP datasinks allow to send and/or schedule reports to a given SCP (SSH) server. SCP datasink configuration can be made in the Administration area. Permissions to local SCP datasinks can be given analogously as to other objects in ReportServer.

The host can be entered using the following format:

scp://scpHost

Note that the path must exist and must be writable by your SCP user. An absolute destination folder is required, so relative paths like "./" do not work here. If the report already exists in the same path, it is overwritten.

Currently, ReportServer supports the following authentication methods:

• Username Password Authentication
• Public Key Authentication

All passwords and private keys are encrypted and saved into the ReportServer database. Note that the private key passphrase may be empty, but it is highly recommended to use a passphrase.

Further, note that for SCP datasinks to work, you have to add your SCP server host to your /etc/ssh/known_hosts file (https://en.wikibooks.org/wiki/OpenSSH/Client_Configuration_Files#~/.ssh/known_hosts) in order to verify the identity of the remote host, thus protecting against impersonation or man-in-the-middle attacks. Its location can be configured in the /fileserver/etc/security/misc.cf file as described in the Configuration Guide. For manually adding a public key to the /etc/ssh/known_hosts file, check here: https://en.wikibooks.org/wiki/OpenSSH/Client_Configuration_Files#Manually_Adding_Public_Keys_to_~/.ssh/known_hosts.

Local filesystem datasinks allow to send and/or schedule reports to a given directory of your local filesystem, i.e. where your ReportServer is installed. Local filesystem datasink configuration can be made in the Administration area. Permissions to local filesystem datasinks can be given analogously as to other objects in ReportServer.

You can enter a path to a given folder in your filesystem as follows:

/users/myuser/myreports

in Linux/Mac, and

C:/myuser/myreports

in Windows.

The path in the datasink definition determines the base path of the datasink. It also contains a default folder/default folders, which can be overriden in specific uses of this datasink, analogously to other datasink types.

Note that the base path must exist and must be writable by your Tomcat's user. If the (extension) folder/folders does/do not exist, it/they are created. If the report already exists in the same path, it is overwritten.

Printer Datasinks allow to send and/or schedule reports to a given printer. Printer datasink configuration can be made in the Administration area. Permissions to printer datasinks can be given analogously as to other objects in ReportServer.

Note that, while scheduling, you have to select the ''PDF'' export type in order to use printer datasinks.

OAuth2-authenticated datasinks use, as their name suggest, OAuth2 https://oauth.net/2/, the industry-standard protocol for authorization.

Before being able to use oauth2-datasinks, you have to create an app in the service needed and authorize file uploads in this app. You can then add your app key and app secret into the respective ReportServer fields and save the datasink.

When this is achieved, you can click the ''Datasink OAuth2 Authentication Setup'', which redirects you to the service authorization. Please note that you have to add the ''redirect URI'' into your app. This redirect URI is shown when clicking the before-mentioned button. The URI should be similar as this: http://localhost:8080/ReportServer/reportserver/oauth. When this is done, after app authorization and automatic redirection, you should be able to use your oauth2-datasinks. For testing your datasink, as with other ReportServer datasinks, you can use the ''Test datasink'' button.

Dropbox OAuth2-authorized datasinks allow you to send and/or schedule reports to a given directory in your Dropbox https://www.dropbox.com/ account. Note that Dropbox for Business is also supported: https://www.dropbox.com/business/.

For getting your ''app key'' and ''secret key'', you have to create an app here: https://www.dropbox.com/developers/apps/ and give it the appropriate permissions. Specifically, files.content.write and files.metadata.write is needed. More information on this can be found here: https://developers.dropbox.com/de-de/oauth-guide. Note that when permissions are changed, it is necessary to create a new ''refresh token'' with the ''Datasink OAuth Authentication Setup'' button.

The path in the datasink definition determines the base path of the datasink. It also contains a default folder/default folders, which can be overriden in specific uses of this datasink, analogously to other datasink types.

Note that the base path must exist. If the (extension) folder/folders does/do not exist, it/they is/are created. If the report already exists in the same path, it is overwritten. The path should be entered like this: /my/path. Note that, different as in other datasinks, ./my/path does not work.

OneDrive - SharePoint (O365) OAuth2-authorized datasinks allow you to send and/or schedule reports to a given directory in your OneDrive - SharePoint (O365) http://onedrive.com/ account. Note that OneDrive for Business is also supported (Office 365), as well as SharePoint O365. Older SharePoint versions are currently not supported.

For getting your ''app key'' and ''secret key'', you have to create an app registration here: https://portal.azure.com/ (Azure Active Directory - App registrations section) and give it the appropriate API permissions (API permissions section). Specifically, files.readwrite.all is needed. The secret key can be created in the ''Certificates and secrets'' section. More information on this can be found here: https://docs.microsoft.com/en-us/graph/auth-register-app-v2. Note that when permissions are changed, it is necessary to create a new ''refresh token'' with the ''Datasink OAuth Authentication Setup'' button.

The ''base root'' path in the datasink definition, together with the ''folder'' path, determines the base path of the datasink. The default folder/folders are also included, which can be overriden in specific uses of this datasink, analogously to other datasink types.

If needed, you can configure your tenant id, available in your Microsoft account. You can use the default if this is not needed or if you don't have a custom tenant id.

Note that the base path must exist. The default ''base root'' points to your User's OneDrive. You can change this to some other drive, e.g.:

/sites/mySharePoint.sharepoint.com,d489891a-364e-4c89-8156-ee045257235c,9e23eb81-e053-444e-8716-2269fc0763d0e/lists/33459768-096a-4107-834a-d5c2a5ce0299/drive/root:

You may also make use of the terminal command onedrive to configure the ''baseroot'': First use onedrive group getmygroups for an overview of all groups you have access to. Afterwards use the group-id to list all available drives with onedrive group-id getdrivesof. Then you may configure your ''baseroot'' as such

/drives/your-drive-id/root:

Refer to Section 16.39. onedrive for more information on the onedrive terminal commands.

More information on this can be found here: https://docs.microsoft.com/en-us/graph/api/resources/onedrive?view=graph-rest-1.0. If the (extension) folder/folders does/do not exist, it/they is/are created. If the report already exists in the same path, it is overwritten. The path should be entered like this: /my/path. Note that, different as in other datasinks, ./my/path does not work.

Google Drive OAuth2-authorized datasinks allow you to send and/or schedule reports to a given directory in your Google Drive https://drive.google.com/ account.

For getting your ''app key'' and ''secret key'', you have to create an app registration here: https://console.developers.google.com/ (Google API Console) and give it the appropriate API permissions.

The ''base root'' path in the datasink definition, together with the ''folder'' path, determines the base path of the datasink. The default folder/folders are also included, which can be overriden in specific uses of this datasink, analogously to other datasink types.

Note that the base path must exist. If the (extension) folder/folders does/do not exist, it/they is/are created. If the report already exists in the same path, it is overwritten. The path should be entered like this: /my/path. Note that, different as in other datasinks, ./my/path does not work.

More information on this can be found here: https://developers.google.com/drive/api/v3/about-sdk.

Box OAuth2-authorized datasinks allow you to send and/or schedule reports to a given directory in your Box Drive https://box.com/ account.

For getting your ''app key'' and ''secret key'', you have to create an app registration here: https://app.box.com/developers/console (Box Developer Console) and give it the appropriate API permissions.

The ''base root'' path in the datasink definition, together with the ''folder'' path, determines the base path of the datasink. The default folder/folders are also included, which can be overriden in specific uses of this datasink, analogously to other datasink types.

Note that the base path must exist. If the (extension) folder/folders does/do not exist, it/they is/are created. If the report already exists in the same path, it is overwritten. The path should be entered like this: /my/path. Note that, different as in other datasinks, ./my/path does not work.

More information on this can be found here: https://developer.box.com/reference/.