Post reply

Automated CSV XLS Import into DW

  • October 21, 2007 at 1:34 pm

    #153906

    Hi guys,

    I'm pretty new in SQL Server and trying to build up a BI Solution for my firm. We have got monthly data about revenue headcounts and so, which should be integrated into a DW.

    I was wondering whether there is an automated way to import this csv data in SQL like in a batch, because the users are not familiar with SQL Server at all. The structure of the data will be the same all the time, since it comes from legacy systems.

    YOur help would be highly appriciated:)

  • October 22, 2007 at 1:42 am

    #744021

    Since your looking for a batch file to perform the Data load i think the way to go would be using the BCP command. go to command prompt and type in bcp /? for the arguments that need to be passed.

    Personally I prefer a SSIS package as it provide more flexibility.

    Jayanth Kurup[/url]

  • October 22, 2007 at 5:14 am

    #744071

    Hi 4h-foursome!

    I agree with 'jayanth...', use SSIS!

    a) A very simple solution to 'automate' your csv-import could go like this:

    - setup a ScheduledTask, that runs once/day(overhead neglectable) doing:

    - executes a tiny 'GetRevenueFile.cmd' file that:

    1. looks for the 'Revenue.csv' file in a certain folder on the server

    2. if the file exists, excutes the SSIS-package

    3. move the prcoessed file to some 'archive' folder

    b) If the revenue data gets stored in some 'staging table', after running the SSIS-package, then

    your next step is loading the DW?

    To do this, maybe consider using SSAS?

    SSAS = Analysis Services = Microsofts tool for loading data-wharehouses.

    c) Finally, your users run their reports against the DW

    Regards,

    BF-Sweden

Viewing 3 posts - 1 through 2 (of 2 total)

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