October 10, 2014 at 8:39 am
Hi,
I have a requirement to parse IIS logs into a database, the IIS logs will be coming from multiple server/directory location.
So far I have come up with the following list of things I think i need to achieve.
a, Create a table to hold the IIS log path locations, (may be 1 location per web server or multiple for multiple sites and will definitely be network paths)
b, Create a SSIS package to perform the following:
1, Get the first IIS log directory path
2, Copy all logs from that path to another directory named "Processing" (local to the server where the logs are created)
3, Use a execute process task to call logparser and pass its some variables (these will probably the same values as point 1 (location oriented))
4, Once the files have been processed move them to another directory named processed (local to the server where the logs are created)
5, Move onto the next log file location and reapeat steps 2-5 until all logs have been processed.
I thought this would be quite simple.... but so far I am struggling with the first 2 steps, and in a total google loop.
Any advise on how the above can be achieved, even if just which SSIS tools/components to get started with will be appreciated.
October 12, 2014 at 9:21 pm
acrutchley (10/10/2014)
Hi,I have a requirement to parse IIS logs into a database, the IIS logs will be coming from multiple server/directory location.
So far I have come up with the following list of things I think i need to achieve.
a, Create a table to hold the IIS log path locations, (may be 1 location per web server or multiple for multiple sites and will definitely be network paths)
b, Create a SSIS package to perform the following:
1, Get the first IIS log directory path
2, Copy all logs from that path to another directory named "Processing" (local to the server where the logs are created)
3, Use a execute process task to call logparser and pass its some variables (these will probably the same values as point 1 (location oriented))
4, Once the files have been processed move them to another directory named processed (local to the server where the logs are created)
5, Move onto the next log file location and reapeat steps 2-5 until all logs have been processed.
I thought this would be quite simple.... but so far I am struggling with the first 2 steps, and in a total google loop.
Any advise on how the above can be achieved, even if just which SSIS tools/components to get started with will be appreciated.
This should get you started. In your SSIS package
- add a new variable of type Object (this will be used to temporarily store a recordset which contains your list of IIS Log paths)
- write a t-sql statement that returns the list of IIS Log paths
- add an Execute SQL task to your SSIS package. This task will execute the t-sql you just wrote
- configure this task so that the recordset it returns is stored in the variable you created earlier (the "Resultset" property shold be "Full result set" and in the the "Result Set" pane of the Execute SQL Task Editor, set assign the result set to your variable
- add a FOREACH Loop container to your package and have it execute after the Execute SQL task
- create another variable of type String. This will be used to temporarily store the each IIS Log Path as the foreach loop iterates through each record)
- configure the FOREACH Loop to process an ADO recordset. To do this, in the Collection pane, set the "Enumerator" to "Foreach ADO Enumerator", set the "ADO Object source variable" to the variable you created and in the "Variable Mappings", assign index 0 to your string variable (this assume that the t-sql statement returns a single column)
- in your connection manager that connects to the IIS Log File, add an expression on the "Connection String" - assign the value in the string variable to it
That should give you the basic loop for all of your logs. You may need to add addition columns/logic to deal with where you copy your log files during processing.
October 13, 2014 at 12:41 am
Many thanks happycat59, I will give this a go today and ley you know how I get on.
Thanks again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply