locked
Is there a way to read the SQL Server error log using SSIS? RRS feed

  • Question

  • Hello everyone,

    The idea is to check for errors against all SQL Servers on our production evironment and load it to a table where we can create some basic reports off of it.

    Anyone has any experiencing on this the task described above?


    --- Best Regards, Igor Santos Twitter: @sqlsantos Blog: sqlsantos.wordpress.com
    Tuesday, January 3, 2012 7:43 PM

Answers

  • The stored procedure returns a typed dataset, by this BIDS knows the column names and types.
    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    • Proposed as answer by Eileen Zhao Sunday, January 8, 2012 5:08 AM
    • Marked as answer by Eileen Zhao Thursday, January 12, 2012 1:45 AM
    Friday, January 6, 2012 5:23 PM

All replies

  • Hello,

    you could use the (undocumented) stored procedure sp_readerrorlog to get the content of error log; see also http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    • Proposed as answer by Todd McDermid Wednesday, January 4, 2012 12:21 AM
    Tuesday, January 3, 2012 8:00 PM
  • That is a handy system stored procedure :) Thanks for the pointer!
    Jagannathan Santhanam
    Tuesday, January 3, 2012 10:37 PM
  • How do you guys handle pulling data from an SP in SSIS?

    Do you guys use OLEDB source connector to perform this task?


    --- Best Regards, Igor Santos Twitter: @sqlsantos Blog: sqlsantos.wordpress.com
    Thursday, January 5, 2012 5:16 PM
  • Yes, then choose "Data access mode" = "SQL Command" and enter the "EXEC  sp_readerrorlog ...." command
    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Thursday, January 5, 2012 5:53 PM
  • How do you setup the mappings with a stored procedure?

    How does the OLE DB source component indentifies external columns?

    How does the OLE DB source component gets the column information? 


    --- Best Regards, Igor Santos Twitter: @sqlsantos Blog: sqlsantos.wordpress.com
    Thursday, January 5, 2012 7:41 PM
  • The stored procedure returns a typed dataset, by this BIDS knows the column names and types.
    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    • Proposed as answer by Eileen Zhao Sunday, January 8, 2012 5:08 AM
    • Marked as answer by Eileen Zhao Thursday, January 12, 2012 1:45 AM
    Friday, January 6, 2012 5:23 PM