asked on

concanate two fields using DTS

hi guys, i have a small problem, I am doing a dts from an excel spreadsheet to a sql server table. the fields are the area code and the phone number column. I need to concanate these two columns into one column in my sql server table. that part is done; however i also need to format this numbers (right now coming out as 3055625889 instead of (305) 562-5889, I have to do this from the dts point of view, can someone help me??? this is what i have so far..

this is what i have so far, but keep getting the error message Duplicate column name ' Account Manager Phone '

this is my code:

Function Main()
      IF ISNULL(DTSSource("ACCT MGR AREA CODE").Value) _
      OR _
      ISNULL(DTSSource("ACCT.MANAGER PHONE").Value) _
      THEN
      DTSDestination("Account Manager Phone") = ""
      ELSE
      DTSDestination("Account Manager Phone") = _
      FormatPhoneNumber(DTSSource("ACCT MGR AREA CODE").Value & _
      DTSSource("ACCT.MANAGER PHONE").Value)
      END IF


      Main = DTSTransformStat_OK
      End Function

Function FormatPhoneNumber(strPhone)
      ' strPhone parameter is the area code and phone number concatenation
      Dim strNew

      strNew = "(" & Left(strPhone,3) & ") " & Mid(strPhone,4,3) & "-" & Mid(strPhone,7,4)
      FormatPhoneNumber = strNew

      Main = DTSTransformStat_OK
      End Function

thanks!!!!!!!!!!!!!!!

Atlanta_Mike

That looks correct to me? What happens if you don't use the FormatPhoneNumber function?

printmedia

ASKER

same error

Atlanta_Mike

Check your transform, the destination tab and make sure Account Manager Phone isn't in there twice. That's the ony thing that I can see is possible.

printmedia

ASKER

thanks, but eventhough now i made it work all the fields come out as null , any ideas why?

nmcdermaid

I would suggest you load the data in as normal then run an Update quwery (from DTS) to format the numbers.

ASKER CERTIFIED SOLUTION

Atlanta_Mike

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

>>IF you're concatenating two fields and one is NULL both become null. <<
You may want to double check that using an ActiveX script.