title: How can I parse Serv-U FTP logs with SSIS?
body: <p>A while back I needed to parse a bunch of Serve-U FTP log files and store them in a database so people could report on them. I ended up developing a small C# app to do the following:</p>
  
  <ol>
  <li><p>Look for all files in a dir that have not been loaded into the db (there is a table of previously loaded files). </p></li>
  <li><p>Open a file and load all the lines into a list. </p></li>
  <li><p>Loop through that list and use RegEx to identify the kind of row (CONNECT, LOGIN, DISCONNECT, UPLOAD, DOWNLOAD, etc), parse it into a specific kind of object corresponding to the kind of row and add that obj to another List.</p></li>
  <li><p>Loop through each of the different object lists and write each one to the associated database table. </p></li>
  <li><p>Record that the file was successfully imported.</p></li>
  <li><p>Wash, rinse, repeat.</p></li>
  </ol>
  
  <p>It's ugly but it got the job done for the deadline we had.</p>
  
  <p>The problem is that I'm in a DBA role and I'm not happy with running a compiled app as the solution to this problem.  I'd prefer something more open and more DBA-oriented.</p>
  
  <p>I could rewrite this in PowerShell but I'd prefer to develop an SSIS package. I couldn't find a good way to split input based on RegEx within SSIS the first time around and I wasn't familiar enough with SSIS. I'm digging into SSIS more now but still not finding what I need. </p>
  
  <p>Does anybody have any suggestions about how I might approach a rewrite in SSIS?</p>