| Author |
Topic |
|
SeekingWisdom
Starting Member
24 Posts |
 Posted - 2009-02-13 : 18:10:48
|
 I am new to SSIS and need some help. I am needing of checking a field from a flat file for zeroes in it. I was told of the IFF instruction or command but I do not know hot to use it. Does any one have any pointer for a novice. Is there a link somewhere that would provide examples of usage of thes instructions? Please help. Thank You. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
SeekingWisdom
Starting Member
24 Posts |
Posted - 2009-02-14 : 10:51:43
|
| Thank you for your reply once again. As I mentioned earlier I am new to SSIS. I come from the world of Mainframes and iSeries using COBOL and RPG. SSIS is made up of various components or languages if you will that require a different mode of thinking and structuring of instructions to do one thing. Please note: I am presently doing the following as an expression in the Derived Column Transformation: DATE_WRKD_YYYY + "/" + DATE_WRKD_MM + "/" + DATE_WRKD_DD Now I find out that the Year, Monday and Day Fields contains zeroes in some cases. If so I need to build my "new date field" as "0001/01/01". If a valid date is found then I need to end up with a properly formatted date. if my field contents are: "00000000" I should come out with "0001/01/01" if my field contents are: 20090131 I should come out with "2009/01/31" How can I accomplisht this concatenation with an IFF expression is my dilemma? can I say: (Iff YYYY == "0000") ? "0001" + "/" + "01" + "/" + "01" : DATE_WRKD_YYYY + "/" + DATE_WRKD_MM + "/" + DATE_WRKD_DD)I wish to learn SSIS and I know that over time I can grow to love this application. Can you provide me with a link(s) that would teach me SSIS and make the transition from the mainframe/iSeries to to SSIS and links that would also show me the various examples or methods of expressions in SSIS? For example I came upon this expression by chance. REPLACE(SUBSTRING(DC_CD,1,2),"WC","CI")I understand this to say replace the characters "WC" if found with "CI" starting in position 1 for a length of 2. Is this correct? When I look at the STRING functions availabe in the Derived Column Transformation it is not evident that I can combine two functions to accomplish the one task as shown above. And these are the things that I find powerful yet elusive as I strive to learn SSIS Thank you for time, patience and consideration to my request. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-14 : 11:50:18
|
| does the date value currently exist in three fields DATE_WRKD_YYYY , DATE_WRKD_MM & DATE_WRKD_DD ? |
|
|
|
SeekingWisdom
Starting Member
24 Posts |
Posted - 2009-02-17 : 17:58:21
|
Hello Visakh16! thank you once again! Please note the expression that I have been using until I found that the date contains zeroes ( ). DATE_WRKD_YYYY + "/" + DATE_WRKD_MM + "/" + DATE_WRKD_DDI must now load the date as "0001/01/01" when this is the case. |
|
|
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
Posted - 2009-02-17 : 18:32:08
|
| I believe you can do the zero part on the Script Transformation Editor. Something like: If Row.field = "00000000" Then Row.field = "0001/01/01" End If |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-02-18 : 09:17:11
|
| CASE WHEN DATE_WRKD_YYYY = '0000' THEN DATE = 0001/01/01ELSE DATE = DATE_WRKD_YYYY + "/" + DATE_WRKD_MM + "/" + DATE_WRKD_DDEND |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-18 : 09:35:13
|
quote:
Originally posted by SeekingWisdom
Hello Visakh16! thank you once again! Please note the expression that I have been using until I found that the date contains zeroes (). DATE_WRKD_YYYY + "/" + DATE_WRKD_MM + "/" + DATE_WRKD_DDI must now load the date as "0001/01/01" when this is the case.
(DATE_WRKD_YYYY!=0 && DATE_WRKD_MM!0 && DATE_WRKD_DD!0)? (DATE_WRKD_YYYY + "/" + DATE_WRKD_MM + "/" + DATE_WRKD_DD) : "0001/01/01" |
|
|
|
SeekingWisdom
Starting Member
24 Posts |
Posted - 2009-02-18 : 11:47:20
|
| Thank you all for your reply. I did get the suggested code below in the derived column transformation expression to work for me with a very small change. I needed to place the values in quotes as shown below. (DATE_WRKD_YYYY!= "0000" && DATE_WRKD_MM!"00" && DATE_WRKD_DD!"00")? (DATE_WRKD_YYYY + "/" + DATE_WRKD_MM + "/" + DATE_WRKD_DD) : "0001/01/01" My additional question is what say that the ":" colon can be used with the instructio given? Can you provide me a link that would instruct me further? I am certain that there are other folks that are new to this world could benefit equally. I will have to look into the CASE code provided as I am also new to .netThank you once again. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-18 : 12:02:45
|
quote:
Originally posted by SeekingWisdom
Thank you all for your reply. I did get the suggested code below in the derived column transformation expression to work for me with a very small change. I needed to place the values in quotes as shown below. (DATE_WRKD_YYYY!= "0000" && DATE_WRKD_MM!"00" && DATE_WRKD_DD!"00")? (DATE_WRKD_YYYY + "/" + DATE_WRKD_MM + "/" + DATE_WRKD_DD) : "0001/01/01" My additional question is what say that the ":" colon can be used with the instructio given? Can you provide me a link that would instruct me further? I am certain that there are other folks that are new to this world could benefit equally. I will have to look into the CASE code provided as I am also new to .netThank you once again.
: is the condition separator in conditional operator. see syntax belowhttp://www.programmersedge.com/?p=55 |
|
|
|
|
Use of Iff in derived colum transformation