Post reply

SSIS Use filename to determine table to load

  • November 27, 2009 at 7:53 pm

    #143264

    So, I have a location where a customer will drop 5 files to load in to a database. I can iterate over the files and get the filenames (Using the for each container). However, I don't know how to now use the filename to determine which table to load.

    Say the filename is xxxx.account.data - therefore I want to load data in the file to the account table in my database. Is it possible to set up a database destination and have the table it loads to as a variable?

    Is there an easy way to do this in SSIS?

    Thanks,

    Dan

  • November 30, 2009 at 8:40 am

    #1085453

    Presumably you are loading the file name into a variable from the For Each? (Use Variable Mappings with Index 0).

    Then you'll need to work out the table name from the file name; you'll probably need a short script task to do that. Put the table name into a second variable, then in your data flow you can load from the file and store into the table.

    When you're testing this, set it up for one file with the actual file and table names. When that's working, use Expressions to set the names from the variables, or use 'Table name variable' option in the OLE DB destination.

    This all assumes the processing is the same for each table. If not, your data flow may get quite complicated! I know, because mine has 😉

  • November 30, 2009 at 8:49 am

    #1085463

    David has it right I was just going to point out that you can can evaluate the value of the destination table on the Precedence Constraint (the arrow between the objects in the control flow) to evaluate to an expression which would test the file name variable prior to running. This way each path can be very different but not affect the other paths.

    Alan

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

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