Results 1 to 14 of 14

Thread: SQL Server 2000 DTS Issuelet

  1. #1
    Sublime HEXUS.net
    Join Date
    Jul 2003
    Location
    The Void.. Floating
    Posts
    11,819
    Thanks
    213
    Thanked
    233 times in 160 posts
    • Stoo's system
      • Motherboard:
      • Mac Pro
      • CPU:
      • 2*Xeon 5450 @ 2.8GHz, 12MB Cache
      • Memory:
      • 32GB 1600MHz FBDIMM
      • Storage:
      • ~ 2.5TB + 4TB external array
      • Graphics card(s):
      • ATI Radeon HD 4870
      • Case:
      • Mac Pro
      • Operating System:
      • OS X 10.7
      • Monitor(s):
      • 24" Samsung 244T Black
      • Internet:
      • Zen Max Pro

    Question SQL Server 2000 DTS Issuelet

    This is a bit on the weird side..

    I've got a DTS package which imports a text file and builds a table with it all standard stuff, but for some reason it skips the final record, and I have no idea why..

    If I duplicate the final row, or add another crlf (blank record in effect) the row in question will import, so I know there is nothing wrong with the data in the row.

    I can get around it by adding in a blank record at the end of the data, but I'd like to keep the data as clean as possible.

    Has anyone come across anything like this before?
    (\__/)
    (='.'=)
    (")_(")

  2. #2
    Administrator Moby-Dick's Avatar
    Join Date
    Jul 2003
    Location
    There's no place like ::1 (IPv6 version)
    Posts
    10,665
    Thanks
    53
    Thanked
    385 times in 314 posts
    sounds like its only inserting the last processed record at the beginning of the next one.

    While I've just had a weeks course telling me how fantastic they are , our developers are a little more sceptical of them.
    could it be that you've told it that there are ( for example ) 100 rows to import , but your text file has 101 ( due to colum names ? ) so that the cursor only imports 99 rows of data ?
    my Virtualisation Blog http://jfvi.co.uk Virtualisation Podcast http://vsoup.net

  3. #3
    Sublime HEXUS.net
    Join Date
    Jul 2003
    Location
    The Void.. Floating
    Posts
    11,819
    Thanks
    213
    Thanked
    233 times in 160 posts
    • Stoo's system
      • Motherboard:
      • Mac Pro
      • CPU:
      • 2*Xeon 5450 @ 2.8GHz, 12MB Cache
      • Memory:
      • 32GB 1600MHz FBDIMM
      • Storage:
      • ~ 2.5TB + 4TB external array
      • Graphics card(s):
      • ATI Radeon HD 4870
      • Case:
      • Mac Pro
      • Operating System:
      • OS X 10.7
      • Monitor(s):
      • 24" Samsung 244T Black
      • Internet:
      • Zen Max Pro
    Hmm, that could be part of it, there are 1269 rows, with the first being the column names, but I've not actually told it how many rows there are, that's left to the DTS package to sort out.. which of course is probably the issue..
    (\__/)
    (='.'=)
    (")_(")

  4. #4
    Administrator Moby-Dick's Avatar
    Join Date
    Jul 2003
    Location
    There's no place like ::1 (IPv6 version)
    Posts
    10,665
    Thanks
    53
    Thanked
    385 times in 314 posts
    there are other ways to import a text file

    how much manipulation are you doing before it gets inserted ?
    my Virtualisation Blog http://jfvi.co.uk Virtualisation Podcast http://vsoup.net

  5. #5
    TiG
    TiG is offline
    Walk a mile in other peoples shoes...
    Join Date
    Jul 2003
    Location
    Questioning it all
    Posts
    6,213
    Thanks
    45
    Thanked
    48 times in 43 posts
    Stoo,

    If you want to check this enable the exception log and you should be able to see why.
    It sounds like you are missing a CR LF at the end of the file. If you open up the text file and put the cursor into the bottom of the text file does it appear on the line below the final set of data or not?.

    DTS packages are good for some things but not others. DTS interestingly enough isn't in SQL2005 apart from it being deemed "Legacy compliance"

    p.s if the data isn't sensitive i'd happily try it for you here.

    TiG
    -- Hexus Meets Rock! --

  6. #6
    Sublime HEXUS.net
    Join Date
    Jul 2003
    Location
    The Void.. Floating
    Posts
    11,819
    Thanks
    213
    Thanked
    233 times in 160 posts
    • Stoo's system
      • Motherboard:
      • Mac Pro
      • CPU:
      • 2*Xeon 5450 @ 2.8GHz, 12MB Cache
      • Memory:
      • 32GB 1600MHz FBDIMM
      • Storage:
      • ~ 2.5TB + 4TB external array
      • Graphics card(s):
      • ATI Radeon HD 4870
      • Case:
      • Mac Pro
      • Operating System:
      • OS X 10.7
      • Monitor(s):
      • 24" Samsung 244T Black
      • Internet:
      • Zen Max Pro
    TiG: Already thought of that chap:

    Execution Started: 18/11/2005 15:24:36

    @@LogSourceRows: D:\CMS\site-errors.txt.Source
    @@LogDestRows: D:\CMS\site-errors.txt.Dest

    @@ExecutionCompleted

    Execution Completed: 18/11/2005 15:24:38

    *********************************************

    No errors reported..

    It sounds like it's missing a cr lf, but it's not (the cursor appears below the final data line)
    If I specifically add in another one, it does work, but then I get a row with a bunch of nulls as well..


    Moby: pretty much nothing..

    BTW, this is a mac formatted text file (CR instead of CRLF, but I've already told that to the DTS routine).


    Edit: Data file here..
    Last edited by Stoo; 18-11-2005 at 04:31 PM.
    (\__/)
    (='.'=)
    (")_(")

  7. #7
    Administrator Moby-Dick's Avatar
    Join Date
    Jul 2003
    Location
    There's no place like ::1 (IPv6 version)
    Posts
    10,665
    Thanks
    53
    Thanked
    385 times in 314 posts
    Yep - they have SSIS ? ( SQL server Integration Services ) which is much nicer from what I've seen.
    my Virtualisation Blog http://jfvi.co.uk Virtualisation Podcast http://vsoup.net

  8. #8
    Administrator Moby-Dick's Avatar
    Join Date
    Jul 2003
    Location
    There's no place like ::1 (IPv6 version)
    Posts
    10,665
    Thanks
    53
    Thanked
    385 times in 314 posts
    stoo , what about using Bulk Insert or bcp to import the file ?
    my Virtualisation Blog http://jfvi.co.uk Virtualisation Podcast http://vsoup.net

  9. #9
    Sublime HEXUS.net
    Join Date
    Jul 2003
    Location
    The Void.. Floating
    Posts
    11,819
    Thanks
    213
    Thanked
    233 times in 160 posts
    • Stoo's system
      • Motherboard:
      • Mac Pro
      • CPU:
      • 2*Xeon 5450 @ 2.8GHz, 12MB Cache
      • Memory:
      • 32GB 1600MHz FBDIMM
      • Storage:
      • ~ 2.5TB + 4TB external array
      • Graphics card(s):
      • ATI Radeon HD 4870
      • Case:
      • Mac Pro
      • Operating System:
      • OS X 10.7
      • Monitor(s):
      • 24" Samsung 244T Black
      • Internet:
      • Zen Max Pro
    Hadn't thought of that Moby.. I've been using DTS processes to do just about everything lol..
    (\__/)
    (='.'=)
    (")_(")

  10. #10
    TiG
    TiG is offline
    Walk a mile in other peoples shoes...
    Join Date
    Jul 2003
    Location
    Questioning it all
    Posts
    6,213
    Thanks
    45
    Thanked
    48 times in 43 posts
    Stoo, not knowing a great deal about MAC's, do they store the txt file in something other than ANSI?. IF the EOF marker is being interpreted badly?

    Does it inform you of 1269 rows imported if you run it manually without the CR LF and 1271 with the extra CR LF?.

    TiG
    -- Hexus Meets Rock! --

  11. #11
    TiG
    TiG is offline
    Walk a mile in other peoples shoes...
    Join Date
    Jul 2003
    Location
    Questioning it all
    Posts
    6,213
    Thanks
    45
    Thanked
    48 times in 43 posts
    Quote Originally Posted by Moby-Dick
    stoo , what about using Bulk Insert or bcp to import the file ?
    NO NO NO NO NO!.

    NO...

    NOOOOOO....

    TiG
    -- Hexus Meets Rock! --

  12. #12
    Administrator Moby-Dick's Avatar
    Join Date
    Jul 2003
    Location
    There's no place like ::1 (IPv6 version)
    Posts
    10,665
    Thanks
    53
    Thanked
    385 times in 314 posts
    lol , not a big fan of them TiG ?

    oddly enough our devs dont like any of them and just write their own little applets run feed applications.
    my Virtualisation Blog http://jfvi.co.uk Virtualisation Podcast http://vsoup.net

  13. #13
    TiG
    TiG is offline
    Walk a mile in other peoples shoes...
    Join Date
    Jul 2003
    Location
    Questioning it all
    Posts
    6,213
    Thanks
    45
    Thanked
    48 times in 43 posts
    BCP should have been committed to the grave with SQL Server 6.5. Bulk insert isn't that bad unless of course you write it badly and it goes tits up and i've seen instances where other people have done things like that linked to triggers. Bulk insert is not imo the right way to bring data into a database.

    Structured import routines during sensible hours of lower DB load, with good reporting to ensure the data is structured and integral as you import it have to be part of good database design.

    Plus strictly if you have a well designed database with foreign keys installed, and referencal integrity enabled, its much less likely that things like bulk insert or bcp will actually provide what you need.

    DTS has good features for complete on success/fail/completion etc which can enable jobs to control what happens.

    Been playing with SQL 2005 since official release on Nov7th and have to say that i'm very impressed (but it is bloatware, server footprint is huge compared to previous versions)

    TiG
    -- Hexus Meets Rock! --

  14. #14
    Sublime HEXUS.net
    Join Date
    Jul 2003
    Location
    The Void.. Floating
    Posts
    11,819
    Thanks
    213
    Thanked
    233 times in 160 posts
    • Stoo's system
      • Motherboard:
      • Mac Pro
      • CPU:
      • 2*Xeon 5450 @ 2.8GHz, 12MB Cache
      • Memory:
      • 32GB 1600MHz FBDIMM
      • Storage:
      • ~ 2.5TB + 4TB external array
      • Graphics card(s):
      • ATI Radeon HD 4870
      • Case:
      • Mac Pro
      • Operating System:
      • OS X 10.7
      • Monitor(s):
      • 24" Samsung 244T Black
      • Internet:
      • Zen Max Pro
    ahhhhhh

    the extra CR causes a null line, but a windows CRLF lets it work properly.. 1271 lines with 1268 data rows imported..

    *scratches head*

    I guess I'll need to process the import texts to add in a windows CR LF to get it to import all the rows, despite me telling it that the end of record delimiter is CR instead of CR LF..

    The good(?) news is that we're getting a dedicated SQL Server 2005 machine rather than 2000 on a dev-box..
    (\__/)
    (='.'=)
    (")_(")

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. MS launches SQL Server 2005, Visual Studio
    By Steve in forum HEXUS News
    Replies: 4
    Last Post: 07-11-2005, 12:33 PM
  2. SQL Server 2000
    By Fatboy in forum Software
    Replies: 37
    Last Post: 17-08-2005, 07:05 PM
  3. SQL Server 2000 SP3 Problems
    By Vini in forum Software
    Replies: 3
    Last Post: 17-08-2005, 10:52 AM
  4. SQL Server Connection string?
    By Stoo in forum Software
    Replies: 7
    Last Post: 04-05-2005, 05:48 PM
  5. New Windows Updates out peeps
    By Skii in forum Software
    Replies: 10
    Last Post: 04-08-2004, 06:19 AM

Posting Permissions

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