November 28, 2010 at 8:46 pm
with "Target Field (Address1 varchar(30) & Address2 varchar(30)"
My question is " I don't want truncate data if the data is more than 30 char. If the Mailing_Address is less than 30 char stay in Address1 & if the data is more than 30 char rest of the data after 30 char transfer to Address2 field. "
Note:- How i can accomplish this one in SSIS. I believe i can use "Derived Transformation" or "Conditional Split" I need your help about "Expression". If my question is not clear Please let me know I will try to explain one more time. Thanks in advance.
November 28, 2010 at 10:45 pm
rocky_498 (11/28/2010)
with "Target Field (Address1 varchar(30) & Address2 varchar(30)"My question is " I don't want truncate data if the data is more than 30 char. If the Mailing_Address is less than 30 char stay in Address1 & if the data is more than 30 char rest of the data after 30 char transfer to Address2 field. "
Note:- How i can accomplish this one in SSIS. I believe i can use "Derived Transformation" or "Conditional Split" I need your help about "Expression". If my question is not clear Please let me know I will try to explain one more time. Thanks in advance.
Perhaps you would want to increase the size of your target column.
Your purpose cannot be solved by SSIS.
Raunak J
November 29, 2010 at 2:43 am
I disagree. It seems relatively straightforward to me.
You need two derived columns - Add1 and Add2 say.
Add1 will be Left(Address, 30)
and Add2 will be characters 31-60 (use a substring function to get that - haven't got time to check syntax at the moment).
Post back if you need help with the syntax - but please have a go first, as it's not very difficult.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 29, 2010 at 2:48 am
Phil Parkin (11/29/2010)
I disagree. It seems relatively straightforward to me.You need two derived columns - Add1 and Add2 say.
Add1 will be Left(Address, 30)
and Add2 will be characters 31-60 (use a substring function to get that - haven't got time to check syntax at the moment).
Post back if you need help with the syntax - but please have a go first, as it's not very difficult.
Add2 = SUBSTRING(Address,31,60)
Raunak J
November 29, 2010 at 5:06 am
As suggested by Phil, Raunak you can do. Also you can do this using script task. Some sample code below.
Dim address1, address2 As String
address1 = Dts.Variables("v_Address1").Value.ToString ' From Variable
If address1.Length() < 30 Then
Dts.Variables("v_Address1").Value = address1
Else
Dts.Variables("v_Address1").Value = address1.Substring(0, 29)
Dts.Variables("v_Address2").Value = address1.Remove(0, 29)
End If
Dts.TaskResult = Dts.Results.Success
November 30, 2010 at 10:17 pm
Sorry for late reply, Thanks all of you guys your help. I can't change my target table.
I really appreciate if some 1 send me " Expression", Thanks.
November 30, 2010 at 10:57 pm
rocky_498 (11/30/2010)
Sorry for late reply, Thanks all of you guys your help. I can't change my target table.I really appreciate if some 1 send me " Expression", Thanks.
the solution is already given above.
in the derived column transformation task you need to have Two new columns addres1 and address 2 with the expressions
SUBSTRING(Mailing_Address ,1,30)
SUBSTRING(Mailing_Address ,31,60)
December 1, 2010 at 7:55 pm
Thank you, I was not clear):,
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply