vb.net SSIS DTS 脚本任务在用户代码中遇到异常

SSIS DTS Script Task has encountered an exception in user code

提问by user2673722

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:

我正在尝试在 Visual Basic 2010 中使用脚本组件执行 SSIS 包。执行包时出现以下错误：

    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!

谢谢！

回答by Raj More

Here's how you debug scripts in SSIS

以下是在 SSIS 中调试脚本的方法

• With the code open, put a breakpoint
• Close the code
• Run the package
• When the script starts running, it will open up a code window and you can walk through the code step by step

• 打开代码，放一个断点
• 关闭代码
• 运行包
• 当脚本开始运行时，它会打开一个代码窗口，您可以一步一步地浏览代码