The SQL Component is certainly one of the most flexible and useful - it allows you to easily query, execute, update, insert, merge, upsert and delete data with total flexibility. This component can act as a source, transformation or destination component on a data-flow, and can also be used from a control-flow.

This component presents the following user interface :



Initially you are asked to select an OLEDB or ADO.Net connection and select a SQL Action from the list presented. Select the most relevant action, using Custom SQL where your operation is not primarily associated with an individual table, view or stored procedure. Having selected an action, the list of relevant tables, view, functions or stored procedures is then presented or you to select from :



You can also select a timeout, and an optional location to store the result of the action on this page.



For an update query the query result will be the number of records updated. For a delete query this will be the number of records deleted. For an insert query this will be the value of the identity of the inserted row (for SQLServer / MySQL only). For a select or custom query or stored procedure call this will be the query result. If the query or stored proecure returns more than one column, you should select <Record Set>, which enables an additional page to configure the result columns. This page is shown below for a Select example from a table with 4 columns :



This Record-Set page allows you to select which columns you want to return, and how you want to bind them to variables and data-flow columns. You can also select via the Record Mode property whether this is a join operation, such that a new data-flow record will be created for every row of the result set, or whether you are only interesated in the first record, or whether you expect exactly one record and the component should fail otherwise.

Returning to our Insert example, having selected a table, we can then bind parameters for the Insert operation. To do this, click on the Parameter Bindings tab :



Here, for every column in the selected table, we can drag a binding value from a variable or column, or NULL (which is the default for unmapped items), or from a contant value. We can also select from the dropdown column on the right the type of the binding - whether we want to write the value or (for a merge, upsert, delete or update operation) match the value. You can also select whether to coerce the value into the destination type, or whether to return an output parameter if calling a stored procedure.

In turn this generates SQL, which we can view on the SQL page. There are 3 sets of SQL, one to contain any initialisation (which in a data-flow is executed just once at the start), one for the primary action (which in a data-flow is executed once for every row) and one for commit/cleanup functions (which in a data-flow is executed once at the end). In a control-flow, all 3 are executed in sequence exactly once. Only the SQLServer Merge action generates anything other than primary SQL currently.



This SQL can be customized if required, and you can even add additional parameters in the format @parameterName (or using the parameter prefix for the underlying database). You can also define parameters that are resolved into the query text rather than being passed to the database for execution by using the #prefix. This enables you to write meta-queries in effect. If you selected Custom SQL as the action type then this SQL will be blank by default and will have to be entered. If you customize generated SQL you will be warned when visiting the Parameter Bindings page and given the option as to regerneate the SQL or keep your modifications.

You can also configure an execution condition on the Condition page.  This allows you to enter a simple expression to determine whether or not the SQL action should execute or be skipped.  For example you could enter [ContactID] != null to execute the lookup only where the ContactID column does not contain a null value.  The condition syntax is essentially that of C#, C, C++, Java and Javascript. and uses double equals (==) to test for equality, double ampersand for logical AND (&&) and double pipe for logical OR (||).  Conditions can include simple arithmetic expressions and variables, e.g. [GrossProfit]/[Turnover]*100 > @MarginThreshold.  You can enter column names in [column] syntax and variables with a @ prefix, or you can use the Insert Token link for assistance in embedding tokens.



Finally the error-handling page allows you to configure what should happen if the action raises an error or returns NULL or no rows, and whether truncated values should cause an error.



Note that this SQL Component can also be accessed as an Foreach Enumerator, whereupon you can iterate rows in a record-set. To access this function, drag a Foreach Loop Container task onto your control-flow surface, then select the BlueSSIS SQL Enumerator.



From here you can click Configure..., and set up your query as required.



So there you have it - everything you possible want to do with a database at your fingertips and ready in seconds!

All BlueSSIS data-flow components support properties 'Fail on truncation' and 'Unicode mode', allowing you to define applicable truncation and Unicode settings. These properties can be found in the Visual Studio/BIDS Properties window as shown below :