More often than not an execute SQL task in SSIS will do the trick. If not, then the data flow will accomplish whatever objective is set out. Occasionally those can become cumbersome in a package to accomplish something simple. In those cases using SQL from the command line could be the perfect fit. SQLCMD will allow SQL commands to be run from the command line in has the ability to define connections to specific databases and use specific credentials, use T-SQL, use stored scripts, work with variables and even store outputs to a file. There were command line utilities for SQL 2000, but SQLCMD goes far beyond what those offered.
There are several ways to use SQLCMD:
· Using SQLCMD
· Interactively from the command line
· Using a .bat file
Let’s take the example of attaching the output from simply SQL query to an email in an SSIS package.
One option would be the following:
SELECT COUNT(*) FROM Sales in an execute SQL task and pass the result in the variable.
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:
