To begin with it’s worthwhile to learn my earlier article known as “The way to ship SSIS logs (errors) by electronic mail” because the processes are fairly the identical. Nonetheless, it’s worthwhile to create some adjustments within the Execute SQL Job (ref.: part “J” quantity 5 and 12 of “The way to ship SSIS logs (errors) by electronic mail”). As an Execute SQL Job is used to gather the logs saved in SQL Server it’s worthwhile to be accustomed to parameter mapping in Execute SQL job and know the way it works. Assume that we have to simply ship the “Error” logs which can be occurred between “Occasion Handler Begin Time” and the present time for the present bundle execution. As you may think about it’s barely completely different from what we did within the earlier article to electronic mail the SSIS logs to the system directors. As there was only one system variable that we mapped in parameter mapping part within the Execute SQL Job. However, right here we have to have another system variable mapped to a parameter. Please word that we’re utilizing OLEDB connection to connect with the SSIS log database that we created earlier than to retailer SSIS logs. So there are some vital factors with OLEDB Connection and Execute SQL Job parameter mapping and its SQL assertion.
The way it works?
Contemplating the next notes we’re additionally answering the query “The way to cross a system variable to a SQL Assertion in an Execute SQL Job”.
· Parameter marker for an OLEDB connection is a query mark “?”. It doesn’t matter if we’re querying the primary mapped parameter or the second, so we have to simply use a “?” within the SQL assertion each time we wish to level to a parameter within the SQL assertion and the Execute SQL Job will establish the parameters by their sequence within the parameter mapping checklist.
· Parameter title for an OLEDB connection ought to be numeric begins from “0” (zero).
· The sequence of utilizing parameters in SQL Assertion is vital.
Now, double click on on the Execute SQL Job to open “Execute SQL Job Editor” for “Learn Logs” (ref.: part “J” from “The way to ship SSIS logs (errors) by electronic mail”) and go to Parameter Mapping.
1. Choose “System::EventHandlerStartTime” from the variable title drop down checklist
2. Path-> Enter
3. Knowledge Sort-> DATE
4. Parameter Title-> 1 (we’ve added “System::ExecutionInstanceGUID” earlier than in the earlier article). Once more word that the sequence of parameter names in parameter mappings is vital right here.
5. Go to “Basic” part and put the next SQL code in SQLStatement half:
choose *
from [dbo].[sysssislog]
the place (executionid = ?) and (starttime between ? and getdate()) and [event] = ‘OnError’
FOR XML AUTO
Within the above code the primary query mark “?” is pointing to the parameter with the parameter title of “0” in parameter mapping checklist and the second “?” is pointing to the parameter within the checklist that its parameter title is “1”. The logs are restricted to error logs solely by assigning ‘OnError’ to “Occasion” column.
6. Click on OK twice. Now run the bundle and also you’ll obtain an electronic mail containing all errors occurred between occasion handler begin time and present time for the present bundle execution.
Why the sequence of utilizing parameters in SQL Assertion is vital?
Let’s take a look at what is occurring if the sequence of utilizing the mapped parameters in SQL assertion will not be matched to the sequence of parameter names.
1. Open Execute Job SQL Editor once more and alter the parameter title as under and click on OK:
2. Don’t change the SQL assertion
3. Run the bundle
4. Evidently the occasion handler emailed one thing to you, so, it is best to obtain an electronic mail.
5. Check out the e-mail content material. It ought to be one thing like this (word to the highlighted half):
<ROOT><?MSSQLError HResult=”0x80040e07″ Supply=”Microsoft SQL Server Native Consumer 11.0″ Description=”Operand kind conflict: datetime2 is incompatible with uniqueidentifier“?></ROOT>
6. Operand kind conflict: datetime2 is incompatible with uniqueidentifier, means that you’ve got an information kind mismatching within the SQL assertion that maps a date discipline to a GUID discipline.
7. Now, open the “Execute SQL Job Editor” once more and go to Basic-> SQLStatement and modify the SQL assertion as under:
choose *
from [dbo].[sysssislog]
the place (starttime between ? and getdate()) and (executionid = ?) and [event] = ‘OnError’
for xml auto
8. Now run the bundle once more.
9. That’s it. You’re receiving the related error messages by electronic mail.
