Solved

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

Posted on 2014-04-01
4
1,964 Views
Last Modified: 2016-02-10
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
Comment
Question by:allenkent
  • 2
  • 2
4 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39971970
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
 

Author Comment

by:allenkent
ID: 39973057
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
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 39974362
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
 

Author Closing Comment

by:allenkent
ID: 39975235
Perfect answer. Worked great.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question