sql server - How can I parse Serv-U FTP logs with SSIS? -


Some time back, I needed to parse a group of Service-U FTP log files and store them in the database so that people could report. On them. I am developing a small App to do the following:

  1. View all the files in a DIR that are not loaded in DB (one of the previously filled files Table).

  2. Open a file and load all the rows in one list.

  4. Use RegEx to identify the loop and line type (connect, login, disconnect, upload, download, etc.) through that list, Paste this into a particular type of object for the type of line and add the OBG to another list.

  5. Write each of the loops and related database tables through each separate object list.

  6. Record that the file was imported successfully.

  7. Wash, rinse, repeat.

It's ugly, but we've been working for the time limit.

The problem is that I am in DBA role and I am not happy with running a compiled app. As a solution to this problem, I want to make something more open and more DBA oriented.

I can rewrite it in PowerShell, but I would like to develop an SSIS package. I could not find a good way to divide the input based on the RegEx within the SSIS for the first time and I used to SSIS Was not quite familiar with. I'm digging into more SSIS now, but I'm still not finding what I need.

Do anyone have any suggestions on how I can do a rewrite in SSIS?

I have to do something similar to the Exchange log I have yet to use all SSIS solutions The easiest solution is to get it I have said, what do I do here:

First of all I use logparser from Microsoft and I use the bulk copy functionality of sql2005

I Copy the log files into a directory that I can work with Not inside.

I created a SQL file that will parse the log. It looks like this: 

  SELECT TO_Timestamp (REPLACE_STR (STRCAT (STRCAT (date, ''), time), 'GMT', '', 'YYY-MD H: M: [Server-IP], [recipient-address], [event-id], [MSGID], [customer-IP], [client-hostname], [partner-name], [server-hostname]] [priority] [Recipient-report-status], [total-bytes], [number-recipient], TO_Timestamp (REPLACE_STR ([genre-time], 'GMT', '', 'YYY-MD H: M' S ') As [the time of origin], the anchor ',% Infile%' of '% outfile%' WHERE [Event-ID] IN (1027; 1028), [Service-version], [Linked-MSGID], [Message-subject], [sender-address] / Code>  
 Then I run the previous SQL with loggers:  
  logparser.exe file: c: \ exchange \ Info \ name_of_file_goes_here.sql? Infile = c : \ Exchange \ info \ logs \ * log + outfile = c: \ exchange \ info \ logs \ name_of_file_goes_here.bcp -i: W3C -o: TSV  
 
 Which output BCP file . 
 
 Then I bulk that copy file in the SQL Server premade database table Copy this command to: 
 
  databasename.dbo.table.table in the b.c p database: \ exchange \ info \ logs \ name_of_file_goes_here.bcp -c -t "\ t" -T -F 2 -S server \ example -U userid -P password  
 
 Then I run questions against the table. If you can understand how to automate with SSIS, then I would love to hear what you did.

Comments

Post a Comment