Importing multiple flat files to multiple tables in SSIS
I have a couple of hundred flat files to importinto database tables using SSIS. The files can be divided into groups by the format they use.I understand that I could import each group of files that have a common format at the same time using a Foreach Loop Container. However, the example for the Foreach Loop Container has multiple files all being imported into the same database table. In my case, each file needs to be imported into a different database table. Is it possible to import each set of files with the same format into different tables in a simple loop? I can't see a way to makea Data Flow Destination itemaccept its table name dynamically, which seems to prevent me doing this. I suppose I could make a different Data Flow Destination item for each file, in the Data Flow. Would that be a reasonable solution, or is there a simpler solution, or should I just resign myself to making a separate Data Flow for every single file?
June 27th, 2006 4:03am

In an OLEDB destination you can select to use a "Table or view name from variable" -in this way the destination can be dynamic.
Free Windows Admin Tool Kit Click here and download it now
June 27th, 2006 6:24am

Thanks, I missed that.
June 27th, 2006 6:30am

Any suggestions on the best way to assign the destinationtable name variable? I imagine could do it in a Script item in the Foreach Loop before the Data Flow item is executed, but is there somewhereto do it more in-line, like building it into the Data Flow item's properties somehow?
Free Windows Admin Tool Kit Click here and download it now
June 27th, 2006 7:30am

Use some logic to derive the table name based on the format, which I guess must be derivable from the filename? You have the filename, so how do you expect to transform this into a table name. I would expect an expression to be used somewhere, the tablename variable for example, but you may need to refer to some logic table. an Execute SQL Task inside the loop could query a SQL table that gave you the destination table from a filename. The Exec SQL Task result could then be assigned to the tablename variable.
June 27th, 2006 10:10am

I'm doing the same thing but the problem I'm runnining into is some of the columns in the txt files are wider than 50. Is there a way to change the default value in the Flat File Connection or possibly change the value from a script task?
Free Windows Admin Tool Kit Click here and download it now
November 18th, 2011 10:11am

Hi, You can use the Multi Flat files source to do that, check on this tip on how to do that with the resource link available. Hope this will be useful http://beyondrelational.com/justlearned/posts/783/import-multiple-files-in-ssis-using-multi-flat-file.aspxThanks Karthikeyan Anbarasan http://f5debug.net/
November 22nd, 2011 8:19am

Also check this thread and the approach provided. Hope this gives you some idea. http://beyondrelational.com/ask/sudeep/questions/926/ssis-multiple-filetxtcsvxls-loading-foreach-loop.aspxThanks Karthikeyan Anbarasan http://f5debug.net/
Free Windows Admin Tool Kit Click here and download it now
November 22nd, 2011 8:19am

Thanks for responding. I didn't use the multi flat file source since the files are unique. Thanks again for any help!
November 22nd, 2011 12:59pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics