SQL Query Source enables you to retrieve data from database using a SQL query or a stored procedure. You can specify any valid SELECT statement or a stored procedure call as query. In addition, you can parameterize your queries dynamically changing values of the parameters at runtime.
Adding a SQL Query source object allows you to transfer data returned by the SQL query. An example of what an SQL Query source object looks like is shown below.
To configure the properties of a SQL Query Source object after it was added to the dataflow, right-click on it and select Properties from the context menu. The following properties are available:
Source Connection screen – allows you to enter the connection information for your SQL Query, such as server name, database and schema, as well as credentials for connecting to the selected database.
SQL Query Source screen:
Enter the SQL expression controlling which records should be returned by this source. The expression should follow SQL syntax conventions for the chosen database provider.
For example, select OrderId, OrderName, CreatedDtTm from Orders