Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple flat files to import into multiple tables (different formats) Expand / Collapse
Author
Message
Posted Monday, August 31, 2009 1:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 9:36 AM
Points: 1, Visits: 34
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

Post #780164
Posted Monday, December 16, 2013 9:48 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, April 3, 2014 1:32 PM
Points: 689, Visits: 331
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?
Post #1523322
Posted Tuesday, December 17, 2013 12:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
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




  • Regards/Raunak
    Now a member of Linkedin

    Please visit the all new Performance Point Forum
    Please visit the all new Data Mining and Business Analytics Forum
    Post #1523551
    Posted Thursday, December 19, 2013 3:16 AM


    SSCarpal Tunnel

    SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

    Group: General Forum Members
    Last Login: Today @ 3:02 AM
    Points: 4,983, Visits: 11,678
    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.



    Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

    When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
    Post #1524502
    Posted Wednesday, March 19, 2014 6:05 AM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Thursday, March 27, 2014 10:01 PM
    Points: 13, Visits: 80
    Hello Sirisha,

    Did you find the solution for this?
    Please let me know the approach you used.

    Thanks in advance
    Post #1552582
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse