SSIS Package to Read IISLog

SSIS Package to Read IISLog

You need LogParser 2.2 installed on your machine.

I need iislog every 1 hour so i have set this package to execute every hour and imports second last files to database.
Before you start creating package you have to create one batch file and one table.

Step1 : First of all you have to create batch file with below script and save it to E:\BatchFiles\Process_IISLog.bat

"c:\program files (x86)\log parser 2.2\LogParser.exe" "SELECT s-computername,LogFileName ,rOWnUMBER,S-SITENAME,S-IP,CS-METHOD,CS-URI-STEM,CS-URI-QUERY,S-PORT,CS-USERNAME,C-IP, SC-STATUS,SC-SUBSTATUS, SC-WIN32-STATUS,SC-BYTES,CS-BYTES,TIME-TAKEN INTO TestDB1.dbo.IISLogDetails FROM %1" -i:W3C -o:SQL -server:ExpertsClick -database:testdb1 -username:sa -password:pwd

Step2 : Create table to store IIS Log.

USE [TestDB1]
GO
/****** Object: Table [dbo].[IISLogDetails] Script Date: 07-02-2013 21:20:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[IISLogDetails](
[HostName] [varchar](25) NULL,
[LogFileName] [varchar](150) NULL,
[RowNumber] [int] NULL,
[LogFolder] [varchar](25) NULL,
[IP] [varchar](25) NULL,
[HTTP_Method] [varchar](5) NULL,
[WebURL] [varchar](100) NULL,
[QueryString] [varchar](250) NULL,
[Port] [int] NULL,
[UserName] [varchar](25) NULL,
[ClientIP] [varchar](25) NULL,
[StatusNumber] [int] NULL,
[SubStatus] [int] NULL,
[Win32Status] [int] NULL,
[SCBytes] [int] NULL,
[CSBytes] [int] NULL,
[TimeTaken] [int] NULL
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

Step3 : Create new integration service project with name “ReadIISLog”.

Step4 : Drag Foreeach Loop Container from toolbox.

Step5 : Drag script task under Foreeach Loop Container.

Step6 : Create two variables named “LogFileName” and “MainLogPath” with data type string.

5

Step7 : assign path to MainLogPath variable for iislog.
default path is “C:\WINDOWS\system32\LogFiles\W3SVC456758617”

Step8 : Right click on Foreach Loop Container and click on Edit.

1

Step9 : Click on Expressions button and select Directory in Property column and drag MainLogPath variable in Expression.

2

Step10 : Click OK on Property Expressions Editor.

Step11 : Select Variable Mapping in left penal of Foreeach Loop Editor and choose User::LogFileName in Variable and 0 as Index.

3

Step12: Select Script task editor, right click on it and click on Edit. Select User::LogFileName as ReadOnlyVariables.

4

Step13: Click on Edit Script and Paste Below Code.

.
/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Text;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_42efca1c007548f2bf3c1dfc977efd42.csproj
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{ <code>
 Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,</code>
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public void Main()
{
try
{
string LogFileName = Dts.Variables["User::LogFileName"].Value.ToString();
FileInfo LogFile = new FileInfo(LogFileName);
DateTime LogFileDate = LogFile.LastWriteTime;
TimeSpan timespan = (DateTime.Now - LogFileDate);
if (timespan.Hours >= 1)
{
//From Command Line.
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.StartInfo.FileName = "E:\\BatchFiles\\Process_IISLog.bat";
proc.StartInfo.Arguments = LogFile.FullName.ToString();
proc.StartInfo.RedirectStandardError = false;
proc.StartInfo.RedirectStandardOutput = false;
proc.StartInfo.UseShellExecute = false;
proc.Start();
proc.WaitForExit();
//Move Imported file to oldlog folder.
File.Move(LogFileName, LogFile.DirectoryName.ToString() + "\\oldLog\\" + LogFile.Name.ToString());
}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
MessageBox.Show("Error : " + ex.Message.ToString());
}
}
}
}

Regards,
Nirav Gajjar