Post reply

Multiple flat files to import into multiple tables (different formats)

  • August 31, 2009 at 1:03 pm

    #138523

    I have about 30 flat files each in different formats with different column names.

    I need to import them creating new tables respectively. I am open to let it pick the datatypes.

    I just need them to get into the database.

    Bulk insert doesn't work for me because it inserts into the same table.

    I would have to create a new connection for each file to create a table so I wasn't sure if SSIS for each container could do any good.

    we get many files from clients throughout the year and so far they were scattered around the year so import wizard or SSIS dataflow task with OLEDB conn worked.

    These days I am on a tight deadline with these files so i need a quicker and automated way to do it.

    Even a batch file would work. Please help!

    Thanks,

    Sirisha

  • December 16, 2013 at 9:48 am

    #1673776

    Hello Sirisha,

    Did you find the solution for this?

    I m planning to do the same with my current requirement. But I am dealing with only 3 file formats Text, excel and ASC respectively. So far I am just using import wizard, But requested to automate this.

    Note: I really don't need to run a bunch per day, The task will be once a week.

    Can you guide me if you already has the solution?

  • December 17, 2013 at 12:27 am

    #1673963

    When you say that you have different file formats and file structures, you can leverage the following to design a SSIS package:

  • Create Dynamic File Connection Manager for all files
  • Use a For Each Loop to enumerate the file system directory to pick up the file and load the file onto the tables
  • Include a cleanup task to remove the file after successful load operation

    • Raunak J

  • December 19, 2013 at 3:16 am

    #1674748

    Raunak Jhawar (12/17/2013)

    When you say that you have different file formats and file structures, you can leverage the following to design a SSIS package:

  • Create Dynamic File Connection Manager for all files
  • Use a For Each Loop to enumerate the file system directory to pick up the file and load the file onto the tables
  • Include a cleanup task to remove the file after successful load operation

    • You are responding to a forum newbie and these are high-level ideas which require some elucidation, in my opinion.

    I am no newbie, but still don't have a clue how would create a "Dynamic File Connection Manager for all files" - what is this thing?

    You mention the use of a FEL, but do not mention data flows at all. Perhaps you have a single 'Dynamic Data Flow for all files' in mind - if so, I'd like to know more please.

    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

  • March 19, 2014 at 6:05 am

    #1698342

    Hello Sirisha,

    Did you find the solution for this?

    Please let me know the approach you used.

    Thanks in advance

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

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