Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2004
    Posts
    10

    Unanswered: Trying to create a DTS package to import dbf tables & need help

    Hi There,
    I am trying to create a DTS package that will import dbf tables. My problem is that the data was created back in the pre-dos 5 days, so to save room they took the dates and convert them to 2 characters.
    Now I would like to import these tables and conver the date back. I do have a Function that I can run in FoxPro to "Unpack" these dates.


    CODE
    Function UnPackDate( cDate )

    *!* PARAMETERS: f_datestr - Character (manditory). This should be a two
    **!* byte string created with PACKDATE().
    *!*
    *!* RETURNS: A date from 1/1/1970 and 11/29/2126

    If EMPTY(cDate)
    Return {//}
    Endif

    Local nDate
    nDate = (ASC( LEFT(cDate,1) ) * 256) + ASC( RIGHT(cDate,1))

    Return ( CTOD("01/01/1970") + nDate - 8225 )

    *================================================= ======================

    Does anyone know how to take this function and make it work while doing an import?

    Thanks
    Sue

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    This may be just me, but I wouldn't.

    I'd load the data as is (using bcp) to a staging table, and then write a udf a convert the data in the move to it's destination table.

    But that's just me

    MOO
    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
    Mar 2004
    Posts
    10
    well, this is data that will need to be updated daily. That is why I thought I could create a DTS package to do an import and then schedule the updates.

    This is getting way over my head.....

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well you could add the code as part of the transformation...but it'll slow things down...

    And if this is such old data, why is this more than a 1 time thing?
    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
    Mar 2004
    Posts
    10
    well that is my fault. i should have specified that the format is old, but the actual data is needed. the program is an actually written in clipper.

    I have never had to do this before. It is an interim solution. I can schedule it run after hours. So you are saying that I can just copy the Function Unpack Date into the transformation portion of my import?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm shooting from the hip here, but I think you can use
    PHP Code:
        DTSDestination("Dout") = #1947-06-26# + 256 * Asc(DTSSource("Din")) + Asc(Mid(DTSSource("Din"), 2, 1)) 
    to translate a DBF input column named "Din" to an SQL DATETIME output column named "Dout". It has been a while since I've needed this, so you need to carefully test this transformation!

    -PatP

  7. #7
    Join Date
    Mar 2004
    Posts
    10
    So replace "Din" with my dbf column name "datein"

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yep, and same with Dout for whatever output column name you want.

    -PatP

  9. #9
    Join Date
    Mar 2004
    Posts
    10
    Well I ran it and am getting a VBScript Runtime Error
    description: Invalid procedure call or argument: 'DTSDestination'

    on Line 11

  10. #10
    Join Date
    Mar 2004
    Posts
    10
    never mind the error message. my bad.

    i did get the first month. example 9/

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What are you using for the DTSDestination argument? It should be the name of the column in your output table, if I remember correctly.

    -PatP

  12. #12
    Join Date
    Mar 2004
    Posts
    10
    the name of my column, like you said. I am just getting the first 2 characters of the date and the slash. Example 9/

  13. #13
    Join Date
    Mar 2004
    Posts
    10
    thank you thank you thank you thank you!!!!!!

    it works great.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just be sure to beat the living daylights out of that expression in QA. I'm recalling something from memory (that was trivia even when it was fresh), from well over 16 months ago!

    -PatP

  15. #15
    Join Date
    Mar 2004
    Posts
    10
    I will!!!

Posting Permissions

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