Post reply

Question about Inserting formatted files in SQL Server DB

  • November 15, 2010 at 3:18 pm

    #242898

    Hello All,

    I have a question that you guys may be able to help.

    There will be some text files in "C:\SomeFolder\" which need to imported into database every day.

    The file name convention is 'File-ddmmyynnn-inv.csv' where dd=day, mm=month, yy=year, nnn= numbers. Files will be stored regularly every day to the source folder and I need to import them on to the SQL Server db. So I need to process just todays files (ignoring all others).

    I can use 'bulk insert' statement to insert a single file but I wanted to create a stored procedure and schedule it to process those files everyday.

    your help is much appreciated.

    Regards,

    Rookie

  • November 15, 2010 at 3:37 pm

    #1251040

    Hi

    You could create a SSIS package that you can schedule to run when needed

    Scott

    Scott
    I am a Senior Data Analyst/DBA
    I work with MS SQL, Oracle & several ETL Tools

    @thewolf66[/url]

  • November 15, 2010 at 3:54 pm

    #1251046

    Thanks for your reply Scott.

    I have been trying that too... but I got stuck in reading the files using ForEach File enumeration as I only need to pick today's files.

    As a newbie I am finding it bit hard to write expressions to pick the right files.

    Regards,

    Rookie

  • November 15, 2010 at 6:27 pm

    #1251067

    I think you will want to add this expression to the filespec property:

    "File-" + RIGHT("00"+ (DT_STR, 2, 1252) DAY( GETDATE() ), 2) +

    RIGHT("00"+ (DT_STR, 2, 1252) MONTH( GETDATE() ), 2) +

    RIGHT( (DT_STR, 4, 1252) YEAR( GETDATE() ), 2) + "*-inv.csv"

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • November 16, 2010 at 4:17 am

    #1251213

    I am sorry to say that I do not us SSIS that much, all of my ETL work is done with a product from Pervasive called cosmos

    I know that SSIS can do this I am just not the right guy to give you the answer

    Scott

    Scott
    I am a Senior Data Analyst/DBA
    I work with MS SQL, Oracle & several ETL Tools

    @thewolf66[/url]

  • November 16, 2010 at 12:27 pm

    #1251543

    Thanks a lot Wayne. That's worked like a charm!!!

Viewing 6 posts - 1 through 5 (of 5 total)

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