Post reply

Importing Flat File with no Key

  • January 6, 2004 at 11:22 pm

    #108639

    I have a flat file I need to import into a table.  A file is produced for each machine on our network and reports information on Machine Name, Hardware specifics, installed programs etc.

    I need a way of creating a relationship between the Machine and what is on it.  I have created tables for Programs, Hardware, Licenses etc but because there is no key I can use in the file, the only way think I can track what programs are on what machine is if I have a ReportID field and for each file loaded have it set to a value for all rows for that import.  Then for the next file load increment this value by one.

    I would then have a lookup table where I could see where MachineName = ReportId.

    The problem is .... how can I do this?  Also, if anyone has any better ideas on how to handle this problem I'd be grateful for any suggestions.

    Thanks

    Angela

  • January 6, 2004 at 11:48 pm

    #488891

    How is the file created?

    You might be able to use the filename as your key and add it in as an ActiveX Script transformation.

    --------------------
    Colt 45 - the original point and click interface

  • January 7, 2004 at 4:28 pm

    #489064

    The file is created by a thrid party app.  The name of the file is <MachineName>_<UserName>  So I suppose I could use this, but I didn't want to use a varchar as a key, hence my idea to use a Reportid.  How would I add it into the table using ActiveX?

    Thanks

    Angela

  • January 7, 2004 at 5:08 pm

    #489065

    Firstly you would store the file name in a global variable. Is this package loading multiple files or just one file?

    For the transformation click the destination column and create a new ActiveX Script transformation. Your script will look something like this,

    Function Main()

     DTSDestination("ID") = DTSGlobalVariables("FileName").Value

     Main = DTSTransformStat_OK

    End Function

    As for using a varchar for the key, well beggars can't be choosers. If you weren't using a third party app, you could have modified the output file to have an integer ID

    --------------------
    Colt 45 - the original point and click interface

  • January 7, 2004 at 5:16 pm

    #489066

    Hi Phill,

    There will be almost 100 different files each with a different file name containing about 80 rows each.

    How can I pass through the name of the file as a global variable when it will always be different?  They will all be stored in the one directory though.

    Thanks for your help.

    Angela

  • January 7, 2004 at 5:22 pm

    #489068

    Does the one package import all these files?

    Take a look at the following article

    http://www.sqldts.com/default.aspx?246

    The filename is assigned to a global variable in step 4 of the example code. You might want to break the machine name and user name into seperate variables. Could be useful later if you wanted to do machine or user level reporting.

    --------------------
    Colt 45 - the original point and click interface

  • January 7, 2004 at 5:25 pm

    #489069

    Thanks Phill.  I will have a look at this article and see how I go.

  • January 8, 2004 at 6:42 am

    #489120

    I would preprocess the files first before loading into sql.

    Use DOS command

    findstr /i /r "." \folder\* > \difffolder\newfile.txt

    this will produce single file containing each line of all the files with the filename at the beginning of the line followed by a colon.

    load \difffolder\newfile.txt (dts, bcp) into staging table (with IDENTITY column if required) and parse into relevant tables.

    with a bit of work could create a dts package to do it all.

     

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply