Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2346
  • Last Modified:

Using Derived Column (FINDSTRING) in a DTSX Package - SQL 2008 SSIS

I have a Sharepiont data that I am extracting into a separate SQL 2008 database. I am using a SSIS Package to move the data. This works great. The issue I am having is my selection fields have ;# in the front and in the back of the fields.

Example:  
:#Mike Brown;#
;#Dave Smilth;#
;# Tim Red;#

My goal is to remove the ;#.

I am trying to use Derived Column with a string function of FINDSTRING and it does not work. What am I doing wrong here? Here is my FINDSTRING code:

My Derived Column Name is:  Technician

FINDSTRING(Technician,";#",2)
0
allenkent
Asked:
allenkent
  • 2
  • 2
1 Solution
 
ValentinoVBI ConsultantCommented:
If you know that you always need to lose the first and last two characters you can just use SUBSTRING() in combination with LEN().  Something like this:

SUBSTRING(Technician, 3, LEN(Technician)-4)

Open in new window

Another option, assuming that character combination is never part of your string as valid value, is to use REPLACE():

REPLACE(Technician, ";#", "")

Open in new window

More info: SSIS Expression Functions
0
 
allenkentAuthor Commented:
Still does not work. I enter the options of REPLACE or SUBSTRING and the system does not like.
image-of-error-using-SUBSTRING.jpg
image-of-error.jpg
0
 
ValentinoVBI ConsultantCommented:
Ow, your Technician field is not a regular string but a BLOB (DT_NTEXT instead of DT_WSTR).  So you first need to convert it to regular string through a type cast.  The REPLACE expression would be something like this:

REPLACE((DT_WSTR, 100)Technician, ";#", "")

Open in new window

Note that the 100 is the maximum string length, you can change that to any length you prefer but 100 seems sufficient given the examples.
0
 
allenkentAuthor Commented:
Perfect answer. Worked great.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now