We help IT Professionals succeed at work.

Problem with FindString IN SSIS

Hi Experts, I have an SSIS Package that is loading an Excel file into an SQL Server Table.
However I do have a problem with one column.

On my source Excel file I have a Column called "ProductsInNeed" and the datatype is like (DT_WSTR with a length of 255)
and the values in that column are like

"Toner,Ink,computer,printer,phone,power"
"printer,Ink,Toner,"
"computer,Toner,phone,power"
""
"computer,printer"

On my Target Table I have 6 columns like
"TonerWanted","InkWanted","computerWanted",
"printerWanted","phoneWanted","powerWanted"
All columns are with Datatype Varchar with a length 255.

What I have to do is like I need to check for a string in the incoming column
for example

If I have a string "Toner" in the source column then I need to send That to the Target Column
"TonerWanted" or else I need to send in a NULL

For this I have written an expression in DerviedColumn Like this

TRIM(FINDSTRING(ProductsInNeed,"Toner",1) == 0 ? "Toner" : (DT_STR,255,1252)NULL(DT_STR,255,1252))

But I am getting an error Like this

[OLE SRC [1]] Error: There was an error with output column "ProductsInNeed" (3112) on output
"OLE DB Source Output" (11). The column status returned was:
"Text was truncated or one or more characters had no match in the target code page.".

Any help on this will be Highly Appreciated.... Thanks In Advance
Comment
Watch Question

Try changing the first (DT_STR, 255, 1252) To (DT_STR, 20, 1252)
.... or some other value smaller than 255, but bigger than your largest conditional compare - for example, 120.
Reza RadConsultant, Trainer

Commented:
try this one:

TRIM(FINDSTRING(ProductsInNeed,"Toner",1) == 0 ? "Toner" : NULL(DT_STR,255,1252))
Top Expert 2010

Commented:
there is a very nice Tool in SSIS called conditional Split, its very useful and easy to use, attached a word document with images on how to use it

i am reading a table from SQL and based on specific data in the acct_type field, i am sending it to 2 different files

inside the conditional split control you need to make cases and then drag each case to different destination

conditional-split.doc
Reza RadConsultant, Trainer

Commented:
@AmmarR:
in this case Conditional Split is useless,
question is how to find a string inside a value and pass another values based on different input values, a Derived Column or a Script Component Transformation can be used.
ability of Conditional Split is to put condition on input values, and then redirect different values to different outputs , and this is different from above requested.


Top Expert 2010
Commented:
oh my god

sorry, i read the question upside down.

yeah derived columns is the right solution and just to make sure
i tried your question exactly the way you described above reading data from excel into SQL and it worked fine with me without any errors

the only problem was
you need to change the
TRIM(FINDSTRING(ProductsInNeed,"Toner",1) == 0 ? "Toner" : (DT_STR,255,1252)NULL(DT_STR,255,1252))

into

TRIM(FINDSTRING(ProductsInNeed,"Toner",1) != 0 ? "Toner" : (DT_STR,255,1252)NULL(DT_STR,255,1252))

!= 0

or

you can input data from Execl and keep it as as in a sql table and then use insert into select case.

this is just another option.
Top Expert 2010

Commented:
i forgot to upload the image

and i think may be your problem is the oledb driver.

try using SQL Server Destination, as your data flow destination instead of OLEDB


img.jpg