0

I want to know, how to implement the text qualifier while importing using script task. Below is my code. I have been only able to use delimiter and not text qualifier. So, I am also getting double quotes loaded in my table.

        public void Main()
    {
        // TODO: Add your code here
        string SourceFolderPath = Dts.Variables["User::SourceFolder"].Value.ToString();
        string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();
        string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
        string TableName = Dts.Variables["User::DestinationTable"].Value.ToString();


        SqlConnection myADONETConnection = new SqlConnection();
        myADONETConnection = (SqlConnection)
        (Dts.Connections["PEGASUS.AdventureWorks1"].AcquireConnection(Dts.Transaction) as SqlConnection);

       string[] fileEntries = Directory.GetFiles(SourceFolderPath, "*" + FileExtension);
            foreach (string fileName in fileEntries)
            {
                int counter = 0;
                string line;
                string ColumnList="";
                MessageBox.Show(fileName);

                System.IO.StreamReader SourceFile = new System.IO.StreamReader(fileName);
                while ((line = SourceFile.ReadLine()) != null)
                {
                    if (counter == 0)
                    {
                        ColumnList = "[" +(line.Replace(FileDelimiter, "],[").Replace("\"", ""))+ "]";
                        MessageBox.Show(ColumnList.ToString());
                    }
                    else
                    {
                        MessageBox.Show("pass 2");
                        string query = "Insert into " + TableName + " (" + ColumnList + ") ";
                        query += "VALUES('" + line.Replace(FileDelimiter, "','") + "')";
                        MessageBox.Show("pass 3");

                        //MessageBox.Show(query.ToString());
                        SqlCommand cmd = new SqlCommand(query, myADONETConnection);
                        cmd.ExecuteNonQuery();
                        MessageBox.Show("pass 4");
                    }

                    counter++;
                }

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

CSV input This is expected output

above are the input and expected output.

Atul Shubhankar
  • 43
  • 1
  • 6
  • You need to include an example of the CSV file and what the output looks like using that code in your question – Mazhar Oct 03 '18 at 10:47
  • 4
    Why don't you use the a Dataflow task using a Flat File Source with different delimeters? It's definitely faster than this code, as it streams data from the source to the target and uses bulk insert operations to insert data to the database tables instead of executing INSERTs one by one – Panagiotis Kanavos Oct 03 '18 at 11:21
  • 2
    If you want to load multiple files, put the Dataflow task in a `Foreach Loop Container`. This is described [here](https://www.mssqltips.com/sqlservertip/2874/loop-through-flat-files-in-sql-server-integration-services/) – Panagiotis Kanavos Oct 03 '18 at 11:21
  • This script task loads data into columns even if the column sequence is changed in the csv file. I cannot use the For Each Loop Container if the column sequence is changed, it will load data in wrong columns. I just need to implement the text qualifier in the script task. – Atul Shubhankar Oct 03 '18 at 12:17
  • I'd follow the advice in the linked post to create a copy of the flat file, replacing the embedded text delimiters. Then use your script task, removing the new text delimiters with `REPLACE`. https://visakhm.blogspot.com/2014/06/ssis-tips-handling-embedded-text.html – digital.aaron Oct 03 '18 at 17:00
  • 1
    check the answer here: https://stackoverflow.com/questions/6542996/how-to-split-csv-whose-columns-may-contain You can use Microsoft.VisualBasic.FileIO.TextFieldParser class and it will handle that. You'll need to add a reference to Microsoft.VisualBasic in your script task. – Tim Mylott Oct 03 '18 at 17:26
  • 1
    i googled this regex (?:^|,)(?=[^"]|(")?)"?((?(1)[^"]*|[^,"]*))"?(?=,|$) – KeithL Oct 03 '18 at 19:15

0 Answers0