Hi, The data will be in a text file. I need to check the two columns from within the same textfile. like, we have 2 columns Place and Country as like with below said similar values ---Place---|----Country---- --NY, US--|-----US----- Alabama-|--------------- Now i have check the column 'Country' whether it is null or not null..accordingly i have to remove the last 2 character of the field 'Place' . If value is there then blindly i can remove the last 2 character from col 'Place' if not i will not remove any characters. like in above said xample, i will remove the character with expression like substring~~. But i am confused here how to check against the same file.. If any had this similiar kind of work plz do let me know how can i handle this ? Thanks !--------------------------- Radhai Krish | Golden Age is no more far | --------------------------

What do you mean by "how to check against the same file"? Do you want to check data per row or do you want t compare rows? You can check for empty country like this in a derived column (or are your "-" spaces?): TRIM(REPLACE([----Country----], "-", "")) == "" ? <<<true condition>>>, [----Country----] or LEN(TRIM(REPLACE([----Country----], "-", ""))) == 0 ? <<<true condition>>>, [----Country----]Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Hi, Thanks for your replies. Now I am able to remove the last 2 characters from col 'Place'. Thanks to Kingxxx1 But i am not able to check for the value in the col 'Country' which is my main requirement. I need to check for the value of the field Country and then only i can remove the character. Like if the value for Country field is null then i dont want to remove any character or space from the field Place. But in case if any value is present with the field i need to apply the logic for removing characters. i've arrived at the following formula. But its removing character irrespective of the value present in the Country field. The null value is not at all checked here. IsNull seems to not work here. (!ISNULL(REPLACE([Country],""," ")) ? SUBSTRING([Place],1,LEN(RTRIM([Place])) - 2) + " " : "") In SQL query, im able to get the records for null or not null using the formula Where [Country] != '' I dont know how to check the same here in SSIS. Please do let me know. --------------------------- Radhai Krish | Golden Age is no more far | --------------------------

What do you mean by "how to check against the same file"? Do you want to check data per row or do you want t compare rows? You can check for empty country like this in a derived column (or are your "-" spaces?): TRIM(REPLACE([----Country----], "-", "")) == "" ? <<<true condition>>>, [----Country----] or LEN(TRIM(REPLACE([----Country----], "-", ""))) == 0 ? <<<true condition>>>, [----Country----] Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter Hi, I meant the column check to be done within the same file. I want to check the col 'Country' data per row and ve to update the col 'Place' value accordingly. I've given the values to be '------' jus for indicating that the value is null or "" Can you please let me know how can i delete the last two character from the 'Place' col value ? I am new to this SSIS project so unable to get to understand the expressions. Thank you ! --------------------------- Radhai Krish | Golden Age is no more far | --------------------------

Hi krish, Its is quite simple.. (!ISNULL(REPLACE([Country],""," ")) ? SUBSTRING([Place],1,LEN(RTRIM([Place])) - 2) + " " : "") replace this with below expression LEN(TRIM([Country]))!=0? SUBSTRING([Place],1,LEN(RTRIM([Place])) - 2) + " " : ""

Hi krish, Its is quite simple.. (!ISNULL(REPLACE([Country],""," ")) ? SUBSTRING([Place],1,LEN(RTRIM([Place])) - 2) + " " : "") replace this with below expression LEN(TRIM([Country]))!=0? SUBSTRING([Place],1,LEN(RTRIM([Place])) - 2) + " " : "" Hi King thanks, but now for the country field's null value, i am getting the Place field's value to be null i.e its even not taking up the original Place value .. May i request you ? Could you please check that formula what i am using is correct for both condition .. for the country field values without null your formula is working good ...--------------------------- Radhai Krish | Golden Age is no more far | --------------------------

Hi krish, check this LEN(TRIM([Country]))!=0? SUBSTRING([Place],1,LEN(RTRIM([Place])) - 2) + " " : [Place]

Hi krish, check this LEN(TRIM([Country]))!=0? SUBSTRING([Place],1,LEN(RTRIM([Place])) - 2) + " " : [Place] Thank you so much !!!--------------------------- Radhai Krish | Golden Age is no more far | --------------------------

Hi anyone please let me know the expression that i can use it for deleting the last 2 character of a field with space trailing before ... --------------------------- Radhai Krish | Golden Age is no more far | --------------------------

SUBSTRING(<ColumnName>, 1, FINDSTRING(<ColumnName>, " ", 1) - 1) http://msdn.microsoft.com/en-us/library/ms137541.aspx http://msdn.microsoft.com/en-us/library/ms141748.aspx Here is the reference for all the string functions in SSIS http://msdn.microsoft.com/en-us/library/ms141671.aspxPlease mark the post as answered if it answers your question

hi check this SUBSTRING(field,1,LEN(RTRIM(Field)) - 2)