Hi, I am currently stuck in one issue during SSIS building for weeks, any comments/ideas will be very helpful, many thanks! I have got a group of zip files under one specific folder, inside every zip is a single csv file. All the csv files are in exactly the same format -- Same column name, same number of attributesetc. All the zip files are named with date, for example XXX03_02_2012.zip, XXX04_02_2012.zip. Also everyday there'r new zip files arrive in the same folder with the current date on the name. I am in a way of building SSIS to unzip the files (with encrypt password) using For each Loop and read the csv, eventually I will need to write all these csv files into SQL Server in a one big table. Also this SSIS will need to run daily to pickup new arrive data. Although I am not sure what will be the best way to just insert the new data into the database instead of reading the whole files. Is there any related example/tutorial online that can be helpful for such case? Also the number of attributes of each csv file is 1,605, which beyond the limit of SQL Server(1,024). Any ways to solve that? Regards stephen

the whole solution can be like this: use a foreach loop container and loop through files in the source folder then use a Execute Process Task inside the foreach loop container to unzip files, this is an example: http://www.ssisguru.com/2009/10/unzip-and-zip-files-using-ssis.html then use a data flow task to load unzipped csv files into sql server tabe with dynamic connection string as expression property of flat file connection manager, this is an example: http://beyondrelational.com/modules/2/blogs/106/posts/11113/ssis-dynamically-set-flat-file-connection-manager.aspx then you can use a file system task after the data flow task task inside the foreach loop container to archive processed files, this is an example: http://www.rafael-salas.com/2007/03/ssis-file-system-task-move-and-rename.htmlhttp://www.rad.pasfu.com

the whole solution can be like this: use a foreach loop container and loop through files in the source folder then use a Execute Process Task inside the foreach loop container to unzip files, this is an example: http://www.ssisguru.com/2009/10/unzip-and-zip-files-using-ssis.html then use a data flow task to load unzipped csv files into sql server tabe with dynamic connection string as expression property of flat file connection manager, this is an example: http://beyondrelational.com/modules/2/blogs/106/posts/11113/ssis-dynamically-set-flat-file-connection-manager.aspx then you can use a file system task after the data flow task task inside the foreach loop container to archive processed files, this is an example: http://www.rafael-salas.com/2007/03/ssis-file-system-task-move-and-rename.htmlhttp://www.rad.pasfu.com

Hi Reza, Many thanks for the solutions, i will try that with the example provided in ur thread. Have you got any ideas for how to read in the 1,605 fields as indicated in my thread? cheers

do you have data in all columns? actually limitation is that you can create more than 1024 columns, but you should not have value for more than 1024 of existing columns. and the reason is that limitation of data per each row in sql server tables is 8,019 byte: (look at definition of wide tables in below link) http://msdn.microsoft.com/en-us/library/ms186986%28SQL.105%29.aspxhttp://www.rad.pasfu.com

Hi, Would SSIS has limitation of columns when read in the file? If I can't write to SQL Server as a big one table for all these data, i might just break it into 2 or 3 seperate tables. However I still need SSIS to read in the 1,605 columns, not sure whether it got similar limitations. Many thanks. Stephen