Search code examples
ssisforeach-loop-containerscript-task

SSIS Script Task does not work on second iteration of ForEach Loop Container


This is my first post on Stack Overflow and it surely will not be the last one. I had a very rough week trying to fix a problem with my SSIS Script Task in SSDT for VS2015.
The problem is the following:
I have a ForEach Loop Container in the Control Flow. I map a variable USER::FileName. This Variable I do use to process an excel file (replace commas with dots) in a Script Task and afterwards save the processed file and convert it to a flat file in data flow task. So far so good. But on the second loop of the ForEach Loop Container the original excel file is not processed properly. The names are set correctly but the commas are not replaced. I don't know what to try anymore. Maybe someone has any suggestion?

enter image description here

Edit 2: This is how the Script Task Code looks like: 

using Excel = Microsoft.Office.Interop.Excel;
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

    public void Main()
    {
        Excel._Application oApp = new Excel.Application();
        Excel.Workbook oWorkbook = oApp.Workbooks.Open(Dts.Variables["User::SourcePath"].Value.ToString() + "\\" + Dts.Variables["User::FileName"].Value.ToString() + ".xls");
        Excel.Worksheet oWorksheet = oWorkbook.Worksheets[1];
        try
        {
            ChangeValues(oWorksheet);
            oWorkbook.SaveAs("C:\\TEMP\\" + Dts.Variables["User::FileName"].Value.ToString() + ".xls");
            oWorkbook.Close();
            oApp.Quit();
            oWorksheet = null;
            oWorkbook = null;
            oApp = null;

            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();

            Dts.TaskResult = (int)ScriptResults.Success;
        } 
        catch (Exception e)
        {
            Dts.Events.FireError(0, "Script task", e.Message + "\r" + e.StackTrace, String.Empty, 0);
        }
    }

    private static void ChangeValues(Excel.Worksheet oWorksheet)
    {
        Excel.Range range = oWorksheet.UsedRange;
        int colNo = range.Columns.Count;
        int rowNo = range.Rows.Count;
        // read the values into an array.
        object[,] cells = range.Value;
            for (int j = 1; j <= colNo; j++)
            {
                for (int i = 1; i <= rowNo; i++)
                {
                    if (j > 3 && cells[i, j] != null)
                    {
                        cells[i, j] = cells[i, j].ToString().Replace(",", ".");
                    }
                }
            }
            // set the values back into the range.
            range.Value = cells;
            return;
        }
 }
Solution

  • Ok, I know this Post is a bit old but I found a solution for the problem a few weeks later and wanted to share it realy quickly with you guys. To debug the code I had to install Windows 10 and VS2017 on a VM because the Visual Studio Tools for Application (VSTA) Debugger that runs Script Tasks in SSIS does not work on VS2015 and SSDT is not available for VS2017 on Windows 7. So I was able to debug the Script on Windows 10 in VS2017 and found out that my code didn't open the correct excel sheet. Hope it might help anyone else with problems debugging SSIS Script Tasks.