How to Find the Causes of SSIS Package Failure in SQL Agent

In this article, we will discuss how to get list of packages related to SSIS, connection issues in data store, key access problem in files and protection level for package, SSIS source speed and log logics.

SSIS Package FailureIf we do not apply necessary functions in our SSIS package then console outputs or windows events logs will show small number of errors. But if we enable the logging function in SSIS then it is a completely different scenario. In general, we can work with five different places that includes logs from SSIS component, logs from SSIS logging audit, Event log and job histories, logs from the underlying sources of data and audit log.

SQL Server Agent JobIf your SQL Server Agent job is running with SSIS package then first we need to check the errors in event logs of windows and in the history of SQL Agent. We can populate event panel handler with more logics of custom errors handling. On both the control task level and the package level, we can define event handler for errors. For creation of custom events and their handling logics, this feature is most powerful.

In audit logs, initial methods typical gives general errors and if you feel the need of analyzing more information then there is an option given by SQL server to enable the SSIS log audit which output errors in a XML file, event logs of windows, profiler tracer of SQL Server or SQL Server database log. It can be done by accessing the setting and configuring log providers of SSIS.

Packages related to SSIS

There is at times need to get list of packages which are related to SSIS in our SQL Server. For this purpose, we can use following query.

--packages related to SSIS in SQL DB
SELECT 
          DIR.foldername AS Directory-Name
          PKG.name AS Name-Of-Package,
          PKG.[description] AS Package-Description,
          --using switch case to categorize results
          CASE PKG.packagetype
          WHEN 0 THEN ‘Client is default’
          WHEN 1 THEN ‘Input/Output Wizard’
          WHEN 2 THEN ‘Data Transform Service Designer’
          WHEN 3 THEN ‘Replicated’
          WHEN 5 THEN ‘SSIS’
          WHEN 6 THEN ‘Plan for Maintenance’
          ELSE ‘unidentified’
          END AS packagetype,
          GL.name AS Name-Of-Owner,
          PKG.isencrypted AS ‘Encrypter-Or-Not’,
          PKG.createdate AS ‘Date-Created’,
          PKG.vercomments AS ‘Comments-Of-Version’,
          DATALENGTH(PKG.packagedata) AS ‘Size-Of-Package’,
          CONVERT(varchar(25), vermajor)+’.’+
          CONVERT(varchar(25),verminor)+’.’+
          CONVERT(varchar(25),verbuild) AS ‘Package version’

FROM 
          msdb.dbo.sysssispackages as PKG
INNER JOIN
         msdb.dbo.sysssispackagefolders as DIR
ON
         DIR.folderid = PKG.folderid
INNER JOIN
         sys.syslogin AS LG
ON 
         GL.sid = PKG.ownersid
ORDER BY 
         PKG.name
--ordered by names of packages

Custom Log Logics

SQL Server provides custom log logics which can be implemented in script component or script tasks of SSIS. An example would be maintenance of text file using data or values from variable during execution of SSIS package.

If we talk about underlying data sources and their logs, there are some errors which can found in these underlying data sources and to solve them we should drill down into detail by checking logs of errors of respective data source. By default, logs are in ERRORLOG folder under LOG.

SSIS Source Speed

It is to be noted that SSIS source speed is not directly proportional to query time complexity. The speed in which data is returned is what makes an impact on SSIS source speed. Source components are not the source of our data. We should focus on optimizing our queries because that will ultimately tune up the SSIS.

SQL Repair

In the end, we suggest you use of SQL Server fix tool like DataNumen SQL recovery which helps in retaining the lost data due to sudden crash in database.

Author Introduction:

Upton Mark is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and word recovery software products. For more information visit www.datanumen.com

Leave a Reply

Your email address will not be published. Required fields are marked *