This is a quick example of how to take the output of a sql query and email it to someone.
The above image shows a few of the pieces. Create a variable, in this case filename. You then open the properties editor for the sendmail task. You select fileAttachments and link the variable. This will send the text file you create as an attachment. Now we go to the DataFlow task to see how to connect the output of your sql to that same textfile.
You create a DataReader source and connect the output to a FlatFile destination. To create the FlatFile destination you need a FlatFileConnectionManager and that is where you connect the output to a physical file. Click on your FlatFileConnectionManager then on the properties window on the right, click on the + sign to the left of Expressions. Click on the ellipses (...) on the right side, which will open the PropertiesExpressionsEditor you see on the left. Select the ConnectionString property and select the filename variable.
You have now connected the output of your sql to the file attachment in your email.