Microsoft Technologies: SSIS Query builder

In SQL Server Integration Services, SSIS Query builder makes the developer life easy to design the SQL Queries. If you don’t know how to write the SQL Queries or if you find it difficult to perform grouping, sorting and join operations or if you find it difficult to design the complex SQL Queries then you can use SSIS Query builder to design your desired queries using Graphical User Interface.

SSIS Query Builder Example

In this example we will show you, How to design an SQL Query using SSIS Query Builder. For this example we will design the query for OLE DB Source. So, Within the OLE DB Source Editor click on the Build Query button as shown below.

Once you click on the Build Query button it will open the Query Builder in separate window. Before designing the SQL query you should understand the different panes present in the SSIS Query builder.

Diagram Pane: This pane is used to select the tables, Views or functions. SSIS Query builder will automatically join the tables as per the database relations. After selecting the required tables we can select the required columns by check marking the columns.
Grid Pane: All the selected columns will be displayed in this panes. This pane is very useful to apply sorting (ORDER BY ASC and ORDER BY DESC) and also grouping the column names (GROUPING Statement)
SQL Pane: SSIS Query builder will automatically build the SQL query for us in this pane.
Result Pane: Once you finished designing your SQL query then click on the Execute button (Play button) will display the SQL Query result in this pane.

Right Click on the Diagram Pane to add the required table.

From the above screenshot you can observe that, we selected the Person table. Let me add one more table (Sales Person) as well to show the Joins as well.

From the above screenshot you can observe that, SSIS Query builder automatically Joined the two tables Person and SalesPerson using the BusinessEntityID column.

Please select the required columns by check marking the column names in the Diagram Pane.

Sorting: Within the Grid Pane, Please change the Sort Type to Ascending or Descending as per you requirement. In this example we are sorting First Name Ascending and Last Name Descending

Please change the Sort Order option as per your requirement. For this example, it will sort the data by First Name in the Ascending order first and then it will sort the Data by Last Name in descending order.

From the above screenshot you can see the final SQL Query generated by the SSIS Query builder. Let us see whether the built query is executing perfectly or not by clicking the execute button on the top.