Saturday, October 27, 2012

SSIS Logging

In this article I'm writting about Logging features in SQL Server Integration Services (SSIS).

I'hv been using SSIS since last 6-7 years and I have had to implement Logging Mechanism in almost all the projects to track/debug the execution of each and every task/event used in the package. Some of you may say its pretty easy to implement - still I guess we need to take care of logging because some of the things are not so easy.

Integration Services includes logging features that write log entries whenever run-time events occur but the good thing is that it can also write custom messages. Also SSIS supports a diverse set of log providers that can write log entries to: Text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files.

My favourite log is SQL Sevrer because using sql table I can write my own queries to find logging info related to specific task/event or any failure easily.

Basically SSIS logs are associated with packages and are configured at the package level. The task(s)/container(s) in a package can be enabled for logging even if the package itself is not e.g enable logging on an Execute SQL task without enabling logging on the parent package. Also package/container/task can write to multiple logs. You can enable logging on the package only, however you can choose to enable logging on any individual task/container.

You can select a level of logging as per your requirement by specifying the events to log and the information to log for each event, however some events provide more useful information than others.

I'll explain Logging using SQL Server 2012.
Note: SSIS 2005 uses table sysDtsLog90 for logging and SSIS 2008 & SSIS 2012 use sysSSISLog table for logging.

Below is the description of each element in the log schema:
ElementDescription
ComputerThe name of the computer on which the log event occurred.
OperatorThe identity of the user who executed the package.
SourceThe name of the container or task in which the log event occurred.
SourceIDThe unique identifier of the package;
the For Loop, Foreach Loop, or Sequence container;
or the task in which the log event occurred.
ExecutionIDThe GUID of the package execution instance.
StartTimeThe time at which the container or task starts to run.
EndTimeThe time at which the container or task stops running.
DataCodeAn integer value from the DTSExecResult enumeration
that indicates the result of running task:
  • 0 - Success
  • 1 - Failure
  • 2 - Completed
  • 3 - Canceled
DataBytesA byte array specific to the log entry.
The meaning of this field varies by log entry.
MessageA message associated with the log entry.

The following table describes the predefined events that can be enabled to write log entries when run-time events occur:
EventsDescription
OnErrorWrites a log entry when an error occurs.
OnExecStatusChangedWrites a log entry when the execution status of the executable changes.
OnInformationWrites a log entry during the validation and execution of an executable to report information.
OnPostExecuteWrites a log entry immediately after the executable has finished running.
OnPostValidateWrites a log entry when the validation of the executable finishes.
OnPreExecuteWrites a log entry immediately before the executable runs.
OnPreValidateWrites a log entry when the validation of the executable starts.
OnProgressWrites a log entry when measurable progress is made by the executable.
OnQueryCancelWrites a log entry at any juncture in the task processing where it is feasible to cancel execution.
OnTaskFailedWrites a log entry when a task fails.
OnVariableValueChangedWrites a log entry when the value of a variable changes.
OnWarningWrites a log entry when a warning occurs.
PipelineComponentTimeFor each data flow component, writes a log entry for each phase of validation and execution. The log entry specifies the processing time for each phase.
DiagnosticWrites a log entry that provides diagnostic information e.g. you can log a message before and after every call to an external data provider.


Implementing Logging in SSIS 2012

To implement the SSIS logging, follow these steps:

Step1. Create a Connection Manager for logging database. Right click on Connection Managers area and click on New OLE DB Connections... as shown below:

Step2. In the "Configure OLE DB Connection Manager" click on Create button. Now select Server Name and database name from Connect a database. I am using (Local) server and Test database. Click on Test Connection button to verify the connection.


Finally click on OK button to save the connection manager. This will create a new connection manage with ServerName.DatabaseName name. In my example, it is (local).Test. You can rename it to appropriate and meaningful name. For instance cmLogging.

Step3. Right click anywhere on the package surface area in the Control Flow to open Configure SSIS Log: Package wizard.  Check the Package folder checkbox (left side) to enable the logging. Under the "Providers and Log" tab, select "SSIS log provider for SQL Server" in Provider type and click on Add button t add this logging for the package. Finally, click on Configuration to select the logging connection manager. Select cmLogging as connection manager.


Step4. Select logging events.
To prevent log files from using large amounts of disk space, which could degrade performance, you can limit logging by selecting specific events and information items to log. For example, you can configure a log to capture only the date and the computer name along with error message. It is not a good idea to log all the events. Under "Details" tab, select required events for logging:


Step5. Click on Advance button to set advance properties of the logging events.

Finally click on the OK button to save logging configuration.

Step6. Now you can validate your logging information by executing the SSIS package. To get more information about hoe to execute SSIS package, click Different ways to Execute SSIS packages. My package looks like the picture shown below:
You can see the log in sysSSISLog table after executing the package.

SELECT * FROM Test.dbo.sysssislog




 

Monday, October 1, 2012

How to find missing Identity/Sequence numbers using T-SQL?


SQL Developers, did you ever come across a situation where you need to find missing identity or sequence number for a given table? For instance, someone deleted few records from a table which has an IDENTITY column. Have you wondered how to find those missing rows? In this blogpost, I am going to explain different ways to find missing identity or sequence numbers.

First I will create some sample data for demonstration purpose.
-- Prepare Test data --
SET NOCOUNT ON
IF OBJECT_ID('dbo.TestData') IS NOT NULL    DROP TABLE dbo.TestData

CREATE TABLE dbo.TestData (
    ID INT IDENTITY(1, 1) NOT NULL
)

DECLARE @Counter INT = 1
WHILE @Counter <= 15
BEGIN
    INSERT dbo.TestData DEFAULT VALUES
    SET @Counter += 1
END
 

SELECT ID FROM dbo.TestData
 
 
 
 
  
Now lets delete some rows manualy:
-- Now delete some records (IDs)
DELETE dbo.TestData WHERE ID IN (3,7,8,10,12,13)
 
--Verifiy the data
SELECT ID FROM dbo.TestData

 
Below are three different ways to identity missing values.
 
Find missing sequence numbers using Ranking Function:
-------------------------------------------
-- Option 1: Using Ranking function
-------------------------------------------
DECLARE @MaxID INT = (SELECT MAX(ID) FROM dbo.TestData)

SELECT SeqID AS MissingSeqID
FROM (SELECT ROW_NUMBER() OVER (ORDER BY column_id) SeqID from sys.columns) LkUp
LEFT JOIN dbo.TestData t ON t.ID = LkUp.SeqID
WHERE t.ID isnull and SeqID < @MaxID

Here is the output:
 
 -- If there are less records in sys.columns and
-- you need need larger result then use CROSS JOIN
SELECT SeqID AS MissingSeqID
FROM (SELECT ROW_NUMBER() OVER (ORDER BY c1.column_id) SeqID
FROM sys.columns c1
CROSS JOIN sys.columns c2) LkUp
LEFT JOIN dbo.TestData t ON t.ID = LkUp.SeqID
WHERE t.ID isnull and SeqID < @MaxID
 

Find missing sequence numbers using CTE:
-------------------------------------------
-- Option 2: Using Common Table Expression
-------------------------------------------
DECLARE @MaxID INT = (SELECT MAX(ID) FROM dbo.TestData)
;WITH CTE (MissingSeqID, MaxID)
AS (
    SELECT 1 AS MissingSeqID, @MaxID
    UNION ALL
    SELECT MissingSeqID + 1, MaxID FROM CTE WHERE MissingSeqID < MaxID
    )
SELECT MissingSeqID FROM CTE
LEFT JOIN dbo.TestData t on t.ID = CTE.MissingSeqID
WHERE t.ID isNULL
GO
 
 
Find missing sequence numbers using Tally Table:
This is the most prefered way out of all the above options.
-------------------------------------------
-- Option 3: Using Tally Table
-------------------------------------------
DECLARE @MaxID INT = (SELECT MAX(ID) FROM dbo.TestData)
SELECT t.ID  MissingSeqID FROM dbo.Tally t
LEFT JOIN dbo.TestData td
ON td.ID = t.ID
WHERE td.ID IS NULL
AND t.ID <@MaxID