Blog Page

SSIS Error Logging and Notification

sqlsafety 1 Comment

I recently spoke at SQL Saturday #192 in Tampa and used this package as a demonstration.  I have made it available for download here.

SSIS comes with a surplus of built in tools and features for error logging and reporting, but once a package is set free in the wild and executed within a SQL Server agent job, or other scheduling process the ability to receive detailed and timely error information becomes a bit trickier. Despite all of the built in features there is no out of the box solution that provides email notification containing detailed error information of a running package.

Some years ago I found a gem that detailed how to modify a package to record errors within a package, using event handlers and script components, and send the error information once the package completes using a send mail task. The post details the steps necessary and gives a great deal of insight into errors within SSIS, http://agilebi.com/jwelch/2007/05/05/handling-multiple-errors-in-ssis/, Thank youJohn Welch!!!

I don’t wish to ruin the ending of John’s technique, but the premise of his solution utilizes script tasks in both the OnError and OnPostExecute event handlers and send mail task to send the error information. After utilizing this method for quite some time there were two enhancements that I preferred, which are ENTIRELY personal preference. The first is formatting the message in an easy to read arrangement and the second is to insure I receive only a single message with all the error information that was raised within the package.

My first preference, to receive a well formatted email, is straight forward and would appear quite easy with the out of the box tasks in SSIS, but unfortunately the Send Mail task does not support html messages, which somewhat limits formatting the message body. Anyone who knows me is aware of my inability, as well as desire, to create anything aesthetically pleasing so this wish may seem out of character for me. Despite my visual design limitations in order to be able to quickly respond to an error the information should be laid out in an easy to read and well defined format.

My second preference seemed very straight forward and easy to meet, or so I thought. After modifying some of my packages adding the script and send mail tasks in event handlers I discovered that if an error occurred I would receive numerous emails. This completely perplexed me and it was only as I began to step through the process that I realized I had completely written off event propagation. Each executable has its own associated event handlers. Each event propagates up through the package, by default, unless the “Propagate” variable is set to “False”. An example of propagation can be demonstrated with an execute SQL task within a sequence container. If an error occurs within the execute SQL task it will propagate up to the sequence container, which in turn propagates up to the package. The result is that using event handlers to compile and email error messages can result in numerous emails being sent unless careful attention is paid to setting the “Propagate” variable to “False” in all tasks and event handlers that will suppress multiple executions. The issue that I run into is that in complex packages with multiple executable and containers it can be quite cumbersome and time consuming to create script tasks to suppress unwanted propagation. **If you are not familiar with propagation Jamie Thomson has an amazing video that steps through event handler propagation here.

I began to develop a means of capturing package errors that I would later send in a defined format. After experimenting with event handlers to collect error information I realized that there was an easier way that is built into SSIS. All packages support configuring log providers, which are used to capture events from within a package. Of the log providers available in SSIS, text file, SQL server profiler, SQL Server, Windows event log, and XML file, I decided to use SQL Server to store OnError events. Under the hood logging uses the same method of recording errors as I mentioned above, using the OnError event handler, which means that a single error can be recorded multiple times due to propagation. The reason that I chose this method over capturing error information within a package in a variable is primarily due to ease as it allows to act on the stored information within a single script task as the last task in a package, rather than requiring multiple script tasks in all associated OnError event handlers for all tasks and containers.

With the error information being stored it is time to address the aesthetics of the error email. As I noted the send mail task does not support sending an html formatted message, which limits the ability to design an easily readable presentation. As such I decided to use a script task to retrieve the error information, create an html formatted message using the error details, and send the email usin the System.NET.Mail namespace. I defined the format of the email as illustrated in the example below.

ERROR OCCURRED IN PACKAGE
Task Error Occurred Error Code Error Message
**cntn_Errors – 1073450974 SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “pkeyErrorTable” (2) failed with error code 0xC0209029 while processing input “OLE DB Destination Input” (15). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
**cntn_Errors – 1071636471 SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Violation of PRIMARY KEY constraint ‘PK__pkerror__357D0D3EB66D974B’. Cannot insert duplicate key in object ‘dbo.pkerror’. The duplicate key value is (1).”.
**cntn_Errors – 1071607767 SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “pkeyErrorTable.Inputs[OLE DB Destination Input]” failed because error code 0xC020907B occurred, and the error row disposition on “pkeyErrorTable.Inputs[OLE DB Destination Input]” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
DuplicateKey -1073450974 SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “pkeyErrorTable” (2) failed with error code 0xC0209029 while processing input “OLE DB Destination Input” (15). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
DuplicateKey -1071636471 SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Violation of PRIMARY KEY constraint ‘PK__pkerror__357D0D3EB66D974B’. Cannot insert duplicate key in object ‘dbo.pkerror’. The duplicate key value is (1).”.
DuplicateKey -1071607767 SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “pkeyErrorTable.Inputs[OLE DB Destination Input]” failed because error code 0xC020907B occurred, and the error row disposition on “pkeyErrorTable.Inputs[OLE DB Destination Input]” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
**ErrorLogging – 1073450974 SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “pkeyErrorTable” (2) failed with error code 0xC0209029 while processing input “OLE DB Destination Input” (15). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
**ErrorLogging – 1071636471 SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Violation of PRIMARY KEY constraint ‘PK__pkerror__357D0D3EB66D974B’. Cannot insert duplicate key in object ‘dbo.pkerror’. The duplicate key value is (1).”.
**ErrorLogging – 1071607767 SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “pkeyErrorTable.Inputs[OLE DB Destination Input]” failed because error code 0xC020907B occurred, and the error row disposition on “pkeyErrorTable.Inputs[OLE DB Destination Input]” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

**Red denotes error is at the package level and could have “bubbled up” from the calling task

As I have now provided a means of storing the error information and defined a format to present the information I can move forward with my script task development. The script task needs to define three readonly variables, System::ExecutionInstanceGUID, System::TaskID, and System::PackageID which will be used in the body of the script. I will provide the body of the script and then step through and describe what it is doing.

Imports System.Data.SQLClient

Imports System.NET.Mail
Imports System.IO  Public Sub Main()
Dim msg As StringBuilder = New StringBuilder
Try
msg.Append
(“<HTML><Body><table border=””1″”>”)
msg.Append(“<col align=””Center””>”)
msg.Append(“<col align=””Center””>”)
msg.Append(“<col align=””Center””>”)
msg.Append(“<tr>”)
msg.Append(“<td colspan=3><strong>ERROR OCCURRED IN PACKAGE<strong></td></tr>”)
msg.Append(“<tr>”)
msg.Append(“<th><Strong>Task Error Occurred</Strong></th>”)
msg.Append(“<th><Strong>Error Code      </Strong></th>”)
msg.Append(“<th><Strong>Error Message</Strong></th>”)
msg.Append(“</tr>”)

CreateMessate(msg)
Catch ex As Exception
msg.Append
(“<tr>”)
msg.Append(“<td>Generate Error Message</td>”)
msg.Append(“<td width=100>0</td>”)
msg.Append(“<td>” &amp; ex.Message.ToString &amp; ex.InnerException.ToString &amp; “</td>”)
msg.Append(“</tr>”)
End Try
msg.Append
(“</Table><font size=””1″” color=””red””>**Red denotes error is at the container or package level and could have ” &amp; _
“””<em>bubbled up</em>”” from the calling task</font></Body></HTML>”)

SendMessage(msg)

Dts.TaskResult = ScriptResults.Success
End Sub

Private Sub CreateMessate(ByVal msg As StringBuilderDim src As String = Dts.Variables(“PackageID”).Value.ToString.Replace(“{“, “”).Replace(“}”, “”)
Dim cmd As SqlCommand
Dim cmdtext As String
cmdtext = “SELECT sourceid, source, datacode, message FROM sysssislog WHERE event = ‘OnError’ AND executionid = ‘” &amp; _
Dts.Variables
(“ExecutionInstanceGUID”).Value.ToString.Replace(“{“, “”).Replace(“}”, “”) &amp; “‘” &amp; _
“ORDER BY source, datacode”

Dim cn As New SqlConnection(“Server=localhost; Database=Adventureworks2012;Integrated Security=SSPI”)
cmd = New SqlCommand(cmdtext.ToString, cn)

cn.Open()
Dim dr As SqlDataReader
dr
= cmd.ExecuteReader
While dr.Read

If src.ToUpper = dr.Item(0).ToString.ToUpper Or dr.Item(1).ToString Like “cntn_*” Then
msg.Append(“<tr>”)
msg.Append(“<td><font color=””red””><em>**” &amp; dr.Item(1).ToString &amp; “</em></font></td>”)
msg.Append(“<td width=100><font color=””red””>” &amp; dr.Item(2).ToString &amp; “</font></td>”)
msg.Append(“<td><font color=””red””>” &amp; dr.Item(3).ToString &amp; “</font></td>”)
msg.Append(“</tr>”)
Else
msg.Append(“<tr>”)
msg.Append(“<td>” &amp; dr.Item(1).ToString &amp; “</td>”)
msg.Append(“<td width=100>” &amp; dr.Item(2).ToString &amp; “</td>”)
msg.Append(“<td>” &amp; dr.Item(3).ToString &amp; “</td>”)
msg.Append(“</tr>”)
End If

End While
If
cn.State <> ConnectionState.Closed Then
cn.Close()
cn.Dispose()
cmd.Dispose()
End If

End Sub

Public Sub SendMessage(ByVal msg As StringBuilder)
Try
Dim varHTMLMail As MailMessage
Dim varSMTPClient As SmtpClient
Dim varMailBody As String = String.Empty
Dim varAddresses As String = String.Empty

varMailBody = msg.ToString

varAddresses = “Error@Error.com”
varHTMLMail = New MailMessage(“Error@Error.com”, varAddresses, “SSIS Error Messenger”, varMailBody)
varHTMLMail.IsBodyHtml = True
varSMTPClient = New SmtpClient(“localhost”)
varSMTPClient.UseDefaultCredentials = True

varSMTPClient.SSend(varHTMLMail)

Catch ex As Exception
Dim err As StringBuilder = New StringBuilder
err.Append
(“<html>”)
err.Append(“<body>”)
err.Append(“<table border=””1″”>”)
err.Append(“<tr>”)
err.Append(“<td colspan=2><strong>ERROR OCCURRED IN GENERATE ERROR MESSAGE TASK/SENDMESSAGE SUB<strong></td>”)
err.Append(“</tr>”)
err.Append(“<td>Erro Message: </td>”)
err.Append(“<td>” &amp; ex.Message.ToString &amp; “</td>”)
err.Append(“</tr>”)
err.Append(“<td>Erro Inner Message: </td>”)
err.Append(“<td>” &amp; ex.InnerException.ToString &amp; “</td>”)
err.Append(“</table>”)
err.Append(“</Body>”)
err.Append(“</html>”)

Dim varHTMLMail As MailMessage
Dim varSMTPClient As SmtpClient
Dim varMailBody As String = String.Empty
Dim varAddresses As String = String.Empty

varMailBody = msg.ToString

varAddresses = “Error@Error.com”
varHTMLMail = New MailMessage(“Error@Error.com”, varAddresses, “SSIS Error Messenger”, varMailBody)
varHTMLMail.IsBodyHtml = True
varSMTPClient = New SmtpClient(“localhost”)
varSMTPClient.UseDefaultCredentials = True

varSMTPClient.Send(varHTMLMail)

End Try
End Sub

The first step is to instantiate the variable msg that will be used to hold the error message formatted in html, I used stringbuilder as the data type rather than string for performance sake as this variable is constantly appended to. Then within Ty…Catch the msg is appended with html to begin the message and format the table:

Dim msg As StringBuilder = New StringBuilder
Try
msg.Append
(“<HTML><Body><table border=””1″”>”)
msg.Append(“<col align=””Center””>”)
msg.Append(“<col align=””Center””>”)
msg.Append(“<col align=””Center””>”)
msg.Append(“<tr>”)
msg.Append(“<td colspan=3><strong>ERROR OCCURRED IN PACKAGE<strong></td></tr>”)
msg.Append(“<tr>”)
msg.Append(“<th><Strong>Task Error Occurred</Strong></th>”)
msg.Append(“<th><Strong>Error Code      </Strong></th>”)
msg.Append(“<th><Strong>Error Message</Strong></th>”)
msg.Append(“</tr>”)

The message format has been initiated so it is now time to retrieve the error information from the log table.  The CreateMessage Sub accepts the msg variable to append the error information to the message body formatted in html.  The CreateMessage uses a DataReader to extract the error information from the sysssislog table which is filtered with the OnError event and uses the read only variable System::ExecutionInstanceGUID to insure that only error information for the specific execution is returned.  The DataReader iterates through the results placing the results in the specific html table columns.  During each iteration the values are evaluated to see if the current row is the result of event propagation.  If the value is ascertained to have been captured as a possible result of propagation then it is formatted in red.

Private Sub CreateMessate(ByVal msg As StringBuilderDim src As String = Dts.Variables(“PackageID”).Value.ToString.Replace(“{“, “”).Replace(“}”, “”)
Dim cmd As SqlCommand
Dim cmdtext As String
cmdtext = “SELECT sourceid, source, datacode, message FROM sysssislog WHERE event = ‘OnError’ AND executionid = ‘” &amp; _
Dts.Variables
(“ExecutionInstanceGUID”).Value.ToString.Replace(“{“, “”).Replace(“}”, “”) &amp; “‘” &amp; _
“ORDER BY source, datacode”

Dim cn As New SqlConnection(“Server=localhost; Database=Adventureworks2012;Integrated Security=SSPI”)
cmd = New SqlCommand(cmdtext.ToString, cn)

cn.Open()
Dim dr As SqlDataReader
dr
= cmd.ExecuteReader
While dr.Read

If src.ToUpper = dr.Item(0).ToString.ToUpper Or dr.Item(1).ToString Like “cntn_*” Then
msg.Append(“<tr>”)
msg.Append(“<td><font color=””red””><em>**” &amp; dr.Item(1).ToString &amp; “</em></font></td>”)
msg.Append(“<td width=100><font color=””red””>” &amp; dr.Item(2).ToString &amp; “</font></td>”)
msg.Append(“<td><font color=””red””>” &amp; dr.Item(3).ToString &amp; “</font></td>”)
msg.Append(“</tr>”)
Else
msg.Append(“<tr>”)
msg.Append(“<td>” &amp; dr.Item(1).ToString &amp; “</td>”)
msg.Append(“<td width=100>” &amp; dr.Item(2).ToString &amp; “</td>”)
msg.Append(“<td>” &amp; dr.Item(3).ToString &amp; “</td>”)
msg.Append(“</tr>”)
End If

End While
If
cn.State <> ConnectionState.Closed Then
cn.Close()
cn.Dispose()
cmd.Dispose()
End If

End Sub

Execution returns back to the Public Main and the Catch block is used to capture any errors that occurred in the script which includes the script error information.  The message body appends the note denoting format for possible propagated error messages and the html code is included to close the table, body, and html tags.

If an error does not occur then message body appends the note denoting format for possible propagated error messages and the html code is included to close the table, body, and html tags.  The script then calls the SendMessage sub passing the full html formatted error message.

The SendMessage sub uses the System.Net.Mail namespace to send the html formatted message.  Structured error handling is included within the sub procedure and if an error is encountered it will be added to the msg body.  I haven’t quite completed the logic to dynamically handle failure at this point, but had considered writing the error message to a known file location.

Public Sub SendMessage(ByVal msg As StringBuilder)
Try
Dim varHTMLMail As MailMessage
Dim varSMTPClient As SmtpClient
Dim varMailBody As String = String.Empty
Dim varAddresses As String = String.Empty

varMailBody = msg.ToString

varAddresses = “Error@Error.com”
varHTMLMail = New MailMessage(“Error@Error.com”, varAddresses, “SSIS Error Messenger”, varMailBody)
varHTMLMail.IsBodyHtml = True
varSMTPClient = New SmtpClient(“localhost”)
varSMTPClient.UseDefaultCredentials = True

varSMTPClient.SSend(varHTMLMail)

Catch ex As Exception
Dim err As StringBuilder = New StringBuilder
err.Append
(“<html>”)
err.Append(“<body>”)
err.Append(“<table border=””1″”>”)
err.Append(“<tr>”)
err.Append(“<td colspan=2><strong>ERROR OCCURRED IN GENERATE ERROR MESSAGE TASK/SENDMESSAGE SUB<strong></td>”)
err.Append(“</tr>”)
err.Append(“<td>Erro Message: </td>”)
err.Append(“<td>” &amp; ex.Message.ToString &amp; “</td>”)
err.Append(“</tr>”)
err.Append(“<td>Erro Inner Message: </td>”)
err.Append(“<td>” &amp; ex.InnerException.ToString &amp; “</td>”)
err.Append(“</table>”)
err.Append(“</Body>”)
err.Append(“</html>”)

Dim varHTMLMail As MailMessage
Dim varSMTPClient As SmtpClient
Dim varMailBody As String = String.Empty
Dim varAddresses As String = String.Empty

varMailBody = msg.ToString

varAddresses = “Error@Error.com”
varHTMLMail = New MailMessage(“Error@Error.com”, varAddresses, “SSIS Error Messenger”, varMailBody)
varHTMLMail.IsBodyHtml = True
varSMTPClient = New SmtpClient(“localhost”)
varSMTPClient.UseDefaultCredentials = True

varSMTPClient.Send(varHTMLMail)

End Try
End Sub

For brevity’s sake I have not included screen shots of the different tasks and steps or the C# code, but will email the example packages and/or code if you request them.  Email me at sqlsafety@gmail.com for the samples.

The sample package can be downloaded from my site here.

SSISEndtoEnd

T-SQL Parameters in an SSIS Execute SQL Task

sqlsafety 0 Comments

SSIS provides several tasks that directly pass T-SQL queries directly to SQL Server.  There are numerous occasions in an SSIS package where when input needs to be passed into a T-SQL statement prior to the execution.  Several examples of this are creating dynamic statements and passing parameters to a stored procedure.  I covered creating dynamic statements using variables in SSIS in this post, so this post will focus on a bit more simplistic approach of passing parameters to execute SQL tasks.
In order to prepare for the SSIS package pre-create the stored procedure that will be called in the AdventureWorks2012 database, the below code can be used:

CREATE PROC usp_GetName @BusinessEntityID INT
AS
SELECT
FirstName,
MiddleName,
LastName
FROM Person.Person
WHERE BusinessEntityID = @BusinessEntityID
Create an SSIS package called T-SQLParameters and a create a package scoped variable called BusinessEntitytyID, leave the default data type of integer, but set the value to 10.

image
Drag and drop an execute SQL task onto the control flow pane and rename it Retrieve Person Name.
Configure the task’s SQLStatements property to pass the command EXEC usp_GetName ? and create a connection manager to an instance of SQL that has the sample database AdventureWorks2012 that has the stored procedure usp_GetName already created.
image
The T-SQL statement is obviously is not syntactically correct based on T-SQL standards, the whole “?” thing should be a clue.  The “?” is a place holder for parameters in a statement, both input and output parameters are supported, in either execute SQL or data flow tasks.  The statement in the task is configured to execute a parameter in SSIS, but the task now needs to be configured to recognize the parameter being passed.  In the Parameter Mapping menu configure the Variable Name as BusinessEntityID, Direction as Input,  Data Type as Long, and the Parameter Name as @BusinessEntityID.
image
The Parameter Name can either be the ordinal position in which the parameter appears, using a zero based index, in the task, input or output, or by the parameter name.  In this case there is only one parameter so the name could be replaced with “0”.
Executing the package now will successfully execute the stored procedure usp_GetName passing the BusinessEntityID of 10 based on the valued of the associated parameter.  In order to make things more interesting let’s add an output parameter to the usp_GetName stored procedure and reconfigure the execute SQL task.  Alter the stored procedure using the below DDL statement:
ALTER PROC usp_GetName @BusinessEntityID INT, @Rows INT OUTPUT AS
SELECT
FirstName,
MiddleName,
LastName FROM Person.Person WHERE BusinessEntityID = @BusinessEntityID SELECT @Rows = @@ROWCOUNT;

Add a variable to the package called RowCount and leave the defaults, this will be used to capture the output parameter RowCount fo the stored procedure.  The Retrieve Person Name execute SQL task needs to be modified to successfully execute.  Modify the SQLStatement property to now show:
DECLARE @RowCount INT
EXEC usp_GetName ?,  @RowCount = @RowCount OUTPUT
SELECT ? = @RowCount
The Parameter Mapping needs to be changed to take into account the OUPUT parameter so modify this by adding the RowCount variable and setting its direction as OUPUT and ordinal position to 1 in the Parameter Name property:
image     In order to verify that the output value of the query is being properly assigned drag and drop a script task onto the control flow pane and configure it to accept the RowCount as a ReadOnlyVariable.
image In the C# script body add a message box show method to display the value of the RowCount varaible value:
MessageBox.Show(Dts.Variables["RowCount"].Value.ToString());
Executing the package will not display a message box as shown below:
image      This sample focused on an execute SQL task, but a similar technique can be used for a data flow task source using a variable.  This technique is outlined on my post SSIS – Replace Dynamic SQL With Variables.

The sample package for this post can be downloaded from my site here.

SSISEndtoEnd

SSIS Custom Logging

sqlsafety 2 Comments

     In a previous post I outlined how to log and record errors, which uses the underlying log providers of SSIS.  This provides an excellent means to capture information of a package at runtime, but it does have limitations, MSDN documents logging in SSIS here.  Log providers can be used to capture specific information:

  1. Computer
  2. Operator
  3. SourceName
  4. SourceID
  5. ExecutionID
  6. MessageText
  7. DataBytes
  8. StartTime
  9. EndTime
  10. DataCode

upon the firing of the following events:

  1. OnError
  2. OnExecStatusChanged
  3. OnInformation
  4. OnPostExecute
  5. OnPostValidate
  6. OnPreExecute
  7. OnPreValidate
  8. OnProgress
  9. OnQueryCancel
  10. OnTaskFailed
  11. OnVariableValueChanged
  12. OnWarning
  13. PipelineComponentTime
  14. Diagnostic

     I am in no means trying to diminish the usefulness of native SSIS logging, but there are most definitely several areas that fall through the cracks.  The most immediate example that comes to mind is recording how many rows are affected in a data flow task or execute SQL task.  It is a common requirement to need maintain the execution time of SSIS tasks, which can easily be done using native log providers, as well as the number of affected rows for appropriate tasks.  This information can be invaluable in order to maintain a baseline of package performance.

     Custom logging first requires that a destination is created to store the log information and in this example I’m going to use a table, which is created with the below DDL script:

 

USE AdventureWorks2012;
GO

 

CREATE TABLE CustomLogging(
PackageGUID    CHAR(38),
ExecGUID       CHAR(38),
TaskName       VARCHAR(25),
RowsAffected   INT,
LogDate       DATETIME);
GO

     With the logging table created it’s now time to configure your package, which we’ll start by creating a variable called RowCount:

image      I’ll use a simple, albeit somewhat pointless, data flow task to demonstrate populating the variable and logging its information.  The below illustration shows the OLEDB data source of my Pointless data flow task:

image      In order to be able to capture the rows affected from this source to the destination add a row count task and configure it to use the RowCount variable:

image Now all that is needed is a destination, in my case I will just use a flat file destination.

image

     I want to capture the affected rows immediately on completion of the data flow task so I will use the OnPostExecute event handler to log the information to my CustomLog table using an execute SQL task.  The execute SQL task will use my OLEDB data source to AdventureWorks2012 and will us the query below:

image      Anyone familiar with T-SQL will stare questioningly at this INSERT statement pondering how this is exactly supposed to work.  The magic of this is that the “?”’s act as placeholders for the variables that will be configured as parameters, to get more information on using variables as parameters you can view this post.  The Parameter Mapping pane of the execute SQL task provides the ability to map the values from the system and user variables to the ordinal position of each one of the question marks.  The variables don’t have to necessarily be created in the order in which the values appear in the query, but the Parameter Name must specify the 0 based index value of the ordinal position in which the value is to be placed.  In this example I am going to use a mixture of system and user variables that will relate to the table columns:

System::PackageID   = PackageGUID

System::ExecutionID = ExecutionGUID

System::SourceName = TaskName

User::RowCount = RowCount

System::ContainerStartTime = LogDate

The diagram below shows how these values should be configured.

image      Once the execute SQL task is complete you are ready to run the package.  After the package is complete you can query the CustomLogging table and insure that the results were properly logged.

SELECT *
FROM CustomLogging;
GO

Results {0270D6BB-FCC8-43C4-BBF4-CAB5DE031350}    {9A8AE9FA-F2FA-41D2-B640-335D787D76B4}    PointlessFlow    19972    1900-01-01 00:00:00.000

     This above example displayed how to configure custom logging for data flows to capture information that is not included in SSIS native log providers.  Obviously there may be times when execute SQL tasks are used rather than a data flow and there is still the ability to record affected rows, but rather than a row count an output parameter will have to be used.

     The below illustrates a query in an execute SQL task that can be used to assign the @@ROWCOUNT to the RowCount variable.

 image The only thing left to do is configure the RowCount variable as an output parameter in the execute SQL task

image      To log the results follow the same steps above configuring the OnPostExecute event handler of the execute SQL task.

     The sample package for this post can be downloaded from my site here.

Package Storage: MSDB, Folder, or Package Store?

sqlsafety 2 Comments

First Published 11/2010

SSIS packages can be stored in three different places, the msdb database, the “package store”, or the file/folder system.  The confusion between these options is typically struggling with the difference between the “package store” and the folder.

 

Storing packages in the msdb database is rather straight forward as the packages, and meta-data, is stored in the system table(s) of the database.  When storing packages in the file/folder system and/or the “package store” the lines begin to fade.  The package store, by default, is located at C:\Program Files\Microsoft SQL Server\100\DTS\Packages for both SQL 08 and 08R2.  The truth is that this location, is by definition, a part of the file/folder system, but it differs in the fact that SSIS is configured to monitor this location for packages.  SSIS is made aware of this location through the MsDtsSrvr.ini.xml configuration file which is located, in a default installation, at C:\Program Files\Microsoft SQL Server\100\DTS\Binn.  Examination of the file reveals two Folder elements, one for SqlServerFolder and the other is a FileSystemFolder.  The SqlServerFolder points to the msdb database, while the file system elements is named “File System and has a StorePath of“..\Packages”.

In order to demonstrate the difference from a folder and the package store you can deploy a package to a folder system, other then C:\Program Files\Microsoft SQL Server\100\DTS\Packages.  After deploying the package connect to SSIS with SQL server management studio and view Stored Packages>File System and you can see that there are no packages that are showing installed.

image

Now use an xml editor or notepad to open the  MsDtsSrvr.ini.xml and change the “..\Packages” value to “C:\Packages”, this folder must be present before making the change.  Save the file and restart the SSIS service, this must be done as the new values will be loaded into the registry upon restart.  From SSMS reconnect to the instance of integration services and refresh the Stored Packages>File System.

<Folder xsi:type=”FileSystemFolder”>
<Name>File System</Name>
<StorePath>C:\Packages</StorePath>
</Folder>

image

Modifying the config file also provides the ability to add multiple folders within the package store.  Additional elements can be added like the example below which will allow you to monitor and manage these locations through SSMS.  The below is the entire config file:

<?xml version=”1.0″ encoding=”utf-8″?>
<DtsServiceConfiguration xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”>
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type=”SqlServerFolder”>
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
    <Folder xsi:type=”FileSystemFolder”>
     
<Name>E Drive</Name>
     
<StorePath>E:\Packages</StorePath>
   
</Folder>
   
<Folder xsi:type=”FileSystemFolder”>
     
<Name>File System</Name>
     
<StorePath>..\Packages</StorePath>
    </Folder>
  </TopLevelFolders>
</DtsServiceConfiguration>

image

Harsh Shah has an excellent post on his blog here that provides excellent information on adding a root level folder.

The modification of the config file in relation to clustering is outlined here on Microsoft support.

Modifying the config file to connect to a named instance of SSIS is outlined on my blog here:

http://sqlsafety.blogspot.com/2009/11/connecting-to-named-instance-of-ssis.html

SSIS – Replace Dynamic SQL With Variables

sqlsafety 3 Comments

     I was recently introduced to a number of SSIS packages containing execute SQL and data flow tasks, all of which were using dynamic SQL.  After reviewing the tasks I found that many could, and should, be rewritten using an SSIS variable.  The focus of this post will be on replacing dynamic SQL in SSIS with variables and is not meant to address the good, bad, or ugly of dynamic SQL.  If you want to evaluate the pros and cons of dynamic SQL PLEASE review Erland Sommarskog article that will answer any question you may have on this topic here.

     Without diving into what can become a heated debate over the use of dynamic SQL I will just outline several reasons for replacing dynamic SQL with an SSIS variable.

1. First and foremost IT IS MUCH EASIER to troubleshoot and debug statements passed as variables than those using dynamic SQL

     a. Dynamic SQL statements are very difficult to troubleshoot as the statement is only fully visible at runtime

     b. Statements passed using SSIS variables are available during debugging and can displayed using techniques such as a script task message box or viewed in the Locals window during a breakpoint

2. SSIS statement variables provide all of the benefits of creating an ad-hoc dynamic query without the limitations of dynamic SQL

This post will focus on, and support, these benefits, as well as pointing out the limitations of SSIS variables.

     Let’s first focus on why dynamic SQL or SSIS variables are required.  One of the most common reasons for using dynamic SQL is that the column(s) and/or table(s) need to be dynamically defined at runtime. T-SQL does not support using variables for these types of queries.  The below example illustrates a query that would require using dynamic SQL since in its current form would result in an error:

DECLARE @table VARCHAR(50) = ‘Person.Person’
SELECT *
FROM @table;
GO Results Msg 1087, Level 16, State 1, Line 5 Must declare the table variable “@table”.

To successfully execute this statement dynamic SQL can be used as displayed below using both EXEC and sp_executesql:DECLARE @table VARCHAR(50) = ‘Person.Person’
DECLARE @cmd AS NVARCHAR(4000)
SET @cmd = ‘SELECT * FROM ‘ + @table
EXEC (@cmd);
EXEC sp_executesql @cmd;
GO

The above sample shows an overly simplistic, but quite common, example of dynamic SQL.  SSIS provides the means of replacing dynamic SQL with variables that provide the same functionality, but are far easier to troubleshoot. 

     To demonstrate using variables for dynamic statements create a package and two variables: Statement  Data Type String

TableName Data Type:String Value:Person.Person

image

Click the ellipse button under the Expression column for the Statement variable and enter the following expression:

“SELECT FirstName, MiddleName, LastName FROM  ” +  @[User::TableName]

image      Add a data flow task to the control flow tab and within the data flow add a OLEDB data source and configure the source data access mode to SQL command from variable:

image

With the statement defined and the data source configured to use it you need only add a destination to send the query results.

image

       This seems simple enough, and in fact a bit too easy.  The point of using a variable as a statement is to be able to dynamically create a query, which the above sample is somewhat lacking.  To better demonstrate a dynamically generated statement add an execute SQL task to the control flow pane and set the Result Set to Single row and use the query below for the SQLStatement value:

SELECT SCHEMA_NAME(schema_id) + '.' + name AS name
FROM sys.tables
WHERE SCHEMA_NAME(schema_id) = 'Person'
AND OBJECT_NAME(OBJECT_ID) = 'Person'

image

In order for the TableName variable to be assigned the value from the query configure the Result Set property to assign the output value:

imageAt run time the first execute SQL task will populate the value for the TableName variable which will in turn be used in the Statement variable that is used in the data source task.

image

     Using an SSIS variable as a statement provides the ability to dynamically generate a statement without using dynamic SQL and is far easier to troubleshoot.  Unlike dynamic SQL SSIS variable values can be viewed during debugging of a package allowing you to see the statement that will be used in either an execute SQL or data flow task:

imageTo keep true to my word I wil mention one limitation of using a variable as a statement is that there is a maximum of 4,000 characters in an expression, which can make it a bit trying when working with a rather large and complex dynamic statement.  Please keep in mind that this limitation is for expressions and not for string variables, which means that it is possible to create multiple string variables, assign their values in each expression, and then use a variable to concatenate all of the variables together.  The below illustrates where 4 different variables were created all defining an expression of up to 4,000 characters.  Finally a variable called FullStatement concatenates each one of the variables together. 

image

Kind of a pain and can definitely make using variables as a value a bit more complex, but it works all the same.

     The sample package referenced in this post can be downloaded from my site here.

Post SQL Saturday Costa Rica, Almost The Truth

sqlsafety 3 Comments

I was fortunate enough to spend 36 hours of the past weekend participating in SQL Saturday #189 in San Jose Costa Rica.  This was the first SQL Saturday that I have ever been able to attend from beginning to end completely uninterrupted and I can’t wait to do it again next year.

My adventure began Friday at the airport in San Jose Costa Rica where Andy Leonard, Frank Quintana and I were met and driven to the hotel, or maybe Andy and Frank were met at the airport and I yelled, “WAIT!!!  I’m with them!!”  Let’s not be technical in these events!  After all this is my story!

After checking into the hotel Andy and I walked through the Central Market, El Mercado Central, to kill time and find some local delicacies.  After some time roaming the market we located a very quaint native eatery called Pizza Hut.  As we went to order Andy asked me if I spoke Spanish to which I answered, “Por supuesto”.  I ordered for the both of us and after completing the order in flawless native Spanish the cashier, after overhearing our conversation stated, “¿Ud habla Espnaol?  ¡Creo que no!”  WHATEVER!!  Again my story, and not important since we got our food and had a great time discussing SQL, SSIS, and Costa Rica.

Andy and I got back to the hotel just in time to get ready to go to the speaker’s dinner.  I had just enough time to take a shower and change into fresh clothes, translation…  I retied my shoes, before heading down to go to dinner.  We all met in the lobby and I met with my team, Chalk 1, who would be riding together to go to the restaurant.

Chalk1

I was pretty relaxed and tired figuring that this should be a tranquil evening of fellowship and food.  After climbing into our SUV Russ Fustino looks back and says, “I bet we roll this b3&$#!!”  REALLY!!  C’MON MAN!!  I half laughed and then got a good look at how Russ was dressed and about freaked!

Russ

HOLY CRAP!!!  I had a man in a crash helmet talking about rolling an SUV while driving up a freaking mountain with pot holes which are properly defined in the US as craters!!  I regained my self-control and realized that I was in an SUV with a gentleman name Alex Funkhouser and knew I was SAFE, with a name like that you know it’s good.

Funk

We finally arrived at the restaurant and it was well worth the drive.  The restaurant overlooked the city of San Jose and the only thing that was better than the view was the food and the company.  It was great to meet new people that shared my ineterests…  Great Food!!  I mean SQL Server…

RestaurantView

During dinner I sat next to Kenneth Ureña who filled in every gap that I was missing in the plans and schedule for my next day and a half.  I can honestly say that I have never been so lucky to meet nicer gentleman in my life.  If my weekend would have ended after dinner the highlight would STILL have been meeting and getting to know Kenneth.  I also think that at one point he said that my Spanish made his ears bleed.  ALRIGHT!!  Maybe that’s not true, but it is MY STORY!!

MeandKenneth

After dinner the drive home seemed like it took half the time compared to the drive to the restaurant…  Did you not read about the drive there?!?!?  Up mountains?!?!?  Pot holes like craters?!?!  C’MON!!  Once back at the hotel I begged Chalk 1 to go out, but they all insisted that it was late.

I woke up bright and early and SQL Saturday #189 began!  I spoke at the first two sessions after the opening, which makes it easy because there is no time to get nervous.  I again met up with Andy, Kenneth, Carlos, and others that I met from the night before and had a great morning.  Eduardo Castro did an extraordinary job during the opening of SQL Saturday #189!  The place was packed, but everything went VERY smooth.  I had the first session in salon A and almost immediately after beginning a live band began to play outside the hotel in the court.  I was kinda fired up about this and thought of it as my “theme” music, until I realized that they were playing Back Sabbath.  WOW!!  I was going for more upbeat theme music than that.

OldandFat

The only reason I am sharing this picture is to share what mom said to me after seeing it.  “Well you’ve obviously aged, but at least you’ve gained allot of weight.”  Now don’t EVER question me again about where my brutal honesty comes from!!

I had one more session after the opening and my theme band continued on, although I tried to block them out as I was getting a bit freaked.  I was immediately followed by Andy Leonard, who upon starting his session the theme music stopped, REALLY!!

We broke for lunch and I was able to visit again with Kenny and Andy:

ThreeAmigos

Kenneth and I began to walk around the hotel and while looking down the center of the uper floor saw this sight below:

Waiting for a water balloon

Kenneth looked at me and said, “I have a water balloon!!  Let’s get them!!”  As Kenneth blurted this out Frank Qunitana walked behind us and asked, “Is that really necessary?  Think about this and how it will affect the feelings of others.”

ThinkAboutIt

Frank then broke out into a grin and shrieked, “Just kidding!!!  Let’s Get’em!”, and began laughing and lurched for Kenneth’s water balloons.  THIS IS MY STORY AND I WILL MAKE IT UP AS I PLEASE!!  Thank goodness the pictures support me.

FrankOK

After lunch I atteneded Jeff Prom’s presentation on MDS and DQS.  Jeff did a great job, but I was a bit angry that he also had his own theme music playing outside in the court yard.  I try not to be a jealous person, but was dismayed at the fact that Jeff’s theme music was noticeably better than mine and that the tone, timbre, and pace would change based on his session.  As Jeff began to discuss the limitations of DQS the band changed to a melancholy R&B tune that mirrored the mood of all in the room.  JUST KIDDING!!  Jeff did an outstading job and even though his theme music was better than mine I thoroughly enjoyed it.

So the truth of this entire post…  I attended SQL Saturday #189 in Costa Rica and it was one of the best events I have ever participated in.  I cannot thank Eduardo Castro and the organizers enough for all of their efforts and work for putting on such an event.

Eduardo

Also true is the fact that I got to spend time with Andy Leonard and Kenneth Ureña, as well as the members of Chalk 1.  Andy and I had a great time and Kenneth was awesome, and thanks for the coffee Kenneth!!  LOVING IT!!.  Russ Fustino was AWESOME and even gave me a .NET Gurus Can Cook book.  I want to thank Eduardo, the sponsors, and ALL the volunteers who made this a premiere event!!  I wish I had more pictures and names to share, but you will have to attend next year to truly understand.

So to answer everyone’s questions:

1. Was there really a SQL Saturday Chalk 1?

2. Did Kenneth really have water baloons?

3. Is Alex’s last name really Funkhouser?

I cannot respond to these questions.  Show up next year and find out yourself.

Thank you again organizers and sponsors of SQL Saturday #189!

T-SQL Set Operators INTERSECT, EXCEPT, and UNION

sqlsafety 1 Comment

The T-SQL sample can be downloaded here:
http://www.sqlsafety.com/TSQLCode/SETOperators.sql

INTERSECT, EXCEPT, and UNION are set operators that perform operations between two or more input sets.  UNION has been available in t-sql for some time, while INTERSECT and EXCEPT were introduced in SQL 2005.  All three operators have similar requirements:

  1. Require a minimum of 2 input sets
  2. Each input set must have the same number of columns returned
  3. Each relative column must have compatible data types
  4. The ORDER BY clause can only be used on the final set

I was recently approached by a colleague at a conference asking why my blog posts are not more specific to law enforcement, my full time job.  I explained that public safety is such a small and specific sector that it is difficult to tailor examples towards this area while still making them germane to other areas of business. He requested that I occasionally dedicate post(s) specifically pointed towards public safety. As I began working on the examples for this post I struggled to find meaningful examples using sample databases, AdventureWorks, Nortwhind, etc., that illustrated real life usefulness of all the outlined set operators. I was able to kill two birds with one stone by looking through scripts I use daily that deal directly with public safety.
These examples will utilize 3 tables, incidents, accidents, and arrests.

  1. The incidents table records all reported crimes
    1. An incident can spawn an arrest which is then recorded in the arrests table using the incident’s case number, i.e. a burglary is reported and some days later the suspect is arrested.
    2. An incident cannot spawn an accident
  2. The accidents table records all traffic crashes
    1. An accident can spawn an arrest and is then recorded in the arrests table using the accident’s case number, i.e. a crash caused by a drunk driver.
    2. An accident cannot spawn an incident
  3. The arrests table record all arrests
    1. Arrests can come from incidents or accidents in which case the related incidents/accidents case number is used for the arrest
    2. Arrests can be made without a related incident or accident

A non-clustered index is placed on the case number column of all tables; there can be multiple identical case numbers in each table which makes a clustered index less attractive.
The below statements create and populate the sample tables and indexes:
USE tempdb;
CREATE TABLE incidents(

pkey           INT IDENTITY PRIMARY KEY,

casenumber     CHAR(8),

nature         VARCHAR(35),

reported       DATETIME );

GO
CREATE TABLE accidents( pkey           INT IDENTITY PRIMARY KEY,

casenumber     CHAR(8),

location       VARCHAR(75),

occurred       DATETIME );

GO
CREATE TABLE arrests( pkey           INT IDENTITY PRIMARY KEY,

casenumber     CHAR(8),

primarycharge  VARCHAR(40),

arrested       DATETIME );

GO
INSERT incidents VALUES('1001234',
'Burglary',
'1/2/2010'),
(
'1001244',
'Theft',
'2/5/2010'),
(
'1001264',
'Assault',
'4/6/2010'); GO
INSERT accidents VALUES('1001284',
'123 Main St.',
'11/14/2010'),
(
'1001294',
'754 SW 56th Ave',
'7/9/2010'),
(
'1001304',
'815 Nicholas Pkwy',
'4/6/2010'); GO
INSERT arrests VALUES('1001234',
'Burglary',
'1/2/2010'),
(
'1001254',
'Theft',
'2/5/2010'),
(
'1001264',
'Aggravated Assault',
'4/6/2010'),
(
'1001304',
'DUI Property Damage',
'4/6/2010');
GO
CREATE INDEX inc_casenumber ON incidents(casenumber); GO
CREATE INDEX acc_casenumber ON accidents(casenumber); GO
CREATE INDEX arr_casenumber ON arrests(casenumber); GO

UNION

As UNION was introduced to t-sql first let’s start there. A common request that would be ideal for the UNION operator would be to write a query that returns all reports from the three tables, incidents, accidents, and arrests. The below query utilizes the UNION operator and returns the reports, but does not fully meet the requirements:
SELECT casenumber,
nature,
reported FROM incidents UNION
SELECT
casenumber,
'Traffic Crash',
occurred FROM accidents UNION
SELECT
casenumber,
primarycharge,
arrested FROM arrests ORDER BY casenumber; GO

The results are as follows:
clip_image002
Observe that all columns in each input set are of similar; if not identical, data types and that the ORDER BY clause is utilized, but only in the last statement. The results returned are almost complete except that a row is missing. The UNION operator, as well as INTERSECT and EXCEPT, only return distinct rows to the final result set, which means that a the above result set is missing a single row. An incident and arrest report with the same case number, nature/primary charge and date has been removed from the result set as the selected columns are identical. In order to assure that this row is included in the result set then UNION ALL could be used, which returns all rows regardless of duplicates. However, in order to return all rows and assure that the results are more insightful a string literal can added:
SELECT casenumber,
'Incident Report ' + nature AS Nature,
reported FROM incidents UNION
SELECT
casenumber,
'Traffic Crash',
occurred FROM accidents UNION
SELECT
casenumber,
'Arrest Report ' + primarycharge AS CHARge,
arrested FROM arrests ORDER BY casenumber

clip_image002[4]
The above results now include all rows and, with the use of the string literal, the Nature column includes the type of report.

INTERSECT

Another common request that is made is how many incidents resulted in arrests.  This can be accomplished a couple of different ways, but as this article is on set operators, let’s look at INTERSECT.  The INTERSECT operator returns all rows that occur between the left and right query so the below query will return the distinct case number, nature/primary charge, and date that occurs in both queries.
SELECT casenumber,
nature,
reported FROM incidents INTERSECT
SELECT
casenumber,
primarycharge AS CHARge,
arrested FROM arrests

A single row is returned:
image
**ANSI SQL outline INTERSECT ALL and EXCEPT ALL, which are similar to UNION ALL in that duplicate rows will be returned, but this is not currently supported in SQL 2008.
Similar results can be returned using an INNER JOIN between the incidents and arrests table.  The below query  returns an extra row, thereby giving a more accurate representation of incidents resulting in arrests:
SELECT i.casenumber,
nature,
reported FROM incidents i JOIN arrests a ON i.casenumber = a.casenumber
image
You will also notice that the estimated execution plan between the two queries are similar, but that the estimated overall query cost for the INTERSECT is greater than that of the INNER JOIN.  The bulk of the query cost for the INSERT query is the SORT (DISTINCT Sort) operator, 63%.
image
By adding DISTINCT to the query utilizing the INNER JOIN then the estimated query costs between the two queries become equal, although the execution plans differ slightly.
image
Results identical to the INTERSECT statement can be returned using an inner join by adding another join criteria on the incidents nature and the arrests primary charge.  The result set is identical, but the additional comparison causes a much greater variance between the two query plans and results in the INTERSECT having a greater over all estimated query cost:
SELECT i.casenumber,
nature,
reported FROM incidents i JOIN arrests a ON i.casenumber = a.casenumber AND i.nature = a.primarycharge

image Another way of retrieving all intersecting incidents and arrests is to use a sub-query using IN:
SELECT casenumber,
nature,
reported FROM incidents WHERE casenumber IN(SELECT DISTINCT casenumber
FROM arrests);

The above query will show an identical estimated query cost compared to using an INNER JOIN although the execution plan differs slightly:
image

EXCEPT

The EXCEPT operator acts as would be expected, contrary to the INTERSECT.  EXCEPT will show all results that appear in the left query that do not exist in the right query.  This can be used to display incidents that do not have an arrest associated with them:
SELECT casenumber,
nature,
reported FROM incidents EXCEPT
SELECT
casenumber,
primarycharge,
arrested FROM arrests; GO

The results are two rows as follows:
image Notice again that this is not truly accurate since the case number 1001264 occurs in the incidents table as an assault and the arrests table reflects the charge as an aggravated assault.  This query can also be converted using an outer join and filtering based on the arrest case number being NULL:
SELECT i.casenumber,
nature,
reported FROM incidents i LEFT JOIN arrests a ON i.casenumber = a.casenumber WHERE a.casenumber IS NULL

image
Again since DISTINCT is place on all columns in the result set of EXCEPT the outer join proves to be a better choice and the estimated query costs and plans are comparable to those documented above for INTERSECT and INNER JOIN.
Just as illustrated above identical results can be achieved using NOT IN in the WHERE clause:
SELECT casenumber,
nature,
reported FROM incidents  WHERE casenumber NOT IN(SELECT casenumber FROM arrests)

Estimated query costs and plans are again comparable to those outlined above for INTERSECT.

SET OPERATOR PRECEDENCE
All 3 set operators can be used together to provide a single result set, but each operator has its own precedence.
  1. INTERSECT
  2. EXCEPT/UNION
    1. Both are treated equally and evaluated based on appearance in order
Consider now how to return the case number and date of all incidents and accidents that are present in the arrests table.  As the INTERSECT operator is evaluated first this is rather easy:
SELECT casenumber,
reported FROM incidents INTERSECT
SELECT
casenumber,
arrested FROM arrests UNION
SELECT
casenumber,
occurred FROM accidents INTERSECT
SELECT
casenumber,
arrested FROM arrests

The above query will first return all case numbers and reported incident dates that occur both within incidents table and the arrests table.  Next all case numbers and accident dates will be returned that are also within the arrests table.  Finally the result sets will be UNION’ed and returned as a single result set:
image Precedence can be specified by placing parenthesis around queries utilizing a set operator.  In the query above the UNION operator can be given precedence over the INTERSECT by placing the two queries utilizing UNION in parenthesis:
SELECT casenumber,
reported FROM incidents INTERSECT (SELECT casenumber,
arrested FROM arrests UNION
SELECT
casenumber,
occurred FROM accidents INTERSECT
SELECT
casenumber,
arrested FROM arrests)

image
In the above query the result set from the UNION is returned, and precedence is given to the UNION over the INTERSECT due to this, the INTERSECT query is then evaluated against the UNION’ed results and as such one less row is returned.
CONCLUSION
INTERSECT, EXCEPT, and UNION are all set operators that act on a minimum of two queries.  INTERSECT and EXCEPT can be re-written in several different ways, using joins or utilizing IN/NOT IN within the WHERE clause.  The result set returned by all three operators assures distinctness of each row by default and only UNION supports ANSI defined ALL, the book T-SQL Fundamentals by Itzik Ben Gan outlines ways of mimicking ANSI INTERSECT ALL and EXCEPT ALL using t-sql.  MSDN and books online also document these set operators,http://msdn.microsoft.com/en-us/library/ms188055.aspx

SSRS Formatting

sqlsafety 2 Comments

 All formatting options described will be available for download on a .pdf in the next few weeks at the new site.

After working for years with other reporting products I made the move to SSRS and found that one of the more difficult learning curves was to find formatting codes I began with what I thought was a rather simplistic report that contained a simple tabular report with only a few columns. I had no problem with grouping my data, creating the expressions for the footers and headers, or even creating default values for parameters, but hit a wall when I began trying to format my dates and numbers. After finally finding the Format property I was at a loss of what to put in it. The below are the formatting codes that I have compiled since that day:
Date/Time Formatting Codes

 

 

Date Format Codes

 


Description

Example
        d Short date  3/12/2006
       D Long date  Monday, June 1,2006
       t Short time 7:12 PM
       T Long time   6:15:55 AM
       f Long date/short time Monday, June 1,2006 7:12 PM
       F Long date/long time  
       g Short date/short time  3/12/200619:12
       G Short date/long time  3/12/200619:12
       M or m Month and day only 5-Nov
       Y or y Month and year only October, 2002
       Yyyy Four digit year 2008
       yy Two digit year 8
       MMMM Full month name April
       MMM  Three character month Jan
       MM One or two digit month 4
       Dddd Full day name Friday
       Ddd Three character abbrev. Sat
       dd Two digit day always 4
       d One or two digit day  4
       hh Two digit hour always 12 hr clk 8
       h One digit hour 12 hr clk 8
       HH Two digit hour always 24 hr clk 20
       H One or two digit hour 24 hr clk 8
       Mm Two digit minutes  24
       ss Two digit seconds 24
       tt Two character AM or PM  AM
       t One character AM or PM 24 hr clk     A
 
NUMERIC FORMAT CODES

 


Numeric Format Codes

 


Description

 


Example
# Optional place holder                                                           
0 Required number placehoolder  
% Percentage .95 becomes 95%                                                           
C Two digit numbers & local currency char $12,345.95(For US currency)
D or D2 Decimal value, optionally can specify precision 56.95
E or E12 Specific notation, optionally can specify precision 1.23E+08
P or P1 Percentage, optionally can specify precision  87.4
N or N# Numeric, including thousands comma separator prec.Optional 1,000
 

 

Now that you have these formatting codes where exactly do they go?  From within Business Intelligence Development Studio you can right click on the textbox or group and choose Properties and from the properties window select the Format tab :

 
or click on the object and browse to the format property in the Properties pane

and enter the formatting code.

 
 

The results for numeric formatting are:


Format
Value
Formatted Value
N0 10000.00 10,000
N1 10000.00 10,000.0
C 10000.00 $10,000.00
D2 10000.00 10000
P1 10000.00 1,000,000.0%
E 10000.00 1.000000E+004
 
The results for date/time formatting are:

Format
Value
Formatted Value
d 2/3/2010 12:30.000AM 2/3/2010 12:30:00AM
D 2/3/2010 12:30.000AM Wednesday, February03, 2010
t 2/3/2010 12:30.000AM 12:30 AM
T 2/3/2010 12:30.000AM 12:30:00 AM
f 2/3/2010 12:30.000AM Wednesday, February03, 2010 12:30:00 AM
F 2/3/2010 12:30.000AM Wednesday, February03, 2010 12:30 AM
g 2/3/2010 12:30.000AM 2/3/2010 12:30 AM
G 2/3/2010 12:30.000AM 2/3/2010 12:30:00AM
M or m 2/3/2010 12:30.000AM February 03
Y or y 2/3/2010 12:30.000AM February, 2010
Yyyy 2/3/2010 12:30.000AM 2010

This post covered simplistic out of the box formatting and will be followed by more complex formatting of numeric, data/time, and string types, until then you can also refer to MSDN on line documentation:

 

SQL Saturday #189 Costa Rica Countdown

sqlsafety 0 Comments

       Two days to go before SQL Saturday #189 in Costa Rica and I am ready to go!!  ¡No lo puedo creer!  This will be the first SQL Saturday that I will be attending from start to finish with nothing else to do but speak and attend sessions and I am fired up.  I have scoped out the schedule, picked out sessions of interest, and am prepared to relax and enjoy this awesome event.  I found ₡25,000 colones from my last visit to Liberia Costa Rica and have nothing left to do but count down the hours.  

sqlsat189_web


Hit Counter provided by Sign Holders