title: SSIS - Getting a Substring from Filename and Inserting into Database Table
body: <p>I have several files in a directory. The file name convention is <strong><code>yob + year + .txt</code></strong></p>
  
  <p>Here's an example of one file name: <strong>"yob1920.txt"</strong>. </p>
  
  <p>The structure of the file(s) is as such: <strong><code>name,sex,number</code></strong> (notice there is not year field as the year is a part of the file name.</p>
  
  <p>The structure of my staging table is: <strong>tblNames(year, name, gender, number)</strong></p>
  
  <p>My requirement:</p>
  
  <ul>
  <li>Get the date part of each filename (1920, 1921, etc.)</li>
  <li>insert it into a staging table with a column called Year</li>
  </ul>
  
  <p>I've created a multiple file connection, a foreach container and inside of that container I have a dataflow task that iterates through the directory and inserts the data. </p>
  
  <p>The problem is I don't fully understand (even after reading several sites) how variables work in relation to the container(s) and the data flow task that actually do the work. </p>
  
  <p>Can I have some guidance on getting a substring from a file name and inserting it into a database table that has a column that doesn't exist in said file?</p>