Solved

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

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server (2008) Declare Variable (Date) and Set value 6 32
SSRS  - Dropdown with Null 3 25
How to share SSIS Package? 6 37
export sql results to csv 6 36
These days socially coordinated efforts have turned into a critical requirement for enterprises.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
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…

773 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