When DTS hits a bad date

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • robboll
    #1

    When DTS hits a bad date

    This routine works in most cases, but fails when a bad date is entered
    such as:
    19910631 -- there is no June 31st.

    Instead of ignoring the bad date the entire DTS job fails. Obviously
    this is something that should be validated at data entry, but
    unfortunately the only control I have is when appending to the table
    with these data quirks. Any suggestions appreciated!!!

    '************** *************** *************** *************** ***********

    ' Visual Basic Transformation Script
    ' Copy each source column to the
    ' destination column
    '************** *************** *************** *************** *************



    Function Main()
    'DTSDestination ("Col002") = DTSSource("Col0 02")
    if DTSSource("Col0 02") = "99999999" or DTSSource("Col0 02") =
    Null then

    Main = DTSTransforStat _SkipRow
    else
    DTSDestination( "Col002") = mid(DTSSource(" Col002"),1,4) & "/"
    &
    mid(DTSSource(" Col002"),5,2) & "/" & mid(DTSSource(" Col002"),7,2)
    End if
    Main = DTSTransformSta t_OK
    End Function


    RBollinger

    Tags: None
    • Stu
      #2
      Re: When DTS hits a bad date

      Regardless of how you want to handle the bad data, you need to check if
      the parsed source column is a date first, eg:

      Dim DestinationDate

      DestinationDate = mid(DTSSource(" Col002"),1,4) & "/" &
      mid(DTSSource(" Col002"),5,2) & "/" & mid(DTSSource(" Col002"),7,2)

      If IsDate(Destinat ionDate) is True then
      DTSDestination( "Col002") = DestinationDate
      Else
      'do something; write the row to a log, correct the error
      based on predefined rules,
      'skip the import
      End if

      HTH,
      Stu


      robboll wrote:
      This routine works in most cases, but fails when a bad date is entered
      such as:
      19910631 -- there is no June 31st.
      >
      Instead of ignoring the bad date the entire DTS job fails. Obviously
      this is something that should be validated at data entry, but
      unfortunately the only control I have is when appending to the table
      with these data quirks. Any suggestions appreciated!!!
      >
      '************** *************** *************** *************** ***********
      >
      ' Visual Basic Transformation Script
      ' Copy each source column to the
      ' destination column
      '************** *************** *************** *************** *************
      >
      >
      >
      Function Main()
      'DTSDestination ("Col002") = DTSSource("Col0 02")
      if DTSSource("Col0 02") = "99999999" or DTSSource("Col0 02") =
      Null then
      >
      Main = DTSTransforStat _SkipRow
      else
      DTSDestination( "Col002") = mid(DTSSource(" Col002"),1,4) & "/"
      &
      mid(DTSSource(" Col002"),5,2) & "/" & mid(DTSSource(" Col002"),7,2)
      End if
      Main = DTSTransformSta t_OK
      End Function
      >
      >
      RBollinger

        Comment

        • robboll
          #3
          Re: When DTS hits a bad date

          thanks!
          Stu wrote:
          Regardless of how you want to handle the bad data, you need to check if
          the parsed source column is a date first, eg:
          >
          Dim DestinationDate
          >
          DestinationDate = mid(DTSSource(" Col002"),1,4) & "/" &
          mid(DTSSource(" Col002"),5,2) & "/" & mid(DTSSource(" Col002"),7,2)
          >
          If IsDate(Destinat ionDate) is True then
          DTSDestination( "Col002") = DestinationDate
          Else
          'do something; write the row to a log, correct the error
          based on predefined rules,
          'skip the import
          End if
          >
          HTH,
          Stu
          >
          >
          robboll wrote:
          This routine works in most cases, but fails when a bad date is entered
          such as:
          19910631 -- there is no June 31st.

          Instead of ignoring the bad date the entire DTS job fails. Obviously
          this is something that should be validated at data entry, but
          unfortunately the only control I have is when appending to the table
          with these data quirks. Any suggestions appreciated!!!

          '************** *************** *************** *************** ***********

          ' Visual Basic Transformation Script
          ' Copy each source column to the
          ' destination column
          '************** *************** *************** *************** *************



          Function Main()
          'DTSDestination ("Col002") = DTSSource("Col0 02")
          if DTSSource("Col0 02") = "99999999" or DTSSource("Col0 02") =
          Null then

          Main = DTSTransforStat _SkipRow
          else
          DTSDestination( "Col002") = mid(DTSSource(" Col002"),1,4) & "/"
          &
          mid(DTSSource(" Col002"),5,2) & "/" & mid(DTSSource(" Col002"),7,2)
          End if
          Main = DTSTransformSta t_OK
          End Function


          RBollinger

            Comment

            • Stu
              #4
              Re: When DTS hits a bad date

              Sorry, just realized I had a typo:

              If IsDate(Destinat ionDate) is True then

              should be

              If IsDate(Destinat ionDate) = True then


              If you've never been to this site, you may want to check it out for
              lots of help on scripting functions: http://www.devguru.com

              Stu

              robboll wrote:
              thanks!
              Stu wrote:
              Regardless of how you want to handle the bad data, you need to check if
              the parsed source column is a date first, eg:

              Dim DestinationDate

              DestinationDate = mid(DTSSource(" Col002"),1,4) & "/" &
              mid(DTSSource(" Col002"),5,2) & "/" & mid(DTSSource(" Col002"),7,2)

              If IsDate(Destinat ionDate) is True then
              DTSDestination( "Col002") = DestinationDate
              Else
              'do something; write the row to a log, correct the error
              based on predefined rules,
              'skip the import
              End if

              HTH,
              Stu


              robboll wrote:
              This routine works in most cases, but fails when a bad date is entered
              such as:
              19910631 -- there is no June 31st.
              >
              Instead of ignoring the bad date the entire DTS job fails. Obviously
              this is something that should be validated at data entry, but
              unfortunately the only control I have is when appending to the table
              with these data quirks. Any suggestions appreciated!!!
              >
              '************** *************** *************** *************** ***********
              >
              ' Visual Basic Transformation Script
              ' Copy each source column to the
              ' destination column
              '************** *************** *************** *************** *************
              >
              >
              >
              Function Main()
              'DTSDestination ("Col002") = DTSSource("Col0 02")
              if DTSSource("Col0 02") = "99999999" or DTSSource("Col0 02") =
              Null then
              >
              Main = DTSTransforStat _SkipRow
              else
              DTSDestination( "Col002") = mid(DTSSource(" Col002"),1,4) & "/"
              &
              mid(DTSSource(" Col002"),5,2) & "/" & mid(DTSSource(" Col002"),7,2)
              End if
              Main = DTSTransformSta t_OK
              End Function
              >
              >
              RBollinger

                Comment

                Previous template Next
                Working...