[ad_1]
Initially it is advisable to learn my earlier article known as “The way to ship SSIS logs (errors) via e mail” because the processes are fairly the identical. Nonetheless, it is advisable to create some modifications within the Execute SQL Job (ref.: part “J” quantity 5 and 12 of “The way to ship SSIS logs (errors) via e mail”). As an Execute SQL Job is used to gather the logs saved in SQL Server it is advisable to be conversant in parameter mapping in Execute SQL process and know the way it works. Assume that we have to simply ship the “Error” logs which might be occurred between “Occasion Handler Begin Time” and the present time for the present bundle execution. As you possibly can think about it’s barely totally different from what we did within the earlier article to e 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 yet one more system variable mapped to a parameter. Please be aware 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 necessary 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 move 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 need 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 listing.
· Parameter identify for an OLEDB connection needs to be numeric begins from “0” (zero).
· The sequence of utilizing parameters in SQL Assertion is necessary.
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) via e mail”) and go to Parameter Mapping.
1. Choose “System::EventHandlerStartTime” from the variable identify drop down listing
2. Course-> Enter
3. Knowledge Sort-> DATE
4. Parameter Title-> 1 (we’ve added “System::ExecutionInstanceGUID” earlier than in the earlier article). Once more be aware that the sequence of parameter names in parameter mappings is necessary right here.
5. Go to “Normal” 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 identify of “0” in parameter mapping listing and the second “?” is pointing to the parameter within the listing that its parameter identify 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 e 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 necessary?
Let’s take a look at what is occurring if the sequence of utilizing the mapped parameters in SQL assertion is just not matched to the sequence of parameter names.
1. Open Execute Job SQL Editor once more and alter the parameter identify as under and click on OK:
2. Don’t change the SQL assertion
3. Run the bundle
4. It appears that evidently the occasion handler emailed one thing to you, so, it is best to obtain an e mail.
5. Check out the e-mail content material. It needs to be one thing like this (be aware to the highlighted half):
<ROOT><?MSSQLError HResult=”0x80040e07″ Supply=”Microsoft SQL Server Native Shopper 11.0″ Description=”Operand sort conflict: datetime2 is incompatible with uniqueidentifier“?></ROOT>
6. Operand sort conflict: datetime2 is incompatible with uniqueidentifier, means that you’ve a knowledge sort 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 Normal-> 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 e mail.
Associated
[ad_2]