Importing CSV file into SQLServer and create table dynamically

Create a script task in SSIS and edit that script and paste the following code.

/*

   Microsoft SQL Server Integration Services Script Task

   Write scripts using Microsoft Visual C# 2008.

   The ScriptMain is the entry point class of the script.

*/

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using System.IO;

using System.Data.SqlClient;

namespace ST_f8e01f62d1ea477ca1a1d9370108e8ad.csproj

{

    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]

    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    {

        #region VSTA generated code

        enum ScriptResults

        {

            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

        };

        #endregion

        /*

            The execution engine calls this method when the task executes.

            To access the object model, use the Dts property. Connections, variables, events,

            and logging features are available as members of the Dts property as shown in the following examples.

            To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;

            To post a log entry, call Dts.Log("This is my log text", 999, null);

            To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

            To use the connections collection use something like the following:

            ConnectionManager cm = Dts.Connections.Add("OLEDB");

            cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

            Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

           

            To open Help, press F1.

      */

        //public void Main()

        //{

        //    SqlConnection myADONETConnection = new SqlConnection();

        //    myADONETConnection = (SqlConnection)(Dts.Connections["Test1"].AcquireConnection(Dts.Transaction) as SqlConnection);

        //    MessageBox.Show(myADONETConnection.ConnectionString, "Test1");

        //    string line1 = "";//Reading file names one by one

        //    string SourceDirectory = @"D:\Project Documents\iCIS Enhancements\Damco\Oem Customization\HDE\HDE_iCIS_Historical_Data\";// TODO: Add your code

        //    string[] fileEntries = Directory.GetFiles(SourceDirectory);

        //    foreach (string fileName in fileEntries)

        //    {// do something with fileNameMessageBox.Show(fileName);

        //         string columname = "";//Reading first line of each file and assign to variable

        //        System.IO.StreamReader file2 = new System.IO.StreamReader(fileName);

        //        string filenameonly = (((fileName.Replace(SourceDirectory, "")).Replace(".csv", "")).Replace("\\", ""));

        //        line1 = (" IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]." + filenameonly + "') AND type in (N'U')) DROP TABLE [dbo]." + filenameonly + " Create Table dbo." + filenameonly + "(" + file2.ReadLine().Replace(",", " VARCHAR(100),") + " VARCHAR(100))").Replace(".txt", ""); file2.Close();

        //        //MessageBox.Show(line1.ToString());

        //        SqlCommand myCommand = new SqlCommand(line1, myADONETConnection);

        //        myCommand.ExecuteNonQuery();

        //        //line1 = "BULK INSERT " + filenameonly + " FROM '" + fileName + "' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\\n' ) ";

        //        //SqlCommand myCommand1 = new SqlCommand(line1, myADONETConnection);

        //        //myCommand1.ExecuteNonQuery();

        //        //MessageBox.Show("TABLE IS CREATED");//Writing Data of File Into Table

        //        int counter = 0;

        //        string line;

        //        System.IO.StreamReader SourceFile =new System.IO.StreamReader(fileName);

        //        while ((line = SourceFile.ReadLine()) != null)

        //        {

        //                if (counter == 0){

        //                    columname = line.ToString();

        //                    //MessageBox.Show("INside IF");

        //                }

        //                else{

        //                    //MessageBox.Show("Inside ELSE");

        //                    string query = "Insert into dbo." + filenameonly + "(" + columname + ") VALUES('" + line.Replace(",", "','") + "')";

        //                    //MessageBox.Show(query.ToString());

        //                    SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);

        //                    myCommand1.ExecuteNonQuery();

        //                }

        //            counter++;

        //            }

        //            SourceFile.Close();

        //        }

        //        Dts.TaskResult = (int)ScriptResults.Success;

        //    }

        public void Main()

        {

            try

            {

                // TODO: Add your code here

                SqlConnection myADONETConnection = new SqlConnection();

                myADONETConnection = (SqlConnection)(Dts.Connections["Test1"].AcquireConnection(Dts.Transaction) as SqlConnection);

                //MessageBox.Show(myADONETConnection.ConnectionString, "Test1");

                string line1 = "";//Reading file names one by one

                string filenameonly1 = "TempCSVParticipation";

                string SourceDirectory = Dts.Variables["mFilePath"].Value.ToString();//+ @"D:\Share\HBFR\Extracts\201201\Participation\";// TODO: Add your code

                MessageBox.Show(SourceDirectory);

                //string SourceDirectory = @"D:\Share\HBFR\Extracts\201201\Participation\";// TODO: Add your code

                string[] fileEntries = Directory.GetFiles(SourceDirectory);

                

                foreach (string fileName in fileEntries)

                {

                    //MessageBox.Show(fileName.ToString());

                    if (fileName.ToString().Contains("Actual"))

                    {

                        System.IO.StreamReader file2 = new System.IO.StreamReader(fileName);

                        string filenameonly = (((fileName.Replace(SourceDirectory, "")).Replace(".csv", "")).Replace("\\", ""));

                        line1 = (" IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]." + filenameonly1 + "') AND type in (N'U')) DROP TABLE [dbo]." + filenameonly1 + " Create Table dbo." + filenameonly1 + "([" + file2.ReadLine().Replace(",", "] NVARCHAR(4000), [") + "] NVARCHAR(4000))").Replace(".txt", ""); file2.Close();

                        SqlCommand myCommand = new SqlCommand(line1, myADONETConnection);

                        myCommand.ExecuteNonQuery();

                        line1 = "BULK INSERT " + filenameonly1 + " FROM '" + fileName + "' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\\n' ) ";

                        SqlCommand myCommand1 = new SqlCommand(line1, myADONETConnection);

                        myCommand1.CommandTimeout = 0;

                        myCommand1.ExecuteNonQuery();

                        //MessageBox.Show(fileName.ToString() + " Completed");

                    }

                }

                Dts.TaskResult = (int)ScriptResults.Success;

            }

            catch (Exception e)

            {

                MessageBox.Show(e.Message);

            }

        }

        }

    }