I am currently updating an already existing SSIS package. The current Package pulls data from an Excel Spread Sheet that is provided by our IT Department. It lists Machine Names of Computers and counts it for a License Report.

I currently have the Job (derived column) strip off the M (Mobile) or D (Desktop) from the first part of the machine name so that it returns just the user name, which is what I need for the report.

MBRUBAKERBR => BRUBAKERBR

However, our IT Department just implemented Windows 7 and with it a new Naming convention. Now there is a 76A, B, C or D that is added to the end of all of the updated machines. If the machine has not been updated then it stays with the older Naming Convention (seen Above).

There are also machines that have to stay on XP, their names have been update to have X3A, B, C or D at the end of theirs.

MBRUBAKERBR76A or DBRUBAKERX3C

What I need is to remove the last part of the name so that I just get the user name out of it for reporting.

The issues is I can't use a LEFT, RIGHT, LTRIM or RTRIM expression as some of the computer names will only have the M or D in front (as they have not yet been upgraded).

What can I do to remove these characters without rebuilding this package?

But how would i add the script to my existing package. I have the Excel Source feeding into a series of Look-Ups to assign some specific values to them. I want to strip the unnecessary text and return only the user name fro the naming convention.

The Existing Data Conversion already strips the M and D from the front end so would I put this script into a Variable and then run the variable?

I would store the patterns in a table in sql server and you can use that to do a lookup with values from the file. if matched replace the matching part with blank ("") and you will get the patterns removed from your machine names