Multiple CSV files to Multiple tables with different schema in SSIS

Question

I have a Master table which has SourceName(Ex:- CSV File Name) and DestinationName(Ex:- Sql Table Name) as I know the source and destination; do we have any approach in SSIS to load files from source to destination dynamically. I don't want to create 50-60 data loads in SSIS Do we have any other better approach?

My Table looks something like this

Source     Destination
File1.csv  dbo.Table1
File2.csv  dbo.Table2
File3.csv  dbo.Table3

Note:- Schemas may differ for each files

Please suggest a best approach which saves a lot of effort

Answer 1

My apologies I misread the question originally I thought you wanted to dynamically EXPORT data from tables. I was starting to work on a solution to do this myself a while back but never completed it. I am leaving the below here in case you or anyone else comes here and wants a way to dynamically EXPORT data to csv files.

This is the code from my C# script task that will take a dataset you populate from a SQL task and output to an object data type. You can pass this any data set you want and it will dynamically create a .csv file for you. NOTE: You have to be careful on very large data sets because it can use up memory.

I have 2 files (though you can combine them into one if you want:

This works by passing it 2 variables in the SSIS ReadOnlyVariables: User::FileExportNameAndPath,User::ObjDataToSaveToExportFile

FileExportNameAndPath - Is the full path you want to export the file to with the file name/extension. You can use Expression Builder and other variables to create this to be whatever you want in a loop (grab your table name into a variable and parse a date to add that to your file name if you want).

ObjDataToSaveToExportFile is an object variable that you populate in previous step with Execte SQL task (whatever query and data you want to generate in your .csv file). On this step you have to have the output be variable, full resultset, and have the variable be: ObjDataToSaveToExportFile

If you use these same variable names you can just copy/paste this C# Script task across different packages without having to edit the script task at all.

This is code in file: ScriptMain.cs

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Text;
using System.Data.OleDb;    // this is to convert he object from SSIS to a data table
//using System.Text;

namespace ST_a8a7451a5662418eb87a1394e40bef29
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            bool IncludeHeaders = true;
            string FileNameAndPath = Dts.Variables["User::FileExportNameAndPath"].Value.ToString();
            
            

            // this gets the data object and sets ti to a data table
            OleDbDataAdapter A = new OleDbDataAdapter();
            System.Data.DataTable dt = new System.Data.DataTable();
            A.Fill(dt, Dts.Variables["User::ObjDataToSaveToExportFile"].Value);

            // for test data
            //DataTable sourceTable = GetTestData();
            DataTable sourceTable = dt;

            //using (StreamWriter writer = new StreamWriter("C:\\Temp\\dump.csv")) {
            using (StreamWriter writer = new StreamWriter(FileNameAndPath))
            {
                // this calls the class in another file
                ConvertToCSV.WriteDataTable(sourceTable, writer, IncludeHeaders);
            }
    

            Dts.TaskResult = (int)ScriptResults.Success;
        }// end main


        public DataTable GetTestData() 
        { 
            
            DataTable sourceTable = new DataTable();

            sourceTable.Columns.AddRange(new DataColumn[] {
                new DataColumn("ID", typeof(Guid)),
                new DataColumn("Date", typeof(DateTime)),
                new DataColumn("StringValue", typeof(string)),
                new DataColumn("NumberValue", typeof(int)),
                new DataColumn("BooleanValue", typeof(bool))
            });

            sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String1", 100, true);
            sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String2", 200, false);
            sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String3", 300, true);



            return sourceTable;
        }// end get teest data


        public static class Extensions
        {
            public static string ToCSV(DataTable table)
            {
                var result = new StringBuilder();
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    result.Append(table.Columns[i].ColumnName);
                    result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
                }

                foreach (DataRow row in table.Rows)
                {
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        result.Append(row[i].ToString());
                        result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
                    }
                }

                return result.ToString();
            }
        }



        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }// end class
}// end namespace

I have a second file in my project named: ConvertToCSV.cs and the code in there is:

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

namespace ST_a8a7451a5662418eb87a1394e40bef29
{
    class ConvertToCSV
    {

        public static void WriteDataTable(DataTable sourceTable, TextWriter writer, bool includeHeaders) 
        {
            if (includeHeaders) {
                IEnumerable<String> headerValues = sourceTable.Columns
                    .OfType<DataColumn>()
                    .Select(column => QuoteValue(column.ColumnName));
                
                writer.WriteLine(String.Join(",", headerValues));
            }

            IEnumerable<String> items = null;

            foreach (DataRow row in sourceTable.Rows) {
                items = row.ItemArray.Select(o => QuoteValue(o.ToString()));                
                writer.WriteLine(String.Join(",", items));
            }

            writer.Flush();
        }// end Write Data Table


        // this function adds quotes around the strings for text qualified values
        private static string QuoteValue(string value)
        {
            return String.Concat("\"",
            value.Replace("\"", "\"\""), "\"");
        }



    }
}

For the expression bulder for the FileNameAndPath You can use this in your variable and update the path/name as you wish and it will generate a path like this:

C:\Temp\FileExportForTesting\ExportFile_20240126_0830.csv

"C:\\Temp\\FileExportForTesting\\ExportFile_" + (DT_WSTR,4)YEAR(GETDATE()) +  RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + "_" + Right("0" + (DT_STR,2,1252) DatePart("hh",getdate()),2) +
Right("0" + (DT_STR,2,1252) DatePart("mi",getdate()),2) +
".csv"

Here is image of the SQL Task with how you have to set it up for full result set:

Then on the Result Set Tab it should look like this: