Dynamically create tables from csv files looping through folder

  • I have a dilema and suspect it is not solvable through SSIS built-in toolbox facilities. I would like to build a Foreach loop that loops through a directory containing multiple csv files. Within each file (name of file is tablename.csv) the first record contains the names of the columns for the table. The real issue is that columns can change in each table with each receipt of a replacement CSV. (files are used to create a backup copy of the database on a server that does not have access to the production from which the csvs are created). So a drop of each table is the first step in the process.

    Therefore what I need to be able to do is programatically loop through the directory/folder reading the CSVs then create and load the tables based on each csv's contents. This would seem like a natural function for SSIS but I see no way of doing it. Does SSIS really only allow fixed, manually created flat file imports whereby connectors MUST be created one by one and mappings/connectors are only good as long as table columns never change? I suspect yes but since I am new to SISS I wanted to confirm as I cannot find anything on the web that indicates on the fly dynamic imports can be accomplished.

  • How about:

    1. Use an Execute SQL Task to run a bulk insert to read the first line (column headers) into a #temp table (1 column).

    2. Prepare a variable to build the table.

    3. Use an Execute SQL Task to build the table off of the variable.

    4. Use an Execute SQL Task to bulk insert the file, starting at line 2.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Wayne,

    Yes, I think the right way to go, I was tossing around the bulk insert but formulating the overall process was still in the thought stage. However I do believe you have confirmed what I'm now disappointed to find out, that there isn't a really quick, set up and run way of doing this. I guess I understand why it's not there since this is ultimately (at least seems to me) an extension of .net framework. So up until now there's no runtime facility to handle the slightly more complex programatic builds of file connectors. Maybe that's coming in 2011 when VS2010 [maybe] gets support for SSIS.

    Thanks,

    Alan

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

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