First of all you need to read my previous article called “How to send SSIS logs (errors) through email” as the processes are pretty the same. However, you need to create some changes in the Execute SQL Task (ref.: section “J” number 5 and 12 of “How to send SSIS logs (errors) through email”). As an Execute SQL Task is used to collect the logs stored in SQL Server you need to be familiar with parameter mapping in Execute SQL task and know how it works. Assume that we need to just send the “Error” logs that are happened between “Event Handler Start Time” and the current time for the current package execution. As you can imagine it is slightly different from what we did in the previous article to email the SSIS logs to the system administrators. As there was just one system variable that we mapped in parameter mapping section in the Execute SQL Task. But, here we need to have one more system variable mapped to a parameter. Please note that we are using OLEDB connection to connect to the SSIS log database that we created before to store SSIS logs. So there are some important points with OLEDB Connection and Execute SQL Task parameter mapping and its SQL statement.
How it works?
Considering the following notes we are also answering the question “How to pass a system variable to a SQL Statement in an Execute SQL Task”.
· Parameter marker for an OLEDB connection is a question mark “?”. It doesn’t matter if we are querying the first mapped parameter or the second one, so we need to just use a “?” in the SQL statement whenever we want to point to a parameter in the SQL statement and the Execute SQL Task will identify the parameters by their sequence in the parameter mapping list.
· Parameter name for an OLEDB connection should be numeric starts from “0” (zero).
· The sequence of using parameters in SQL Statement is important.
· Click here to get more information about parameters and return codes in the Execute SQL Task
Now, double click on the Execute SQL Task to open “Execute SQL Task Editor” for “Read Logs” (ref.: section “J” from “How to send SSIS logs (errors) through email”) and go to Parameter Mapping.
1. Select “System::EventHandlerStartTime” from the variable name drop down list
2. Direction-> Input
3. Data Type-> DATE
4. Parameter Name-> 1 (we’ve added “System::ExecutionInstanceGUID” before in the previous article). Again note that the sequence of parameter names in parameter mappings is important here.
5. Go to “General” section and put the following SQL code in SQLStatement part:
select *
from [dbo].[sysssislog]
where (executionid = ?) and (starttime between ? and getdate()) and [event] = ‘OnError’
FOR XML AUTO
In the above code the first question mark “?” is pointing to the parameter with the parameter name of “0” in parameter mapping list and the second “?” is pointing to the parameter in the list that its parameter name is “1”. The logs are restricted to error logs only by assigning ‘OnError’ to “Event” column.
6. Click OK twice. Now run the package and you’ll receive an email containing all errors happened between event handler start time and current time for the current package execution.
Why the sequence of using parameters in SQL Statement is important?
Let’s test what is happening if the sequence of using the mapped parameters in SQL statement is not matched to the sequence of parameter names.
1. Open Execute Task SQL Editor again and change the parameter name as below and click OK:
2. Do not change the SQL statement
3. Run the package
4. It seems that the event handler emailed something to you, so, you should receive an email.
5. Take a look at the email content. It should be something like this (note to the highlighted part):
<ROOT><?MSSQLError HResult=”0x80040e07″ Source=”Microsoft SQL Server Native Client 11.0″ Description=”Operand type clash: datetime2 is incompatible with uniqueidentifier“?></ROOT>
6. Operand type clash: datetime2 is incompatible with uniqueidentifier, means that you have a data type mismatching in the SQL statement that maps a date field to a GUID field.
7. Now, open the “Execute SQL Task Editor” again and go to General-> SQLStatement and modify the SQL statement as below:
select *
from [dbo].[sysssislog]
where (starttime between ? and getdate()) and (executionid = ?) and [event] = ‘OnError’
for xml auto
8. Now run the package again.
9. That’s it. You’re receiving the relevant error messages by email.
