[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

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!!!!!!!!!!!!!!!

0
printmedia
Asked:
printmedia
1 Solution
 
Atlanta_MikeCommented:
That looks correct to me? What happens if you don't use the FormatPhoneNumber function?
0
 
printmediaAuthor Commented:
same error
0
 
Atlanta_MikeCommented:
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.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
printmediaAuthor Commented:
thanks, but eventhough now i made it work all the fields come out as null , any ideas why?
0
 
nmcdermaidCommented:
I would suggest you load the data in as normal then run an Update quwery (from DTS) to format the numbers.
0
 
Atlanta_MikeCommented:
Because one of the fields is null? IF you're concatenating two fields and one is NULL both become null. If you want to concatenate them you will have to use an ISNULL function to convert it to an empty string or zero depending on if it is character or numeric.

nmcdermaid is right, it is usually easier to bring the data in then manipulate it with SQL. But that's a decision for you.
0
 
Anthony PerkinsCommented:
>>IF you're concatenating two fields and one is NULL both become null. <<
You may want to double check that using an ActiveX script.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now