title: How can I loop though columns by name in an SSIS Script component?
body: <p>I'm loading a pipe delimited flat file into a staging table. During the load process an SSIS script component performs some operations on a row. It may set a flag in one field based on values in another field, add a prefix to certain columns, or apply formatting.  For example, if a date is missing, the field is assigned to a default date.  (if Row.EndDate_isNull then Row.EndDate = defaultDate)</p>
  
  <p>These scripts become cumbersome when the same transformation needs to be applied to a series of rows. For example, a medical record file can describe each of 9 diagnoses with three fields: Diagnosis01_Date, Diagnosis01_Code, Diagnosis01_System....Diagnosis09_Date, Diagnosis09_Code, Diagnosis09_System. </p>
  
  <p>I want to use a loop to perform the operations over each of the 9 groups of 3 fields, instead of writing the same operation 9 times. </p>
  
  <p>If I were dealing with a collection in VB, for example, I'd write something like this in in the Input0_ProcessInputRow sub:</p>
  
  <pre><code>For i = 1 to 9
     row.("Diagnosis0"+ i + "_Date").Value = diagnosisDate
     row.("Diagnosis0"+ i + "_System").value = "ICD10"
  next i 
  </code></pre>
  
  <p>In the SSIS object model, however, the columns are exposed as properties of Input0Buffer and I can't find a way to use a variable to refer to them. So, how <em>can</em> I create a loop that operates on columns by name in an SSIS script component?  </p>
  
  <p>Edit:   I found the following sources, especially the first two,  to be helpful while I was doing my research on this problem. It seems like there should be a solution using system.reflection, but I just don't know .NET well enough to figure it out.</p>
  
  <p><a href="http://agilebi.com/jwelch/2007/10/21/address-columns-generically-in-a-script-task/" rel="nofollow noreferrer">http://agilebi.com/jwelch/2007/10/21/address-columns-generically-in-a-script-task/</a></p>
  
  <p><a href="http://agilebi.com/jwelch/2007/06/02/xml-destination-script-component/" rel="nofollow noreferrer">http://agilebi.com/jwelch/2007/06/02/xml-destination-script-component/</a></p>
  
  <p><a href="http://microsoft-ssis.blogspot.com/2010/12/do-something-for-all-columns-in-your.html" rel="nofollow noreferrer">http://microsoft-ssis.blogspot.com/2010/12/do-something-for-all-columns-in-your.html</a></p>
  
  <p><a href="http://toddmcdermid.blogspot.com/2011/05/iterating-over-columns-in-ssis-script.html" rel="nofollow noreferrer">http://toddmcdermid.blogspot.com/2011/05/iterating-over-columns-in-ssis-script.html</a></p>
  
  <p><a href="http://bidn.com/blogs/MikeDavis/ssis/1800/ssis-for-each-column-in-a-data-flow" rel="nofollow noreferrer">http://bidn.com/blogs/MikeDavis/ssis/1800/ssis-for-each-column-in-a-data-flow</a></p>
  
  <p><a href="https://social.msdn.microsoft.com/Forums/en-US/edbac1df-f05f-40db-820a-e009fae201a4/using-script-destination-object-to-create-and-write-to-new-text-file?forum=sqlintegrationservices&amp;forum=sqlintegrationservices" rel="nofollow noreferrer">https://social.msdn.microsoft.com/Forums/en-US/edbac1df-f05f-40db-820a-e009fae201a4/using-script-destination-object-to-create-and-write-to-new-text-file?forum=sqlintegrationservices&amp;forum=sqlintegrationservices</a></p>
  
  <p><a href="https://social.msdn.microsoft.com/Forums/en-US/757d11c8-8ad4-4021-a959-1d13c8dfdaa7/how-to-run-a-loop-for-all-columns-in-script-component-input-column-collection-for-each-row?forum=sqlintegrationservices" rel="nofollow noreferrer">https://social.msdn.microsoft.com/Forums/en-US/757d11c8-8ad4-4021-a959-1d13c8dfdaa7/how-to-run-a-loop-for-all-columns-in-script-component-input-column-collection-for-each-row?forum=sqlintegrationservices</a></p>
  
  <p><a href="https://stackoverflow.com/questions/40434886/how-can-i-get-the-column-value-in-script-component-in-ssis">How can I get the column Value in Script Component in SSIS?</a></p>