0

I have a BIDS project set-up to upload data from several flat files to a SQL Server 2008 database. The data is provided by another organization.

A lot of the data has trailing or leading spaces. This is enough of a problem that it would require me changing the size of the columns in my table to accommodate. I could use a derived column to resolve this, but there are enough columns that it would be impractical to set this all up manually.

I'm trying to use a script component (transformation) to remove leading and trailing spaces from every field before being uploaded. However, this is my first stab at using a script component and I'm having no luck.

Trying a simple foreach loop:

foreach(DataColumn i in Row)
  {
      /* do something */
  }

Gives me an error, "foreach statement cannot operate on variables of type 'Input0Buffer' because 'Input0Buffer' does not contain a public definition of 'GetEnumerator'. What do I need to do to resolve this?

indigochild
  • 350
  • 1
  • 5
  • 21

1 Answers1

6

Row in a script component is NOT a System.Data.DataRow but rather it is a Input0Buffer. The Input0Buffer is derived directly from your ssis package and has column names as properties.

so you can use GetType().GetProperties() to get all of the System.Reflection.PropertyInfo that are on the object and go through them to do what you want. Though you will have to do some research on how to use system reflection to actually call the property dynamically to modify the contents because I don't know that answer of the top of my head.

using System.Linq;

var properties = Row.GetType().GetProperties().Where(p => !p.Name.EndsWith("_IsNull")).Select(p => p.Name).ToArray();
foreach (var p in properties)
{
    //Do Something
}
Matt
  • 13,833
  • 2
  • 16
  • 28