Solved

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

Posted on 2014-04-01
4
1,918 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

809 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