title: Import Contents from multiple CSV files and Add column for CSV Filename
body: <p>Here’s the problem:</p>
  
  <p>I need to capture a portion of a file name and load it in to a table along with the contents of that file which is a CSV file. The files will be stored in to a folder and will consists of multiple files. I will not know what the file names are. All files will be named using the same format.  </p>
  
  <p>So, here is a breakdown of how it will look.  </p>
  
  <p>RDBMS: SQL Server 2008 R2<br>
  Filename format: xx_xxxx_SERVERNAME1_date.csv<br>
  File Content: Column1, Column2, Column3, Column4 (delimiter used – comma)   </p>
  
  <p>I need to capture only the SERVERNAME portion of the file name as well as all the content within that file.  </p>
  
  <p>So, the table will/should look like this:<br>
  Server_Name         Column1     Column2     Column3     Column4<br>
  SERVERNAME1         XXX         XXX         XXX         XXX<br>
  SERVERNAME1         XXX         XXX         XXX         XXX<br>
  SERVERNAME1         XXX         XXX         XXX         XXX<br>
  SERVERNAME2         XXX         XXX         XXX         XXX<br>
  SERVERNAME2         XXX         XXX         XXX         XXX<br>
  SERVERNAME2         XXX         XXX         XXX         XXX<br>
  SERVERNAME3         XXX         XXX         XXX         XXX  </p>
  
  <p>Each server name in the first column is derived from the SERVERNAME portion of the file name and Columns 1 – 4 is derived from the content within the file. It should loop through the folder and repeat until all filenames and content have been inserted into the table.   </p>
  
  <p>I’m not sure what the best solution is to achieve this. I have a C# program that I found that works great if it was just a matter of capturing the content and loading into a table. I can also use SSIS to read the content.  </p>
  
  <p>My problem is capturing the filename data and then associating it with that content from that file.   </p>