The Query builder in SSIS makes the developer’s life easy to design the SQL Queries. If you don’t know how to write the SQL Queries, or if it is challenging 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 a Graphical User Interface.
Query Builder in SSIS Example
In this example, we show you how to design an SQL Query using Query Builder in SSIS. For this example, we will create the query for OLE DB Source. So, Within the OLE DB Source Editor, click on the Build Query button.
Once you click on the Build Query button, it will open the Query Builder in a 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. Query builder in SSIS will automatically join the tables as per the database relations. After selecting the required tables, we can choose the columns needed by check-marking the columns.
- Grid Pane: All the selected columns will display in this panes. This pane is handy 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 your requirement. In this example, we are sorting First Name Ascending and the 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 sorts the Data by the 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.
You can see the result. Click ok button to finish designing the SQL Query using the Query builder tool
