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.
Problem creating file exactly as I want it
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