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.
If 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.
If 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