Introduction
In the first collection of articles (“SQL Server Integration Services – An Introduction”) of this series, SQL Server Integration Services (SSIS) were discussed at introductory level. Covering topics such as what SSIS is; different ways to create SSIS package; and identification of different kinds of components that constitute a package.
This article will explore advanced features and properties of SSIS such as Event Logging, Event Handling, Transaction Support, Checkpoint Restart and validation process. SSIS package development can be leveraged to make it more robust and improve performance.
Note: where reference is made to “SSIS 2008” it refers to the SSIS version which comes with SQL Server 2008; whereas “SSIS 2005” refers to the SSIS version with SQL Server 2005.
Event Logging
Consider the following scenario: a package has been developed in the development environment, tested it in test environment and finally deployed it onto the production server. In the beginning everything was fine, with the package running as expected. However sometime later the package execution failed each time upon being run. In this situation there is commonly uncertainty regarding what is causing the package to fail. Hence why event logging is very useful. So what is event logging is and how does it help understand problems encountered during runtime.
SSIS package logging is feature which creates a record that traces the execution of components (tasks and containers) within an SSIS package. Logging can be enabled on a per package and per control-flow tasks basis, which all executables should participate in.
The next article in this series explores the Event Handlers in SSIS.
SSIS package logging can log the information on one or more different selected log providers as mentioned below:
- SSIS log provider for Text files – logs the information into a text file, during configuration the location and file name need to be specified.
- SSIS log provider for Windows Event Log – logs the information into the Windows Log Event under Application node and SQLISPackage90/ SQLISPackage100 as source.
- SSIS log provider for XML files – logs the information into the XML file, of which the location and file name are specified during configuration.
- SSIS log provider for SQL Profiler – creates a *.trc file which can be opened in SQL Profiler to analyse further.
- SSIS log provider for SQL Server – logs the information into sysdtslog90/ sysssislog table of msdb database, that requires a connection manager to be provided during configuration.
Example: To enable Logging, right click on the package and select logging. A logging configuration window will pop-up as shown below:
Containers Tree view on Left-side – displays the hierarchical view of a package and all its executables (containers and tasks). Select the executable to enable for logging as shown above.
Providers and Log Tab – Select one or more different log providers from the combo box and click the Add button on the right as shown below, two log providers have been selected; one to write to text file and the other to write to Windows Event Log.
Details Tab – This tab displays all the events of the selected executable, select which events to log. For example, below OnError and OnTaskFailed event have been selected at the package level.
After the package has run the result of logging can be seen; since there are no errors, the log will contain this much information only:
Log information from text file
PackageStart,ARALI-LAPTOP,FAREASTarali,Logging,{9F0475B4-99D5-492F-9D1A-B575E973A6EB},{BC715F16-942A-437C-BD1D-87AFF17FA6C4},12/14/2008 4:30:53 PM,12/14/2008 4:30:53 PM,0,0x,Beginning of package execution.
PackageEnd,ARALI-LAPTOP,FAREASTarali,Logging,{9F0475B4-99D5-492F-9D1A-B575E973A6EB},{BC715F16-942A-437C-BD1D-87AFF17FA6C4},12/14/2008 4:30:57 PM,12/14/2008 4:30:57 PM,0,0x,End of package execution.
Now run the package again and see the result, this time data has been uploaded into a table with duplicate values for a column which has primary key defined on it, here is the log result:
Log information from text file
PackageStart,ARALI-LAPTOP,FAREASTarali,Logging,{9F0475B4-99D5-492F-9D1A-B575E973A6EB},{2C23C3A3-141C-4F51-B98F-4E883CA73254},12/14/2008 4:40:42 PM,12/14/2008 4:40:42 PM,0,0x,Beginning of package execution.
OnError,ARALI-LAPTOP,FAREASTarali,Data Flow Task,{5d83172d-d03a-477b-8120-17ed9723ded3},{2C23C3A3-141C-4F51-B98F-4E883CA73254},12/14/2008 4:40:43 PM,12/14/2008 4:40:43 PM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0” Hresult: 0x80004005 Description: “The statement has been terminated.”.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0” Hresult: 0x80004005 Description: “Violation of PRIMARY KEY constraint ‘PK__vSalesPe__7A591C1805D8E0BE’. Cannot insert duplicate key in object ‘dbo.vSalesPerson’.”.
OnError,ARALI-LAPTOP,FAREASTarali,Logging,{9F0475B4-99D5-492F-9D1A-B575E973A6EB},{2C23C3A3-141C-4F51-B98F-4E883CA73254},12/14/2008 4:40:43 PM,12/14/2008 4:40:43 PM,-1073450974,0x,SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “OLE DB Destination” (79) failed with error code 0xC0209029 while processing input “OLE DB Destination Input” (92). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
OnTaskFailed,ARALI-LAPTOP,FAREASTarali,Data Flow Task,{5d83172d-d03a-477b-8120-17ed9723ded3},{2C23C3A3-141C-4F51-B98F-4E883CA73254},12/14/2008 4:40:43 PM,12/14/2008 4:40:43 PM,0,0x,(null)
PackageEnd,ARALI-LAPTOP,FAREASTarali,Logging,{9F0475B4-99D5-492F-9D1A-B575E973A6EB},{2C23C3A3-141C-4F51-B98F-4E883CA73254},12/14/2008 4:40:43 PM,12/14/2008 4:40:43 PM,1,0x,End of package execution.
Log information from Windows Event Log
An executable can inherit logging settings from its parent or define its own settings as shown below. Please note the Execute SQL Task check box is grayed, it means it will inherit its settings from its parent whereas the Data Flow Task check box is non-grayed, it means it has its own setting defined and will not inherit them from the parent.
There is a property of package and its executables called LoggingMode which accepts three values Enabled, Disabled and UseParentSetting and which is used to control the Logging behavior as discussed above.
Note:
Logging information is quite helpful in troubleshooting the package if it is failing but it puts overhead on SSIS and slows down the package performance. It is recommended to log only required events which will be helpful in package troubleshooting. Logging can be disabled on tasks or packages (using LoggingMode Property dynamically) during normal running and enabled only when debugging or troubleshooting the package is being done.
The next article in the series explores the Event Handlers in SSIS.