SSIS Logging to SQL Server

Question

I am a novice to SSIS. I have created a package with few Sequence Containers and few Tasks within them. The package will be scheduled to run automatically and thus I want capture the run time errors and status like number of rows inserted/updated/deleted to my own logging table in the SQL database.

I was thinking of using the SSIS Logging to SQL Server feature to log the entries to the default tables and then create a SQL Task to insert them into my own logging tables. Is that a good way to do it? Or do you suggest other better ways?

Also I couldnot get the SSIS logging to work properly. I found an online blog How to create an error log or custom error log within an SSIS Package? that describes the process to use SSIS logging to log entries to the SQL Server. However, unlike explained in the blog,

I cannot see either the dbo.sysssislog or dbo.sp_ssis_addlogentry tables created in my SQL Server database. What must I be doing wrong? Here are the steps I followed -

1. Went to SSIS Logging
2. Selected Provider Type as SQL Server
3. Added the OLEDB connection for Configuration
4. Went to the Detail tabs on each sequence container and chose appropriate events
5. Clicked "Save..."
6. Provided the location and name for the XML file
7. Saved the package and ran it. Still cannot see those ssis table or procedure in the database. I am using SQL 2008R2.

Any suggestions and recommendations greatly appreciated.

Thanks in advance.

Answer 1

Look at #3 in your list. Make a note of the location of that database.

Navigate to that database in SSMS. Expand the database (click the plus sign to the left).

For the table location:

Expand the Tables folder. Expand the System Tables folder. The table should be visible there. Its name is dbo.sysssislog.

For the procedure location:

Expand the Programmability folder. Expand the System Stored Procedures folder. The stored procedure should be visible. Its name is dbo.sp_ssis_addlogentry.

Answer 2

Re. visibility of the sysssislog sys table in the user DB (inside the system tables folder). I have set up the logging for SSIS package (2008 R2) with "Writes log entries for events to a SQL Server database", specifying user DB. After that I ran the SSIS package. I have found sysssislog in the system tables folder (and it contains the expected data). After that I decided to test that this table is "created on each run of the SSIS package". I DROPPED this table. I have run SSIS package AGAIN. The table sysssislog really WAS created (in user table), and contains the correct data, OK.

However I could NOT see it ever since in the System tables in the SSMS. In other words, the table exists (e.g. exists in sysobjects, systables etc), but not shown in the list in the SSMS object object explorer. I understand that this is not SSIS bug (SSIS really re-creates this table on each run), but it is SSMS bug (it somehow looses the metadata for this table).

I dropped this table several times and ran SSIS package again, it is really re-created and is re-populated for the data from the last SSIS package run each time.

But I never was able to see it in the object browser (certainly I have refreshed it at any level, closed/reopened SSMS - this table for any reasons never showed up). I AM able to see this system table in the MSDB db (but it is empty).

I repeated creation of the logging for the different DB (creating new connection for a different DB). The SSIS run, the sysssislog table IS created and can be SEEN in the system tables (however, once again, if I will drop this table, it will NOT be see on re-creation in the object browser ever again).

just wanted to share, interesting bug. This was never tested by MS QA (notification of the table creation in SSMS from SSIS on second and subsequent instance of this table creation).

Alexei