December 20, 2017 at 1:33 pm
You've had no replies... probably because this isn't a SQL Server problem, per se... it's more of a problem with programming in C#. However, that said, some things that usually throw people for a loop when writing file I/O code in a procedural language are as follows:
1.) The incoming file's line ending characters are inconsistent or just not what was expected.
2.) The ReadLine function in either C# or VB expects a proper line ending, and will read data until it encounters one.
You should be able to take it from there...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 20, 2017 at 1:35 pm
And another thing.. If you use an IF statement where ReadLine is used, remember that it reads the line, and the line pointer will have moved, so you only get to test such a thing once.
You should instead always use ReadLine with a variable assignment instead, and then test the variable value.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 4, 2018 at 8:41 am
Hello, Posting the solution. Hope this can help future questions related with this topic:
public void Main()
{
try
{
string SourceFolderPath = Dts.Variables["User::SourceFolderPath"].Value.ToString();
string DestinationFolderPath = Dts.Variables["User::DestinationFolderPath"].Value.ToString();
string FileName = Dts.Variables["User::FileName"].Value.ToString();
//string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();
//string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
String[] myArrColumns = new string[1];
String[] myArrRecords= new string[1];
int i = 0; int z = 0; int ColArrayNum = 0; int SplitedWordsCounter = 0; int RecordArrayNum = 0; int CountColumns = 0;
String Filenameonly2 = "";
string SourceDirectory = SourceFolderPath;
//string[] fileEntries = Directory.GetFiles(SourceDirectory, "*");// + FileExtension);
//foreach (string fileName in fileEntries)
//{
System.IO.StreamReader file = new System.IO.StreamReader(FileName);
string[] lines = System.IO.File.ReadAllLines(FileName);
for (int l = 0; l < lines.Length; l++)
{
if (lines[l].Substring(0) == "*")
{
i++;
}
else if (lines[l].Substring(0) != "*" && i == 1 && z == 0)
{
Filenameonly2 = lines[l].Replace(" ", "()").Replace(")(", "").Replace("()", " ");
Filenameonly2 = Filenameonly2.Replace(" ", "_");
Filenameonly2 = Filenameonly2.Replace("#_", "");
z++;
}
else if (lines[l].Substring(0) != "*" && i == 1 && z == 1)
{
string ColumnName = lines[l].Substring(39, 31).Replace(" ", "");
if (myArrColumns.Length - 1 < ColArrayNum)
{
Array.Resize(ref myArrColumns, myArrColumns.Length + 1);
}
myArrColumns[ColArrayNum] = ColumnName.ToString();
ColArrayNum = ColArrayNum + 1;
}
else if (i == 2 && lines[l].StartsWith("|"))
{
lines[l] = lines[l].Substring(1);
string[] words = lines[l].Split('|');
RecordArrayNum = SplitedWordsCounter;
SplitedWordsCounter = SplitedWordsCounter + words.Length;
if (myArrRecords.Length < SplitedWordsCounter)
{
Array.Resize(ref myArrRecords, SplitedWordsCounter);
}
for (int e = 0; e < words.Length; e++)
{
myArrRecords[RecordArrayNum] = words[e];
RecordArrayNum ++;
}
}
else if (lines[l].Substring(0) == "#EOD")
{
File.Delete(DestinationFolderPath + "\\" + Filenameonly2 + ".csv");
string FilePath = DestinationFolderPath + "\\" + Filenameonly2 + ".csv";
int ArrayCount = myArrColumns.Length;
int ArrayCount2 = myArrRecords.Length;
StreamWriter sw = new StreamWriter(FilePath, true);
for (int j = 0; j < ArrayCount; j++)
{
string columns = myArrColumns[j];
sw.Write(columns);
if (j < ArrayCount - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
for (int ir = 0; ir < ArrayCount2; ir++)
{
string rows = myArrRecords[ir];
sw.Write(rows);
if (((ir < ArrayCount2) && ((ir + 1) % ArrayCount) != 0) || ir == 0)
{
sw.Write(",");
}
else
{
sw.Write(sw.NewLine);
}
}
sw.Close();
i = -1; z = 0; ColArrayNum = 0; SplitedWordsCounter = 0; RecordArrayNum = 0; CountColumns = 0;
Array.Resize(ref myArrColumns, 0); Array.Resize(ref myArrRecords, 0);
}
}
file.Close();
//}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception exception)
{
using (StreamWriter sw = File.CreateText(Dts.Variables["User::DestinationFolderPath"].Value.ToString()
+ "\\Error\\" + "ErrorLog_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".log"))
{
sw.WriteLine(exception.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
#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
}
}
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply