Activities
...
- Connection (text box editor type) (type: object): SQL Host connection. The Output from Connect to SQL activity should be used as Input here
- Role (text box editor type) (type: string ): Enter the name of the role that will receive permissions
The Read Only Output variable are the possible output values that the activity will provide:
- Output (type: Boolean): Outputs True or False if the execution was successful or not.
...
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:
...
This activity removes privileges for an user a role to a database.
Activity Parameters
The Remove Role Access from Database activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Connection (text box editor type) (type: object): the connection object that was obtained as output from a Connect To OracleSQL activity SQL Host connection. The Output from Connect to SQL activity should be used as Input here.
- Role (value selector listtext box editor type) (type: string): Enter the name of the roles role 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.have denied permissions.
-
The Read Only Output variable are the possible output values that the activity will provide:
- Output (type: boolean): outputs True or False if the execution was successful or not.
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:
Run query
This activity runs a query on a SQL Server database.
...
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 [FlowsterMSSQLActivitiesFlowsterSQLActivities].[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.
...
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.
...
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):Enter 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 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.
...
- 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:
...