CODEWITHSUNDEEP
sqlparsingsplitssis
Mark Wilson
Mark Wilson

Reputation: 27

SSIS Script Split String with Variable Length

I'm trying to split a delimited field in SSIS using a script component and getting an index array error. If my dataset is;

Name City Age
-----------------------
John Washington 25
Sarah Chicago
Mike
Mary Philadelphia 34

I'm using script code;

Var ColumnValue = Row.People.Split(' ');

    Row.Name = ColumnValue[0];
    Row.City = ColumnValue[1];
    Row.Age = ColumnValue[2];

But when I run the SSIS package I get an index array error. I gather this is because the string I'm trying to split doesn't always have a value for City and Age. The data is being loaded from an Excel file to a SQL DB and there isn't any whitespace / delimiter at the end of the field if those fields are missing. How can I parse this field?

Upvotes: 0

Views: 853

Answers (1)

billinkc
billinkc

Reputation: 61259

You're blindly asking for something that doesn't exist but you're not warning the engine it may not find something.

Instead, you should check the resulting length of your array and fill in columns as expected.

Var ColumnValue = Row.People.Split(' ');
// We assume there's always a first thing
Row.Name = ColumnValue[0];

// Here begins things that might not be there
if (ColumnValue.Length > 1)
{
    Row.City = ColumnValue[1];
}

if (ColumnValue.Length > 2)
{
    Row.Age = ColumnValue[2];
}

.net Fiddle

Upvotes: 1

Related Questions