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.
First of all I want to explain the process of sending SSIS Logs through email for better understanding. As you might know you can save SSIS logs in several ways for administration purposes. In this article our focus is on how to send SSIS logs to the administrator whenever an event like a package failure is occurred. Just assume that we have several SSIS packages and we are storing the logs in a SQL Server database. Almost all of the packages are running over night. Now, what if a package or some packages failed? Generally one of the most essential activities is that a notification email should be sent to the system administrators to let them know that something’s wrong with the package execution. It could be more helpful to send them the relevant information about the failure. Reading the following lines you can handle this important part of the process in your organisation.
There are some cases that you do not have any documentations for the database that you are working on and you need to investigate the entity relationships and so on. We can understand the database entity relationships better if we can find the number of references to the table or the number of tables’ dependencies. We can do the job easily by combining sys.tables, sys.foreign_keys and/or sys.sysreferences. The following simple T-SQL codes will retrieve tables’ references and tables’ dependencies:
Sometimes you might feel you need to store the database diagrams in a file basis. I’ve faced to this feeling specially when I was testing some databases which were under development. I didn’t like to create the whole database diagrams that I’ve created before in a database that a new version of it is newly released for testing as it is a kind of rework and it is a time consuming process. So I decided to store the database diagrams in a file system basis that I can reuse it again and again. The following solution could be great for sharing the database diagrams in the development team as well by sending them the diagram file by email and/or storing the database diagram file in a network shared folder. Here is a work around that might help you. First of all you should install diagram support in your database. To do so just right click on the database diagrams in object explorer in SQL Server Management Studio and select “Install Diagram Support”.