Use SQL from the Command Line
Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Saint Augustine, FL. Bradley is a former consultant, trainer, and has coauthored 6 SQL Server and Power BI books, most recently Learn Microsoft Fabric. As a member of the Microsoft Fabric product team, Bradley works directly with customers to solve some of their most complex data problems and helps shape the future of Microsoft Fabric. Bradley gives back to the community through speaking at events such as the SQLBits, Fabric Community Conference, PASS Community Data Summit, SQL Saturdays, Code Camps, and user groups across the country including locally at the Jacksonville SQL Server User Group (JSSUG). He is a contributor on SQLServerCentral.com and blogs on his personal site, BradleySchacht.com.
Once the execute SQL task runs and the variable is populated create an expression for the body of email that will display the results of the query. You will need to cast the variable as a string as the expression will not allow you to mix the Int32 data type of the variable. The result will be something along the lines of the following. 
As you can see, when the package is run, the email body will read: “The count of stores with sales was # yesterday.” This would also eliminate the need for an attachment on the email. However, if the client required the query results be attached to the email for whatever reason there is a problem. In this case the command line is an alternative usingSQLCMD. You can call a SQL query from the command line using the command SQLCMD. There are a lot of arguments that can be passed with this and you can find some of the others online if need be. In this case a saved query will be used by inputting –i, for input file and –o, for output file where the results of the query will be stored. 
This will take the results of the query that is stored in the file SavedQuery.sql and save it to the file QueryOutput.txt which can then be attached to the email. This is all a great idea, but it would force the query to run before being able to attach the file in the send mail task. In order to automate the entire process inside of a package, a little more work is needed. You cannot, with the execute process task pass arguments into the command line. So calling cmd.exe and using SQLCMD –i c:SavedQuery.sql –o c:QueryOutput.txt as the argument does not work. To solve this issue you can create a batch file to run in the execute process task which will be this exact line of code. 
To create the batch file, open notepad and enter the command that is to be executed. When saving the file be sure to change the Save as type to All Files and use the file extension .bat when entering the file name. With the batch file created use the execute process task to run the batch file and attach the output file to the email. 
The data flow will looks something like this in the end: 
This is a great, simple way to save the output of any query to a file without having to go through scripting or a data flow in SSIS. Some other commands to go with SQLCMD: -U login id -P password -S server -d database name -q directly enter a query into the command line -Q directly enter a query into the command line and exit command line after the query completes -z new password -L list servers -W remove trailing whitespace -u unicode output -b create batch report on error Here is an example of how to use a few of the commands interactively with the command line: 