Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78

    Unanswered: How do you validate and scrub using DTS?

    When I use the DTS GUI and insert a "Bulk Insert Task" the main tab says:

    "Import text files into SQL Server. You cannot validate, scrub, or transform data using this task".

    So my question is, what shoud you use to validate and scrub?

    In particular I have fixed-format text file with some occasional bad records (e.g. wrong length, empty record). What should I be using? If you suggest vbscript could you show me some examples? I'm new to vbscript.

    Thanks!

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Does a little challenge scare you away? Check MSDN, BOL, Technet, Google if nothing else. See if you can find something on accessing text files using a scripting language (any.) OK, a hint, - FileSystemObject, look for it.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You can use a sproc or script to audit/cleanse the data, then parse it out in to the final destination table...works nic for fixed width...


    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Datarow varchar(8000))
    GO
    
    DECLARE @cmd varchar(8000), @User sysname, @Pwd sysname
    
    SELECT @User = '', @Pwd = ''
    
    SELECT @cmd = 'bcp Northwind.dbo.myTable99 IN c:\setup.log -c -S'+@@SERVERNAME+' -U'+@User+' -P'+@Pwd
    
    EXEC master..xp_cmdshell @cmd 
    GO
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO
    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.

Posting Permissions

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