7

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
edyleddie
  • 71
  • 1
  • 1
  • 2
  • The stored proc and template log table will be copied into the catalog you have selected in the log provider when the package first executes. They currently exist in msdb. – billinkc Jan 04 '13 at 19:44

4 Answers4

11

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.

Siyual
  • 16,415
  • 8
  • 44
  • 58
William Salzman
  • 6,396
  • 2
  • 33
  • 49
  • I can now see the table in the msdb database. But cannot get anything to log into it when I execute the package. I even changed the "configuration" to include the OLE DB connection to the msdb database in the "SSIS Logging" screen's "Provider and Logs" tab. I also have all the events selected for the top level container that flows down to all my sequence containers and task. What mus I not be doing right. Thanks. – edyleddie Jan 09 '13 at 17:09
  • The copies in the MSDB database are just templates. You should be using them in another db. What was your db from #3 before you changed it? That is where your objects will be. Remember that in 2008 & 2008 R2 they will be system object. Not sure about 2012. – William Salzman Jan 09 '13 at 20:00
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

alexei
  • 21
  • 1
2

For logging, I usually grab the information I want via variables and then at the end of the process, I have a stored procedure I execute, which inserts records into a "ProjectLog" table. Not sure if it's ideal, but it works for us.

Reason: *The error messages can be customized, so potentially easier to read and troubleshoot. *We have "OnError" tasks with custom, easy to understand error messages (for example, "Process did not execute. File x missing required data for x column/field". *Can add data that valuable to your group to fulfill business requirements.

*Note, this is an additional step we do, in addition to a text file with error logging that overwrites each day.

Jaji
  • 55
  • 5
  • 1
    I do the same thing - so I won't provide a competing answer. Basically, you have RowCount tasks in the data flow that capture the counts based off of conditional splits. Perhaps, as the answer, you can provide some screenshots and query examples for the OP. – J Weezy Mar 25 '19 at 19:45
1

Even if you have enabled SSIS Logging and In case you are trying to execute a task (without executing the actual package), then the sysssislog table will not be created either in msdb or in mentioned connection manager.

Shyam Mallaya
  • 11
  • 3