Activities
Connect to MSSQL
This activity connects to a Microsoft SQL Host. The Output of this activity should be used as input for all Connection parameters used for activities placed under Microsoft SQL category.
NOTE: If during the execution of the workflow, the connection expires, the activity will attempt three times to automatically reconnect to Microsoft SQL host using the input credentials.
Activity Parameters
The Connect to MSSQL activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Authentication (combo box control) (type: string): the authentication method. For SQL Authentication, the Username and Password parameters are mandatory. For Windows authentication they are optional.
- Database (text box editor type) (type: string): the database name to connect to.
- Impersonation Full Username (text box editor type) (type: string): the domain and username required if impersonation is used for the Windows authentication. If empty, default credentials are used. E.g. 'fmrp\demouser'
- Impersonation Password (password editor type) (type: string): the username password if impersonation is used for Windows Authentication. If left empty default credentials are used.
- Impersonation Password Binding (text box editor type) (type: string): bind to a variable containing the encrypted password for the sql server. (This parameter is used only when the user wants to send an encripted password from outside the workflow, for example Flowster Portal). In case of Windows Authentication, if the user that connects to the database is the logged in one, the field can remain empty.
- Instance (text box editor type) (type: string): the instance name to connect to. If there is an SQL server with no instance, this field should be empty. Examples:
- localhost\sqlexpress - server with instance
- localhost - server with no instance
- Password (password editor type) (type: string): the password for the username provided in Username parameter for SQL Authentication.
- Password Binding (text box editor type) (type: string): Bind to a variable containing the encrypted password for the username provided in Username parameter for SQL Authentication. (This parameter is used only when the user wants to send an encrypted password from outside the workflow, for example Flowster Portal). In case of Windows Authentication, if the user that connects to the database is the logged in one, the field can remain empty.
- Port (text box editor type) (type: int32): the port number for the SQL Server.
- Server Name (text box editor type) (type: string): the name or IP of the SQL Server to connect to. If it is a local SQL server, localhost is also accepted as a value.
- Username (text box editor type) (type: string): the SQL username for SQL Authentication. E.g 'demouser' which represents the Login name of type SQL Server authentication.
The Read Only Output variable are the possible output values that the activity will provide:
- Connection (type: object): outputs a SQL connection. The Output of this activity should be used as input for all Connection parameters used for activities placed under Microsoft SQL category.
Disconnect from MSSQL
This activity disconnects from a Microsoft SQL Host.
Activity Parameters
The Disconnect from MSSQL activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Connection (text box editor type) (type: object): the output from a Connect to MSSQL activity.
Add User Access to Database
This activity adds privileges for an user to a database.
Activity Parameters
The Add User Access to Database activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- CSV Roles (text box editor type) (type: collection<string>): the database-level roles for the user access. Overrides the 'Database-level role' parameter. The values must be entered with the format: "db_owner,db_securityadmin,db_backupoperator,db_accessadmin".
- Database - level Role (value selector list) (type: string): the roles that will be added to the user. The roles will be selected from a list, after clicking the ... button. The list will display all possible roles that can be assigned to a SQL user.
- Username for Access (text box editor type) (type: string): the name of the user that will receive permissions. The user must be written by following the "domain\username" format.
Execution: the activity will check if the user exists as a login on the SQL server and if found, will grant access on the connected database, for the given roles:
Backup Database
This activity creates a backup .bak file of a selected database.
Activity Parameters
The Backup Database activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Backup Path (text box editor type) (type: string): the full path where the backup file will be created.
- Check Folder Exists (text box editor type) (type: combobox): select True if the folder where the backup will be created should be checked for existence, otherwise select False. By default the True value will be selected. The False option can be selected for situations where the user doesn't have rights for checking the folder, but it can create the backup.
- Media Name (text box editor type) (type: string): the new media a media name. For better results, use the same name as the database's. As an example, the Restore activity will work properly on a 2014 SQL Server when using the same name as the database's for the media set.
- Name (text box editor type) (type: string): the name of the backup set. Names can have a maximum of 128 characters. (E.g. 'Full Backup of Database'). In the example the name of the backup is the same as the database's.
- Timeout (text box editor) (type: Int32): the time in seconds to wait for the backup operation to execute. The default value in 30 seconds.
Execution: the activity will create a backup file of the database settled in the connection activity, in the specified folder:
The backup file can be seen in the given location:
Delete Row(s) from Table
This activity deletes Row(s) from a SQL Server database table.
Activity Parameters
The Delete Row(s) from Table activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Table (text box editor type) (type: string): the table name where the row(s) will be deleted from.
- Where Statement (text box editor type) (type: string): the Where statement to filter the row(s) that will be deleted. For example: ID>2, where all rows with an ID higher than 2 will be deleted.
Execution: the activity will delete all the rows from a table according to the given Where statement:
- the initial table content:
- the table content after deletion:
Insert Row into Table
This activity inserts Row on a SQL Server database table.
Activity Parameters
The Insert Row into Table activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Fields (text box editor type) (type: string): the column headers that you want to be returned with the select command. If you enter more than one you should separate them with ','. For example: Name,Surname,Company.
- Table (text box editor type) (type: string): the table name where the row(s) will be inserted.
- Values (text box editor type) (type: string): the values for specified fields in Fields parameter. If you enter more than one you should separate them with ','. If you have more values each should be placed in the same order as you add the fields.For example: May,James,Amazon Prime.
The Read Only Output variable are the possible output values that the activity will provide:
- Output Last ID (type: string): outputs the last row inserted ID.
Execution: the activity will insert the new row into the selected table and will output the ID of the added row:
In the database, the row can be seen at the last position:
Remove User Access from Database
This activity removes privileges for an user to a database.
Activity Parameters
The Remove User Access from Database activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- CSV Roles (text box editor type) (type: collection<string>): the database-level roles for the user access revoking. Overrides the 'Database-level role' parameter. The values must be entered with the format: "db_owner,db_securityadmin,db_backupoperator,db_accessadmin".
- Database - level Role (value selector list) (type: string): the roles that will be revoked for the user. The roles will be selected from a list, after clicking the ... button. The list will display all possible roles that can be revoked for an SQL user.
- Username for Access (text box editor type) (type: string): the name of the user that will have the permissions revoked. The user must be written by following the "domain\username" format.
Execution: the activity will check if the user exists as a login on the SQL server and if found and if it already have assigned rights to the database where the activity is connected to. If the user is found and has rights over the database, the activity will revoke access on the connected database, for the given roles:
Restore Database
This activity restores a Database from a backup .bak file.
Activity Parameters
The Restore Database activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Backup File Path (text box editor type) (type: string): the path of the backup file or select it with the File Browser. The .bak file path can be manually written or selected by clicking the
button. In this example the backup file has the same name as the database's. - Check File Exists (text box editor type) (type: combobox): select True if the backup file should be checked for existence, otherwise select False. By default the True value will be selected. The False option can be selected for situations where the user doesn't have rights for checking the folder, but it can create the restore.
- Timeout (text box editor) (type: Int32): the time in seconds to wait for the backup operation to execute. The default value in 30 seconds.
Run query
This activity runs a query on a SQL Server database.
Activity Parameters
The Run Query activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Query (text box editor type) (type: string): the SQL query string. For example: "SELECT Name, Company FROM [FlowsterMSSQLActivities].[dbo].[Names] where ID>1 and ID<4 order by name ASC"
NOTE: if query returns a column of Date type, the format of the date returned as a string will be taken from the execution server.
- Script (text box editor type) (type: string): the query content if a binding to a variable is used ($using). If set and if the $using:variable syntax is used, this parameter will take precedence over the Query parameter. For example, the $using:DemoString syntax will take the value stored in the DemoString variable and will execute it:
The Read Only Output variable are the possible output values that the activity will provide:
- Output (type: collection<collection<string>>): outputs a collection of returned rows.To use the elements of the collection, use ForEachFactory activity.
- Output Columns (type: int32): outputs the number of columns returned.
- Output Rows (type: int32): outputs the number of rows returned.
NOTE: if user-defined messages are desired to be returned and displayed by the Run Query activity, the command(s) can be written by following the example below:
DECLARE @firstMessage char(20);
DECLARE @secondMessage char(20);
SET @firstMessage='The selected category exists in the database';
SET @secondStatus = 'The selected category does not exist in the database';
IF (EXISTS (SELECT name FROM Category where name='Approval Tasks'))
SELECT @firstMessage;
ELSE
SELECT @secondMessage;
Execution: In the Tracking Data there can be seen the returned results:
The same result can be seen when executing the same SQL Select query from SQL Management Studio:
Select Row(s) from Table
This activity selects and returns Row(s) from a SQL Server database table. The example below displays a classic select query: select all data from a given table.
Activity Parameters
The Select Row(s) from Table activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Distinct (combo box control) (type: string): whether the returned values should be distinct or not.
- Fields (text box editor type) (type: string): the column headers that you want to be returned with the select command. If you enter more than one you should separate them with ','. If all fields needs to be returned, then just use "*". The " * " has the same role as used in the SQL select all statement: select * from table
- Limit (text box editor type) (type: string): limit the number of results. If the field is left blank, then the activity will return all the rows from the table
- Order By (text box editor type) (type: string): the column header that you want to result to be ordered by. For example, if "ID" is inserted, then the values returned will be ordered depending on the value set in the ID column. If no value is provided, the activity will return the values in the same order as taken from the table.
- Order by Direction (combo box control) (type: string): choose to order the resulted rows in Ascending or Descending direction. It will be ignored if Order By is not set.
- Table (text box editor type) (type: string): the table name on which to run the select.
- WHERE (text box editor type) (type: string): the Where statement. For example: ID>2, where ID is a column header.
The Read Only Output variable are the possible output values that the activity will provide:
- Output (type: collection<collection<string>>): outputs a collection of collections of strings containing the selected rows.To use the elements of the collection, use Get Element From Collection activity.
- Output Columns (type: int32): outputs the number of columns returned.
- Output Rows (type: int32): outputs the number of rows returned.
A more complex example, with fields, orders and statements can be seen in the image below:
Execution: In the Tracking Data there can be seen the returned results:
The same result can be seen when executing the same SQL Select query from SQL Management Studio:
