I'm trying to execute an SSIS package with a scripting component in Visual Basic 2010. I get the following error when I execute the package:
public void Main()
{
// TODO: Custom Code starts here
/*
* Description: Reads the input CMI Stats files and converts into a more readable format
* This Code for Better CMI Parser is converted as per SC's original code by S.A. on 3/6/2014
* Here is the description from original procedure
* CustType = DOMESTIC/INTERNATIONAL/ETC
* CategoryType = SBU/MAN
* Category = Actual value (AI/CC/etc)
* DataType = INCOMING or SHIP (or something else later?)
*
* 3/23/2010
* Uncommented the CAD file load....
*/
string[,] filesToProcess = new string[2, 2] { {(String)Dts.Variables["csvFileNameUSD"].Value,"USD" }, {(String)Dts.Variables["csvFileNameCAD"].Value,"CAD" } };
string headline = "CustType,CategoryType,CategoryValue,DataType,Stock QTY,Stock Value,Floor QTY,Floor Value,Order Count,Currency";
string outPutFile = Dts.Variables["outputFile"].Value.ToString();
//Declare Output files to write to
FileStream sw = new System.IO.FileStream(outPutFile, System.IO.FileMode.Create);
StreamWriter w = new StreamWriter(sw);
w.WriteLine(headline);
//Loop Through the files one by one and write to output Files
for (int x = 0; x < filesToProcess.GetLength(1); x++)
{
if (System.IO.File.Exists(filesToProcess[x, 0]))
{
string categoryType = "";
string custType = "";
string dataType = "";
string categoryValue = "";
//Read the input file in memory and close after done
StreamReader sr = new StreamReader(filesToProcess[x, 0]);
string fileText = sr.ReadToEnd();
string[] lines = fileText.Split(Convert.ToString(System.Environment.NewLine).ToCharArray());
sr.Close();
//Read String line by line and write the lines with params from sub headers
foreach (string line in lines)
{
if (line.Split(',').Length > 3)
{
string lineWrite = "";
lineWrite = line;
string[] cols = line.Split(',');
if (HeaderLine(cols[1]))
{
string[] llist = cols[0].Split();
categoryType = llist[llist.Length - 1];
custType = llist[0];
dataType = llist[1];
if (dataType == "COMPANY")
{
custType = llist[0] + " " + llist[1];
dataType = llist[2];
}
}
if (cols[0].Contains("GRAND"))
{
categoryValue = "Total";
}
else
{
string[] col0 = cols[0].Split(' ');
categoryValue = col0[col0.Length - 1];
}
int z = 0;
string[] vals = new string[cols.Length];
for (int i = 1; i < cols.Length - 1; i++)
{
vals[z] = cols[i].Replace(',', ' ');
z++;
}
//line = ",".join([CustType, CategoryType, CategoryValue, DataType, vals[0], vals[1], vals[2], vals[3], vals[6], currency])
lineWrite = clean(custType) + "," + clean(categoryType) + "," + clean(categoryValue) + ","
+ clean(dataType) + "," + clean(vals[0]) + "," + clean(vals[1]) + "," + clean(vals[2])
+ "," + clean(vals[3]) + "," + clean(vals[6]) + "," + filesToProcess[x, 1];
if (!HeaderLine(line))
{
w.WriteLine(lineWrite);
w.Flush();
}
}
}
}
}
w.Close();
sw.Close();
//Custom Code ends here
Dts.TaskResult = (int)ScriptResults.Success;
}
public bool HeaderLine(String line)
{
return line.Contains("Stock Qty");
}
public string clean(string str)
{
if (str != null)
return Regex.Replace(str,@"[""]","");
//return str.Replace('"', ' ');
else
return "";
}
#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
}
}
Can anyone suggest what could have possibly gone wrong or maybe how to debug this code in order to understand the errors?
Thanks!
