Appending letters to Member ID filter

Question

I'm pretty new to .net code. I was using C# code inside script task-SSIS

I'm using bulk copy task to pull data from oracle to sql db based on MemberID. I have got member id column in table which is having all integer values.. Member ID ex: 123456 but i have data in oracle like 123456A, i need to append letter in the end of each member before search in oracle and letter is not fixed..i have to add A to M to each member in the end.Any help greatly appreciated sSQL_For_Incoming_MemID parameter is the query fetching id's from sql.. EX: Select distinct MemberID as HICN from table thanks

You're copying from Oracle with the letter already there, right? Then you don't need to add it - you need to change your SQL field to accept something with a letter. Or am I confused? This is why we like to ask for code. You really haven't described what the problem is. — Shannon Holsinger, Sep 08 '16 at 16:33
yeah bit confusion..I got member field in table having all integer member id's but in query i have to pass member id+string... — Koti Raavi, Sep 08 '16 at 16:35
Ok - I need to get this straight. You are copying from oracle to sql. In Oracle, it is 123456, but in SQL, it's 123456A - you need to add an A through an M? Then you can't do that with bulkCopy - you'll have to iterate through and add the ltters individually if they are different for every record. — Shannon Holsinger, Sep 08 '16 at 16:39
No in sql i have 123456..but have to pass 123456A to 123456M to oracle query in script task:(..i need help of iteration — Koti Raavi, Sep 08 '16 at 16:40
If this is JUST to find the records in Oracle, then you would amend your SELECT statement in the BULK COPY to WHERE memberID LIKE '123456%' — Shannon Holsinger, Sep 08 '16 at 16:42
Good one..but i can't do that..it pull loads of records if i pass it as integer.for example there is a data in oracle from 123456A-123456Z..this case am pulling 26 records but required only one :(..Am going to fetch 50000 records from oracle..this case 50000*26 :( — Koti Raavi, Sep 08 '16 at 16:45
your brilliant ..yeah i only need A-M for now..may be in future depends on requirement have to change code — Koti Raavi, Sep 08 '16 at 16:50
I got it..but i'm planning to handle inside script task only : — Koti Raavi, Sep 08 '16 at 16:55
This is why we don't normally help people who don't post code and ask specific questions. This is three times you've changed what you want from your original question. I have **no clue** what "inside script task only" means. If you want to BULK COPY - that's the SQL you use. If you have a specific question, why don't you be brilliant and ask it in a way that someone can answer it? — Shannon Holsinger, Sep 08 '16 at 16:58
you can use @ShannonHolsinger's answer within your code, you just have to adapt for oracle instead and change the RIGHT() to SUSBTRING(). There are probably ways of doing this within dataflow and not in a script.... — Matt, Sep 08 '16 at 17:18
Also having "Dts.TaskResult = (int)ScriptResults.Success;" in the Exception Catch seems like a very bad idea IMHO. you could still use the failed contraint if you want your package to still succeed, but of course it won't though because you re-throw the error which would cause package failure right after setting success.... — Matt, Sep 08 '16 at 17:19
Thank you so much Matt..I don't know much .net code :(..im pretty new..i think throw ex won't give proper error message ..is it correct?..if yes pls let me know what code i need to add.if possible please provide code — Koti Raavi, Sep 08 '16 at 17:22
Yes - sorry - I keep getting confused about which database is on which side of the transaction. I'll revise my answer in a minute. Thanks for adding that, @KotiRaavi - that will make it a lot easier to help — Shannon Holsinger, Sep 08 '16 at 17:37
Revised my answer. If you need more help, please run and let me know what the value of Dts.Variables["$Package::sSQL_For_Incoming_MemID"].Value.ToString(); is on the first loop, and I'll finish this for you. — Shannon Holsinger, Sep 08 '16 at 17:47
To get the value of Dts.Variables["$Package::sSQL_For_Incoming_MemID"].Value.ToString(), insert a breakpoint when running in DEBUG mode or add a Console.WriteLine(Dts.Variables["$Package::sSQL_For_Incoming_MemID"].Value.ToString()) right under it and tell me what the string evaluates to. — Shannon Holsinger, Sep 08 '16 at 17:48
Throw EX will give a proper error message in .net however ssis is slightly different when you code the debugging and errors are a lot more cryptic and not as intuitive because what happens is you get an error script task failed blah blah blah but not the actual details you are looking for so setting a variable with an error is not a bad way of getting the exception. You do redundantly do this though if the error comes from GPSSearch() also because you actually handle and not re-throw the error there it essentially ignores the error. you should definitely rethrow in that catch block too. — Matt, Sep 08 '16 at 17:57
side note I just happened to look at the question again if you want me to get notified of a comment to be able to reply make sure you use the @ symbol like I did above. — Matt, Sep 08 '16 at 17:59
Thanks Shannon, Underscore not working in oracle..i have tried it but does't show any result — Koti Raavi, Sep 08 '16 at 18:16

Shannon Holsinger · Answer 1 · 2016-09-08T17:42:37.827

Select * from myTable where memberID like '123456%' and ASCII(right(memberID,1)) > 64 and ASCII(right(memberID,1))< 78

In Oracle:

Select * from myTable where memberID = '123456_' and ASCII(substr(memberID,-1)) > 64 and ASCII(substr(memberID,-1))< 78

To get this to work in your script, look at:

 Dts.Variables["$Package::sSQL_For_Incoming_MemID"].Value.ToString();

Find out what the value of that is, and manipulate it to equal my SQL above. If you need help with that, please post what Dts.Variables["$Package::sSQL_For_Incoming_MemID"].Value.ToString() is on the first run, and I'll put together a manipulation for you.

Appending letters to Member ID filter

1 Answers1