I need to import multiple CSV files into separate SQL Server tables. The layout of the files are similar but different. I have created an SSIS package (SQL Server 2008) that is made up of a Script Task and a Data Flow Task inside a Foreach Loop. The Foreach Loop loops through all the files, the Script Task reads the column headers and drops/creates staging tables to receive the data. Global variables control the settings on the SourceConnectionFlatFile and DestinationConnectionOLEDB objects. Everything works smoothly for the first file, but when it attempts to load the second file, the Data Flow Task assumes that every file will have the same layout as the first file, and complains when it does not. I cannot find any way to programatically refresh the column layout (as you would do manually when using the Import Data wizard, by just clicking on the "Columns" item). Can anyone help with this? Thanks, Mark

If structure of data in each file different ( as I found in your description they have different structure ), you can not use Data flow task with dynamic metadata, actually data flow task doesn't support dynamic metadata. better solution is to create a BCP command dynamically with expressions and execute it with execute sql task. this is a sample of BCP Command : bcp mydb.dbo.sales in sales.csv /U /P /Ssales_fs /c /t, reference: http://www.cryer.co.uk/brian/sqlserver/howtoimportcsv.htm also you can find more about BCP here. I think you know how to use expression to make dynamic bcp command and execute it with execute sql task. but you can ask me where you need more details. http://www.rad.pasfu.com

Reza, Thanks for your solution. Yes, I think I can figure out how to use Expressions to build and execute the dynamic bcp command in an Execute SQL task. The real trick is making sure I have all the correct switches set on the BCP command! I will try this today and see how it works. Regards, Mark

Reza, Works great. Thank you. -Mark