We help IT Professionals succeed at work.

DTS ActiveX dodgy date formats

Last Modified: 2013-11-30
We have an older app that we are slowly retiring.  I'm building a DTS to import the data from the older system to the new one.  I'm having a problem with dates. The old system uses a varchar(50) to store it, the new system uses a smalldatetime.  To add insult to injury, the date format is in two different formats in the old system  (there was a program change).  It goes from:
mmm dd yyyy hh:mm:ss  
dd/mm/yyyy hh:mm:ss

I've done an activeXscript for it - speed is not an issue - correctness is - this will only be run once in production.  However, its failing.  How can I narrow down what's causing it to fail?  (Not that good with DTS)

Function Main()
      dim src
      dim dest
      src = DTSSource("ACTDATE")
      if isnumeric(left(src, 2)) then
            ' dd/mm/yyyy
            dest = dateserial( mid(src, 7, 4), mid(src, 4, 2), left(src, 2))
      elseif isalpha(left(src, 3)) then
            ' mmm dd yyyy
            dest = datevalue(src)
            err.raise 1, "DTS Action Date", "first 2 digits are not mumeric, first three digits are not alpha"
      end if
      DTSDestination("dateAdded") = dest
      Main = DTSTransformStat_OK
End Function

Private Function IsAlpha(byVal string)
      dim regExp, match, i, spec
      For i = 1 to Len( string )
            spec = Mid(string, i, 1)
            Set regExp = New RegExp
            regExp.Global = True
            regExp.IgnoreCase = True
            regExp.Pattern = "[A-Z]|[a-z]"
            set match = regExp.Execute(spec)
            If match.count = 0 then
                  IsAlpha = False
                  Exit Function
            End If
            Set regExp = Nothing
      IsAlpha = True
End Function

Watch Question

Top Expert 2006
This one is on us!
(Get your first solution completely free - no credit card required)
This one is on us!
(Get your first solution completely free - no credit card required)


amit:  thanks for that.  I just lifted an isalpha from a website.  Yours looks more efficient.

simon: right, done.  found out there was bad data in about 6 lines - two with dates of d/mm/yyyy, and 4 with dates of dd/mm/yy.  they've been fixed.

Now I just have a primary key violation to deal with.  ^_^;

If I'm being told this:
Error at Destination for Row number 260125.
INSERT statement conflicted with TABLE FOREIGN KEY constraint 'FK_tblAction_tblMember'. The conflict occurred in database 'CRM', table 'tblMember'.

How can I select row number 260125 from my source to see what the data is?  
Top Expert 2006

Where do you have source data? In a table or in a file? What kind of file?


source: sql server 7 table.
dest: sql server 2000 table
Top Expert 2006

SQL server. It is little difficult as there won't be any order by clause in the DTS. You could try

select top 260125 from SourceTable

and look the last row. Without the order by clause you are at mercy of SQL server to show that row which it is not required to.


amit: damn.  did a rowcount - it seems to be failing on the last row.  this to me means either there is bad data in the last row,  or the error isn't being raised till the end.

i have found out that there are some customer numbers in the old system not in the new system.  this raises worries about data integrity.  i'll close this question off, as my initial concerns have been answered, have a poke around, and raise another one if I need too.

I'm going to split points, as you both helped.  Thanks guys.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.