Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rjhe22
Constraint Violating Yak Guru
283 Posts |
 Posted - 2014-05-12 : 07:29:32
|
HiI want to read the following line in a column in my ssis pacageIgnis Absolute Return Credit Fundnow im using the follwoing codeFINDSTRING(Group,"Ignis Absolute Return Credit Fund",1) == 0 ? "IL02" : Group That who line isnt in file but im getting IL02 back when i shouldnt be why is this? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-05-12 : 07:32:35
|
| Sorry I didnt understand your question. Can you elaborate? Also whats your source field data type? Is it char,nchar? Is your collation case sensitive?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
rjhe22
Constraint Violating Yak Guru
283 Posts |
Posted - 2014-05-12 : 07:38:07
|
| what i need to do it when i read in my data from flat file source i want to read a Column called group and in the colum if the word Ignis Absolute Return Credit Fund is in it it i want to populate fund column with IL02 and if it has Ignis Absolute Return Government Bond Fund then it populate the fund column with IL01 instead |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-05-12 : 07:42:30
|
quote:
Originally posted by rjhe22
what i need to do it when i read in my data from flat file source i want to read a Column called group and in the colum if the word Ignis Absolute Return Credit Fund is in it it i want to populate fund column with IL02 and if it has Ignis Absolute Return Government Bond Fund then it populate the fund column with IL01 instead
For that easiest method would be to add a lookup table in your db with full group description and short code. Then inside SSIS add a lookup task to lookup to table based on description field and select the short code field to be included in the output.Also you need to make sure field is not of char/nchar type otherwise you might have to TRIM it to remove the trailing spaces------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
rjhe22
Constraint Violating Yak Guru
283 Posts |
Posted - 2014-05-12 : 07:46:43
|
| HiHow come something like this wouldnt workFINDSTRING(Group,"Ignis Absolute Return Credit Fund",1)== 0 ? "IL02" : Group |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-05-12 : 07:56:00
|
| It will work. But its not maintainable ie tomorrow if you want to define a new group or modify a group code you've to come and modify package each time to make the changesThe method suggested by me just requires adding/moifying a table record without any change to be done to package. And its much easier as its a simple insert/update.In both cases you need to careful against data type ie if its char,nchar etc depending on declared field length it will pad rest of places with spaces. Also check if the case of incoming value is correct. FINDSTRING will not find value if its of different case coming from the source.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
rjhe22
Constraint Violating Yak Guru
283 Posts |
Posted - 2014-05-12 : 09:06:05
|
| its only ever going to be 1 of three things will never change from these things so i think the derived column way is find just cant get my code to work |
|
|
|
|
|
|
Reading line of text