SSIS Import Multiple Files Ignore blank lines

Question

I have the below Script Task in SSIS to import multiple text files into multiple tables. It creates the tables on the fly based on the text file names.

However, some of the text files have 1 or more blank lines at the end - how do I exclude these? The text files are coming from a 3rd party so I cannot resolve at source.

This is my code:

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

            string line1 = "";
            //Reading file names one by one
            string SourceDirectory = @"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
            // 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("\\", "")).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("|", "] NVARCHAR(500),[") + "] NVARCHAR(500))").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");


                    while ((line = SourceFile.ReadLine()) != null)
                    {
                        line = line.Trim();
                        if (line.Length > 0)
                        {
                            string query = "Insert into dbo." + filenameonly + "(" + columname + ") VALUES('" + line.Replace("|", "','") + "')";

                            SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);
                            myCommand1.ExecuteNonQuery();
                        }
                    }


                    }

                    counter++;

                }

                SourceFile.Close();
            }
            Dts.TaskResult = (int)ScriptResults.Success;
        }

Answer 1

I usually check if length of line > 0

            while ((line = SourceFile.ReadLine()) != null)
            {
                line = line.Trim();
                if (line.Length > 0)
                {
                    //put your code here
                }
            }