Post reply

Import multiple csv files to multiple sql tables

  • August 5, 2009 at 6:07 am

    #224593

    Hi

    I am using VS2005 with SQL Server 2005.

    I have recently started using SSIS. I know the basics. My current problem is that I need to import 18 different CSV files into 18 sql tables. Does anyone know how I can do this in SSIS dynamically.

    I have an idea to read a table which contains all the csv filenames and the destination table names into recordsets. By using looping and variables I could update the connection managers, but this doesn't update the underlying meta data.

    Can anyone help, I need this urgently for a project I'm working on currently.

    Thanks in advance.

    Suhel

  • August 5, 2009 at 6:38 am

    #1034727

    Inelegant as it may seem, your fastest solution is to create 18 'hard-coded' data flows.

    I know, I wouldn't like it either :w00t:

    An alternative is to build the whole thing in a Script task - you'll get the dynamism you want, but lose out on performance and (possibly) ease of maintenance.

    It may be possible to do all the meta data stuff in code, but if your project is as urgent as you suggest, you don't have time - it's not easy.

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 5, 2009 at 7:55 am

    #1034781

    Hi,

    If this is for a one off import then a dynamic solution may save you time, however if you are going to be using this import regularly then you should spend the time and create the dataflows for all 18 tables. Then you can take advantage of error handling, more complex logic and the improved performance.

    Here[/url] is how you can do it dynamically using Bulk Insert and SSIS, if you do decide to go down that route.

    HTH

    Kindest Regards,

    Frank Bazan

  • August 5, 2009 at 8:36 am

    #1034828

    Guys

    Thanks very much for your posts, they have helped me alot in understanding SSIS and resolve my issue.

    Suhel

  • August 6, 2009 at 8:38 am

    #1035415

    Suhel,

    We have just released beta with an enhancement, which might help. We have improved the CozyRoc Data Flow Task Plus to support dynamic data flows. We will be happy to hear your feedback.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

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

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