none
Troubleshooting SSIS RRS feed

  • Question

  • Hi,

    I am new to SSIS. I am working as a MS SQL production DBA.

    Normally where SSIS error logs stores and what is procedure for troubleshooting. 

    What is the DBA role in SSIS?

    Tuesday, April 8, 2014 12:44 AM

Answers

  • Article on SSIS for DBA-s:

    Administrating SQL Server Integration Services - Planning, Documenting and Troubleshooting

    Beside administration a DBA should know at least how transfer data with the SSIS Import/Export Wizard:

    http://www.sqlusa.com/bestpractices/ssis-wizard/

    The more SSIS you know, the more valuable DBA you are. That translates to dollars as well.


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Tuesday, April 8, 2014 3:01 AM
  • You may want to try looking at msdb sysssislog, however as far as I know, there's no specific standard for error log locations (although SQL2012 greatly improves logging).  Depending on what the SSIS package is doing, there may be additional logging requirements, error handling, notifications, etc.  It's generally best for a company to decide on a standard manner for SSIS logging.  I'd recommend looking at the following BOL articles for information pertaining to SSIS logging:

    Integration Services (SSIS) Logging

    Implementing Logging in Packages 

    Typically you will look at SQL Agent logs to see what jobs/SSIS packages failed.  In many cases, where the problem does not reside in the SSIS package, the SQL Agent output will provide enough information to fully troubleshoot.

    As for the DBA role in SSIS...I would say it varies by company and should generally be established and agreed upon by management.  In my company, we have a data delivery team consisting of DBA's and BI/SQL Developers - the DBA's typically perform an initial review to see if the issue can be resolved by running the package again.  If it's not a quick fix, one of the SQL Developers will generally be assigned the problem.

    Best of luck.

    -D

    Tuesday, April 8, 2014 3:18 AM

All replies

  • Article on SSIS for DBA-s:

    Administrating SQL Server Integration Services - Planning, Documenting and Troubleshooting

    Beside administration a DBA should know at least how transfer data with the SSIS Import/Export Wizard:

    http://www.sqlusa.com/bestpractices/ssis-wizard/

    The more SSIS you know, the more valuable DBA you are. That translates to dollars as well.


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Tuesday, April 8, 2014 3:01 AM
  • You may want to try looking at msdb sysssislog, however as far as I know, there's no specific standard for error log locations (although SQL2012 greatly improves logging).  Depending on what the SSIS package is doing, there may be additional logging requirements, error handling, notifications, etc.  It's generally best for a company to decide on a standard manner for SSIS logging.  I'd recommend looking at the following BOL articles for information pertaining to SSIS logging:

    Integration Services (SSIS) Logging

    Implementing Logging in Packages 

    Typically you will look at SQL Agent logs to see what jobs/SSIS packages failed.  In many cases, where the problem does not reside in the SSIS package, the SQL Agent output will provide enough information to fully troubleshoot.

    As for the DBA role in SSIS...I would say it varies by company and should generally be established and agreed upon by management.  In my company, we have a data delivery team consisting of DBA's and BI/SQL Developers - the DBA's typically perform an initial review to see if the issue can be resolved by running the package again.  If it's not a quick fix, one of the SQL Developers will generally be assigned the problem.

    Best of luck.

    -D

    Tuesday, April 8, 2014 3:18 AM