ssis - load multiple csv with different structure to sql server

Question

I have multiple CSV files (more than 60) with different structure (different number of columns, different columns names) and I want to load them into SQL Server tables.

• Each CSV file will be load to different table.
• The SSIS process needs to create the table automatically with the CSV structure and load the CSV data into the table.

How can I do this?

Answer 1

You "could" try the following.

Create a Staging Table with 1 Column to store the data. VARCHAR(MAX) + 1 col to store the Filename

Then Set up you SSIS to load each row into this column (no Column delimiter) you will prob need to load the Header as a data row too to get the col names.

Then you can ForEach Loop into this Table

Finally you would write a Stored Proc that Parses the Comma Separated Column Names and Dynamically create the destination Tables and also parse the Values and Load them.

Not the most performant way of loading data but if your file sizes are small then you should be ok...

Answer 2

You can do this with a Script Task within a Foreach Loop of the file enumerator type. Note that this example is meant to send data to staging tables, as each column is defined with as VARCHAR(250). You may need to adjust the length, 250 was just used for testing purposes. The tables created are named after the files the data comes from, and you'll want to be sure that no tables with these names already exist otherwise they'll be dropped. If you want this to fail when a table with the same name already exists remove the first SqlCommand.ExecuteNonQuery() call, which is what performs this step. In the Foreach Loop, add a variable at index 0 to hold the file name, then add this variable in the ReadOnlyVariables field of the Script Task. This variable is VariableWithFilePath in the example below.

using System.Data.SqlClient;
using System.IO;
using System.Collections.Generic;
using System.Linq;


string connstr = @"Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=SSPI;";

//get file path
string fullFileName = Dts.Variables["User::VariableWithFilePath"].Value.ToString();

//get only file name to be used when creating table
string fileName = Path.GetFileNameWithoutExtension(fullFileName);

DataTable dt = new DataTable();
using (StreamReader sr = new StreamReader(fullFileName))
{
    List<string> colNames = new List<string>();

    string firstLine = sr.ReadLine();
    string[] headers = firstLine.Split(',');

    foreach (string h in headers)
    {
        dt.Columns.Add(h);
        colNames.Add(h);
    }

    int columnCount = headers.Count();
    string line = sr.ReadLine();

    while (line != null)
    {
        string[] fields = line.Split(',');

        int currentLength = fields.Count();
        if (currentLength < columnCount)
        {
            //make sure fields from each row are kept together 
            while (currentLength < columnCount)
            {
                line += sr.ReadLine();
                currentLength = line.Split(',').Count();
            }
            fields = line.Split(',');
        }

        //load data table
        dt.Rows.Add(fields);
        line = sr.ReadLine();
    }
    string columns = string.Join(" VARCHAR(250), ", colNames);

    //command to drop table if it already exist
    string dropDDL = "IF (OBJECT_ID(N'DBO." + fileName + "') IS NOT NULL) DROP TABLE DBO." + fileName;

    //command to create new with same name as file
    string createDDL = "CREATE TABLE DBO." + fileName + " ( " + columns + " VARCHAR(250) )";

    using (SqlConnection conn = new SqlConnection(connstr))
    {
        SqlCommand sql = new SqlCommand();
        sql.Connection = conn;
        sql.CommandText = dropDDL;

        //drop table if exists
        conn.Open();
        sql.ExecuteNonQuery();

        //create table
        sql.CommandText = createDDL;
        sql.ExecuteNonQuery();

        //load SQL Server table from data table
        using (SqlBulkCopy blkCpy = new SqlBulkCopy(conn))
        {
            blkCpy.DestinationTableName = fileName;
            blkCpy.WriteToServer(dt);
        }
    }

}