I have moved to...

http://ihafidh.wordpress.com
Wordpress seems to be much faster than Blogger. Plus they have more themes with better designs.

Viewing Query Plans

This is more of a reminder to myself. I can't always remember the commands to show the execution plan in text. It's nice having the graphical interface but when you have a big execution plan then seeing it in text is much easier. Here are the commands:

-- simple estimated execution plan

set showplan_text on

GO

--*** estimated execution plan with more detail. best to use this ****

set showplan_all on

GO

-- actual execution plan. runs the query. so it's better to use showplan_all for a big query 

set statistics profile on

GO

More detail here

Executing a BAT file in SSIS

I wanted to execute a batch file in SQL Server Integration Services, and I wanted to utilize two parameters from the Execute Process Task in SSIS: StandardOutputVariable and SuccessValue.

The reasons for using these two parameters is that I wanted to know if the external batch file failed or not. And I wanted to capture any error messages produced.

In SSIS, it is straightforward. Drag the "Execute Process Task" and select your .bat file.

For the StandardOutputVariable create a new string variable. For the SuccessValue specify 0.

Now, the tricky part is getting the SuccessValue to work with %ErrorLevel% from the bat file. To do that, make sure that the last line in your bat file is:

EXIT %ERRORLEVEL%

This will return the error level to the Execute Process Task. Otherwise, the process will always seem to succeed.

The other tricky part is getting an actual error message produced from the bat file. Apparently there are separate output streams produced by the bat file. So you have to use some redirection operators. I found this out via a blogger CrankyBit. Microsoft's reference can be found here. Here is an example:

C:\WINDOWS\system32\ping.exe www.google.com >> mybat_log.txt 2>&1

Notice the 2>&1 at the end. That will send out the error message that you would normally see when executing in a command prompt.

So I used that to capture the error logs.

Finally, I echoed out a custom error message to send to the output variable. I can then use this variable to send out an email for notification.

So here is the complete bat file:

@Echo OFF

Echo ================== >> mybat_log.txt

Echo Start: %Date% %Time%

Echo.

Echo Start: %Date% %Time% >> mybat_log.txt

rem ==================================

rem 2>&1 sends the actual error message

rem http://technet.microsoft.com/en-us/library/bb490982.aspx

rem ===================================

C:\WINDOWS\system32\ping.exe www.google.com >> mybat_log.txt 2>&1

Echo ErrorLevel = %ERRORLEVEL% >> mybat_log.txt

IF %ERRORLEVEL% NEQ 0 (ECHO An error has occurred on the mybat.bat file.

Echo Please check the log file for more detail at D:\mybat_log.txt

) ELSE (ECHO The mybat.bat file ran successfully)

Echo.

Echo End: %Date% %Time%

Echo End: %Date% %Time% >> mybat_log.txt

Echo.

Echo ---

Echo Sent from SQL Server SSIS Package

EXIT %ERRORLEVEL%