locked
How to pass flat file names dynamically ??? RRS feed

  • Question

  • I have multiple files that I need to load in to seperate target tables. I'm using Flat file source and selecting the associated file. My lead wants me to pass the file name dynamically with out hardcoding that file name. Is there a way to do that?
    Tuesday, March 9, 2010 7:29 PM

Answers

  • If you are send the data from multiple files to single table you could check my blog.
    In case you want to send the data from multiple files to separate tables then we need to have more info as to how are you deciding which file data goes to which table and related stuff.

    Sudeep Raj's Blog
    Tuesday, March 9, 2010 8:00 PM

All replies

  • If you are send the data from multiple files to single table you could check my blog.
    In case you want to send the data from multiple files to separate tables then we need to have more info as to how are you deciding which file data goes to which table and related stuff.

    Sudeep Raj's Blog
    Tuesday, March 9, 2010 8:00 PM
  • My team manager asked my advice on this. I m thinking of having a Configuration table where in map the file name to the target table name. I don't know how to work this out in SSIS but it's just a thought that sprouted. Anyone is most welcome to advice me on their views ?
    Tuesday, March 9, 2010 9:19 PM
  • Is your file names and the target tables fixed??
    I Assume you have 10 files and 10 tables to which they will be mapped. So I will have a table in with the above data in it.
    If so you could have a look at dynamic flat file connection. Then in the DFT set up the OLED Destination with the Data Access Mode : Table or View from Variable and map the variable from the Foreach loop that you get for the table to be mapped.

    Let us know the whole requirement in order to provide a better approach.

    Sudeep's Blog
    Tuesday, March 9, 2010 9:44 PM

  • download the sample package as template.
    this will loop through all the files in a folder and then archives it.

    http://www.calsql.com/2009/10/ssis-archive-and-rename-all-files-in.html
    Please mark the post as answered to help others to choose the best.
    chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)
    Tuesday, March 9, 2010 10:01 PM
  • Here is what the requirement is -

    File Names and Target table names are fixed. Each file is associated to a target table.

    1) I have to check for the existence of files in the Root directory ( AllSystem folder) under which there are sub folders X and Y to hold there respective files. SSIS Package should first check these sub folders and then if any file is existing then it should read the Configuration table to determine the target table for loading. Once the SSIS Package reads the Configuration table, it should load each file to it's associated table.

    Please suggest me a better approach to design this package.
    Wednesday, March 10, 2010 6:29 PM
  • Please suggest me a better approach to design this package.
    Better approach?? 
    Sudeep's Blog
    Wednesday, March 10, 2010 8:43 PM
  • I mean, Can you suggest me an approach to handle this?

    Thursday, March 11, 2010 3:54 PM
  • Did you check my post earlier in this thread?
    Sudeep's Blog
    Thursday, March 11, 2010 3:59 PM
  • Oh sorry. I did not check that. Thanks for your info. I will try that way.
    Thursday, March 11, 2010 4:06 PM