Post reply

Automated Importing of a csv file into SQL 2005

  • August 8, 2008 at 6:21 am

    #382203

    Hi guys.

    I am new to SQL 2005. My boss has just asked me to create an automated process that will load data from a csv file everyday at midnight.

    Can this be done on SQL 2005 - as a SQL 2005 configuration perhaps?

    Please help!

  • August 8, 2008 at 10:06 am

    #855350

    well, without much insight onto the nature of the csv file to load, what you are asking for can be really simple.

    setup a flat file connection to point to the csv to connect to and an ole db connection for your sql server. create a data flow with a flat file source and ole db destination to point to your sql server and connect the to mapping the columns from the flat file to the sql server table

    after you load the data, you will want to archive the file, this can be as simple as moving the file to an archive folder

    as far as the timing, that can be done via a sql job to kick off at your designed time (midnight as you say)

    this is a start, they are probably data validations you want to consider and so forth but the basics are above

    hth

  • August 8, 2008 at 4:10 pm

    #855571

    you can deploy the package as a job using Sql Agent and set the job to run at a specific time

    i hope this link helps

    http://www.mssqltips.com/tip.asp?tip=1180

    Regards
    Venkat
    http://sqlblogging.blogspot.com[/url]

  • August 10, 2008 at 5:15 am

    #855740

    Thanks guys - you're the best. Did as you said and sure enough my project is working!

    Thanks again!

  • August 10, 2008 at 1:35 pm

    #855792

    But HOW do you change the NAME of the file dynamically in the connection? I know where the file is but not the name until it appears in the source file AND there may be more than one that needs to be processed. I have the archiving part working but have been beating my head against this one thing.

    Thanks,

    Joe B

    ps, sorry if I'm hijacking this thread but I need to have this working by tomorrow and it looks like this is an active thread

  • August 10, 2008 at 1:46 pm

    #855794

    Joe

    On my corporate Blog, I have a blog detailing how to loop through a collection of files and load them into sql server database without knowing the names of the files (just the structure)

    The blog can be found here: http://www.rdabiblog.com/

    The article is under SSIS Dynamic File Name Load

    hth

  • August 10, 2008 at 1:47 pm

    #855795

    Thanks Chuck. On my way there.

    Joe B

  • August 10, 2008 at 1:58 pm

    #855797

    Wow. That's really weird!!! I saw that earlier today and tried it and it wouldn't work. In the meantime I had been trying other things and after your post back I went and tried it again. Evidently I screwed something up the first time because it worked like a charm this time.

    Thanks again,

    Joe B

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

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