Problem creating file exactly as I want it

Question

Hi everybody,

I'm trying to generate a simple text file with added footer.

Here is what I managed to generate now which is not what I want:

source|request_type|change_type|exclusion_reason|provider_type|social_group|status|effective_date|earliest_effective_date|change_date|term_date|latest_term_date|tin|npi|provider_id|provider_last_name|provider_first_name|provider_middle_initial|clinic_name|t50_specialty|t50_provider_type|t15_taxonomy|license_number|license_state|license_issue_date|svc_address_line1|svc_address_line2|svc_city|svc_state|svc_zip|bus_address_line1|bus_address_line2|bus_city|bus_state|bus_zip|sub_specialty|"source"|"request_type"|"change_type"|"exclusion_reason"|"provider_type"|"social_group"|"status"|"effective_date"|"earliest_effective_date"|"change_date"|"term_date"|"latest_term_date"|"tin"|"npi"|"provider_id"|"provider_last_name"|"provider_first_name"|"provider_middle_initial"|"clinic_name"|"t50_specialty"|"t50_provider_type"|"t15_taxonomy"|"license_number"|"license_state"|"license_issue_date"|"svc_address_line1"|"svc_address_line2"|"svc_city"|"svc_state"|"svc_zip"|"bus_address_line1"|"bus_address_line2"|"bus_city"|"bus_state"|"bus_zip"|"sub_specialty"

In other words, after the header line ending with sub_specialty there is another line with header row starting and I don't want it, I want my header line to not have " around each column definition.

Here is how I try to define my Control Flow:

Here is how the first DataFlow task looks like

These are properties of the destination task:

And this is what I get when I click on connection manager:

There is also a script task to add footer to the file. This works well.

So, my main issue that after normal header which I defined in the Header property by directly placing the header row with delimiters into this editbox, I got headers also autogenerated with quotes around them and I obviously don't want that.

Just in case this is the code for adding footer row:

 public void Main()
        {
            Dts.TaskResult = (int)ScriptResults.Success;
            try
            {
             
            int rowCount = (int)Dts.Variables["User::rowsCount"].Value;
            var outFile = Dts.Variables["User::outpuDailyFile"].Value.ToString();
            var fileText = AddHeaderAndFooter.GetFileText(outFile);
            var footerText = "FOOTER|" + rowCount.ToString("0000000000") + "||||||||||||||||||||||||||||||||||" ;
             //   var headerText = "source|request_type|change_type|exclusion_reason|provider_type|social_group|status|effective_date|earliest_effective_date|change_date|term_date|latest_term_date|tin|npi|provider_id|provider_last_name|provider_first_name|provider_middle_initial|clinic_name|t50_specialty|t50_provider_type|t15_taxonomy|license_number|license_state|license_issue_date|svc_address_line1|svc_address_line2|svc_city|svc_state|svc_zip|bus_address_line1|bus_address_line2|bus_city|bus_state|bus_zip|sub_specialty";
            var fullText =  fileText  + footerText;
            AddHeaderAndFooter.WriteToFile(outFile, fullText);
             }
            catch (Exception ex)
            {
                Dts.Events.FireError(0, "ERROR", ex.Message, null, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }
        public static class AddHeaderAndFooter
        {
            public static int CountRecords(string filePath)
            {
                return (File.ReadAllLines(filePath).Length + 2);
            }
            public static string GetFileText(string filePath)
            {
                var sr = new StreamReader(filePath, Encoding.Default);
                var recs = sr.ReadToEnd();
                sr.Close();
                return recs;
            }
            public static void WriteToFile(string filePath, string fileText)
            {
                var sw = new StreamWriter(filePath, false);
                sw.Write(fileText, Encoding.ASCII);
                sw.Close();
            }
        }
        #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

So, what should I do to get rid of this extra header output?

Thanks a lot in advance

Answer 1

I was able to overcome my problems with the project and produced the file, but I removed the text qualifier. Hopefully this would be acceptable.