How to create tables for multiple pipe delimited files and load them dynamically to those tables in SSIS Package


Scenario:

Let's say that we get 100 pipe delimited files and we want to load all of them to different sql server tables. Each of the file has different meta data or column information. 
We want to build an SSIS Package, that can read the column name from the file and then create table and load the data to it and it should repeat for all 100 files. 

Solution:

To perform this we need to follow below steps.
1. Create an SSIS Package in SQL Server Integration Services Project
2. Create ADO.Net Connection Manager in Connection Managers pane. I have named it ADOAudit. You can name anything but you have to make change in below code accordingly.You will be pointing to the database in which you want to create the tables and load the files.
3. Bring the Script Task to Control Flow pane. Open it and go to Edit Script and paste the below script in it.
4.Change the path of folder in which your files exists for SourceDirectory variable in below code.Save the script and exit the editor. You are done and your SSIS Package is ready to read text files and create table for each of them and load them dynamically in SQL Server Integration Services.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;


namespace ST_da3127cebb85407989456cc583a3f9e4.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

 

        public void Main()
        {


            SqlConnection myADONETConnection = new SqlConnection();
            myADONETConnection = (SqlConnection)(Dts.Connections["ADOAUDIT"].AcquireConnection(Dts.Transaction) as SqlConnection);
            MessageBox.Show(myADONETConnection.ConnectionString, "ADOAUDIT");


            string line1 = "";
            //Reading file names one by one
            string SourceDirectory = @"C:\SourceFolder\";
            // TODO: Add your code here
            string[] fileEntries = Directory.GetFiles(SourceDirectory);
            foreach (string fileName in fileEntries)
            {
                // do something with fileName
                MessageBox.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(".txt", "")).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();

                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();
                        columname = "[" + columname.Replace("|", "],[") + "]";
                        MessageBox.Show(columname);
                        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;
        }
    }
}

4 comments:

  1. Hi, I am getting error in Script for this .... please help

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

    ReplyDelete
  2. Positive site, where did u come up with the information on this posting? I'm pleased I discovered it though, ill be checking back soon to find out what additional posts you include. Glendale CA plumbers

    ReplyDelete
  3. Further, the plumber ought to have on stand by or late night plumbers accessible to fix your plumbing crisis.
    piping

    ReplyDelete
  4. The circumstance where you face crisis plumbing can be any time or night, it tends to be on ends of the week or any open occasions, this is where your typical plumber won't direct you by any means as they don't manage complex issues.
    Plumbers

    ReplyDelete