Solved

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

Posted on 2014-04-01
4
2,046 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

724 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