I'm new to SSIS 2008, and I'm attempting to import a series of CSV files into a database. I want to loop over every CSV file in a specified folder and import each file into a different table that must be created on the fly (name of table should be base name of file, i.e. with path and extension stripped off). The file structures are similar, but not identical, so the single target table solution won't work. Using an example I found here: http://microsoft-ssis.blogspot.com/2011/02/how-to-configure-foreach-loop-file.html I'm able to successfully load all files into ONE table, but I need to load each file into a separate table. Can anyone provide some assistance on exactly how to modify the project to allow for a table to be created, on the fly, for each source file? thanks, Mark

How are you able to load all the files into one table when structure of all the files is not same?Nitesh Rai- Please mark the post as answered if it answers your question

Obviously you need the name of the table to become equal to the name of the file you are processing and this you can do using an expression, so you capture your file name and assign it to the package variable. From there you need an Execute SQL Task to create the table which input parameter becomes that variable for the table. The tricky part is to insert the data each time to a new table, the standard DFT would not work as you cannot manipulate on the OpenRowset object, so you can either use a Script Task to code that in or a bulk insert procedure, depends what you are more comfortable with.Do you need help with any of the items?Arthur My Blog

I asked myself the same question, but since I don't have much experience with SSIS, am not sure how its handling it. It appears to just be shifting columns around. For example, in the first few thousand rows, the "status" column in the target table contains the correct information. Then starting with the 1001 row, it contains the "unique_id" value instead. Since I originally created the target table with all varchar(100) columns, it's just stuffing the data in to the fields without complaint, I guess. -Mark

Arthur, Yes, I could definitely use some help with all of the above. I'm already using one Global variable (FilePath) which contains the full path of the file currently being processed in the loop. The value of this Variable is assigned in the "Variable Mappings" of the "Foreach Loop Editor", and is assigned in the Expression property of the source file Connection definition. It's just not clear to me where or how an additional variable (TargetTable) can be captured and set to the current file name (without path or extension), and used as input to the procedure that will create the table. Any guidance you can provide, with as much detail as possible, will be much appreciated. Thanks, Mark

You can use "variable as expression". Example of an Expression to get File Name from File Path http://beyondrelational.com/blogs/sudeep/archive/2010/11/23/ssis-expression-to-get-file-name-from-file-path.aspxArthur My Blog

You can't use a dataflow for this, as the dataflow in SSIS is strongly typed about its metadata. Aka you just can't change metadata dynamically (at least not out-of-the-box). I would use a script task to read the .csv files and push them to SQL Server using .NET, but that is just personal preference.MCTS, MCITP - Please mark posts as answered where appropriate.

Hello Mark, Here is blog post that i wrote while ago , Load each file to different table, Table name will be same as filename , Table will be created on fly and loaded. http://sqlage.blogspot.com/2011/02/create-table-for-each-flate-file-and.html You might want to change the datalength of each column, i have VARCHAR(100).. have a look. Thanks http://sqlage.blogspot.com/

Hi, Since you can load all files into ONE table, may I suggest you make a column which contains the csv filename? This ONE table will serve as a staging table. Then design a loop process through all distinct values of this 'filename' column, and create the table with this filename and fan out (I mean, move) records that comes from this filename into that newly created table. Hope this helps. ~ J.

Aamir, This looks like what I'm looking for. I will give this a try today and report back. Thanks, Mark

AAmir, Finally got a chance to try your SSIS script and was not successful with it. I'm getting a syntax checker warning: "ScriptMain is not CLS-compliant because it derives from 'VSTARTScriptObjectModelBase', which is not CLS-compliant." When I try to execute the package the "Execution Results" tab shows: Error: Cannot load script for execution. Task Script Task failed I tried to find info on these errors and found mention of the fact that this may be a script that will work in VS2005, but not VS2008 (which is the version that I'm running). Any ideas? Thanks!

Aamir, I finally got your script working! I had to completely change the way the SQLConnection was being created, but it works. The problem, however, turns out to be the speed. Your method inserts into the database row-by-row, which was just taking forever. However, I learned quite a bit from your script and wanted to thank you for that. I have a new problem now, related to the fact that my CSV files have different column layouts and the Data Flow Task apparently can not handle that... but I've started a new thread for that issue. Thanks again, Mark

Here is a cheap and cheerful way to imort a lot of CSVs into SQL Server as a one-off (e.g. for analysis). They can be imported into Access (any version)n using the macro below (change the file path) and then if you like save it off as Access 2003 and import it into SQL Server using DTS/SSIS. Clunky but quick. Sub mcrLinkCSV() Set db = CurrentDb strDir = "C:\CSV\" strTemp = Dir(strDir & "*.csv*") Do While Len(strTemp) strFileName = strTemp strTblName = Left(strTemp, Len(strTemp) - 4) strPathAndFilename = strDir + strFileName DoCmd.TransferText acImportDelim, , strTblName, strPathAndFilename, True strTemp = Dir$() Loop End Sub