Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2004
    Location
    Miami, FL
    Posts
    9

    Question Unanswered: Date Conversion Problem

    I am importing a text file into a SQL table, using DTS. My problem is concerning the date fields. The source fields are in the yyyymmdd format. I have tried using datetime transformation, using yyyyMMdd as the source format, and MM/dd/yyyy as the destination formation. If there is a valid date, this works fine. However, many of the dates are either null or contain spaces, and the DTS will not handle them. Any suggestions as to how to handle this?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Is the column defined as NOT NULL?

    DTS the table a stage table with all of the columns a varchar...

    Then manipulate it with sql and do an insert?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2004
    Location
    Miami, FL
    Posts
    9

    Question

    Originally posted by Brett Kaiser
    Is the column defined as NOT NULL?

    DTS the table a stage table with all of the columns a varchar...

    Then manipulate it with sql and do an insert?
    ------------------------------------

    The Allow Nulls option is turned on in the table definition. If I manually add a record in Enterprise Manager, it will accept nulls. It's just the DTS that doesn't like them.

    I can use an intermediary table if that's the only way. I was just hoping that it could be done during the initial import.

    Thanks for your suggestions.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sounds like a fixed width file...

    Actually I' m suprised it's not working...

    Where's the file coming from?

    Mainframe?

    Got any unprintable chars there?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2004
    Location
    Miami, FL
    Posts
    9

    Question

    Originally posted by Brett Kaiser
    Sounds like a fixed width file...

    Actually I' m suprised it's not working...

    Where's the file coming from?

    Mainframe?

    Got any unprintable chars there?
    Yes, it is a mainframe file, with fixed width fields. There are no unprintable characters. It's just that some of the date fields are either null or contain spaces (I'm not sure which), and DTS keeps choking on them.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you post the transformation code?

    You do know that putting the code in the package like that slows everything down..

    You're much better off getting all the data in, then using set based methods to transform the data...

    much, much fatser...

    Ever use bcp?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Apr 2004
    Location
    Miami, FL
    Posts
    9

    Question

    Originally posted by Brett Kaiser
    Can you post the transformation code?

    You do know that putting the code in the package like that slows everything down..

    You're much better off getting all the data in, then using set based methods to transform the data...

    much, much fatser...

    Ever use bcp?
    No, I haven't used bcp before. I'll check it out.

    In addition to choosing datetime transformation and setting the formats, I've also tried using an ActiveX script. Here is the ActiveX code I've tried for the transformation:

    Function Main()
    If Not IsNull(DTSSource("Col010")) AND LEN(TRIM(DTSSource ("Col010"))) > 0 Then (Checking for null or spaces)
    DTSDestination("AWARD_DATE") = MID(DTSSource("Col010"),7,2)&"-"&MID(DTSSource("Col010"),5,2)&"-"&LEFT(DTSSource("Col010"),4)
    Main = DTSTransformStat_OK
    End If
    End Function

    The error returned is: Invalid procedure call or argument - DTSSource

  8. #8
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    Just wondering is your System a AS400 cause i had also ran into this before.

  9. #9
    Join Date
    Apr 2004
    Location
    Miami, FL
    Posts
    9
    Originally posted by hillcat
    Just wondering is your System a AS400 cause i had also ran into this before.
    No, PC with Windows XP Pro & SQL Server 2000

  10. #10
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    but is the the mainframe file a rpg file

  11. #11
    Join Date
    Apr 2004
    Location
    Miami, FL
    Posts
    9

    Question

    Originally posted by hillcat
    but is the the mainframe file a rpg file
    I'm not familiar with rpg; all I know is, the file is a text file from a mainframe, with fixed width fields. I was given a printout of the file layout to indicate starting and ending point of the fields.

  12. #12
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    well Is not null function will not work thats for sure since this is a unprintable caracter and this caracter as a value. if this unprintable caracter is at the begining of a string try to trim the first caracter from the string.

  13. #13
    Join Date
    Apr 2004
    Location
    Miami, FL
    Posts
    9
    Originally posted by hillcat
    well Is not null function will not work thats for sure since this is a unprintable caracter and this caracter as a value. if this unprintable caracter is at the begining of a string try to trim the first caracter from the string.
    I'm not sure whether it is null or spaces; that's why I used both the the 'not isnull' and the 'trim', so that I'd be covered either way. If either is not true (value is null, or value is spaces), then the statements inside the if clause should be bypassed

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    My guess here is that if you DTS a column that has space and no transformation, it'll put in null..

    But because of the transformation, I guess it thinks there should be a valid value, and then fails.

    The other thing is that it might not be space, but other data that doesn't transform to a valid date.

    Use a stage table and do some analysis.

    Soemthing like

    SELECT * FROM myStage99 WHERE ISDATE(yourDateCol) = 0
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Apr 2004
    Location
    Miami, FL
    Posts
    9
    Originally posted by Brett Kaiser
    My guess here is that if you DTS a column that has space and no transformation, it'll put in null..

    But because of the transformation, I guess it thinks there should be a valid value, and then fails.

    The other thing is that it might not be space, but other data that doesn't transform to a valid date.

    Use a stage table and do some analysis.

    Soemthing like

    SELECT * FROM myStage99 WHERE ISDATE(yourDateCol) = 0
    I'll give it a try. Much thanks . . .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •