CODEWITHSUNDEEP
c#ssis
Dizzy49
Dizzy49

Reputation: 1520

SSIS: Same Script to create folders. Different Results?

I have an SSIS package that does some looping and creates directories on the fly. I have 5 loops, so I have a copy of the script in each loop.

This is in my first loop:

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
#endregion

namespace ST_3b9dbb4827024d4ea9e1bcfe00118dcc
{

    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        public void Main()
        {
            String OrderIDStr = Dts.Variables["User::OrderID"].Value.ToString();
            String folderName = (string)Dts.Variables["User::FolderName"].Value + "\\Folder_T0_" + OrderIDStr;

            try
            {
                // Determine whether the directory exists.
                if (Directory.Exists(folderName))
                {   return; }

                // Try to create the directory.
                DirectoryInfo di = Directory.CreateDirectory(folderName);
            }
            catch (Exception e)
            {   System.Windows.Forms.MessageBox.Show(e.ToString(),"Process Failed");    }
            finally { }
        }
    }
}

It works great. It then goes to the second loop which has the following (the only difference is the T0 vs T1 when setting the folderName variable:

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
#endregion

namespace ST_3b9dbb4827024d4ea9e1bcfe00118dcc
{

    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        public void Main()
        {
            String OrderIDStr = Dts.Variables["User::OrderID"].Value.ToString();
            String folderName = (string)Dts.Variables["User::FolderName"].Value + "\\Folder_T1_" + OrderIDStr;

            try
            {
                // Determine whether the directory exists.
                if (Directory.Exists(folderName))
                { return; }

                // Try to create the directory.
                DirectoryInfo di = Directory.CreateDirectory(folderName);
            }
            catch (Exception e)
            { System.Windows.Forms.MessageBox.Show(e.ToString(), "Process Failed"); }
            finally { }
        }
    }
}

When it runs, it says the task is successful, and I don't get any error popups. However, the folder is not actually created, so the next task to write to the folder of course crashes. I confirmed that the folderName variable is correct before it tries to create the folder, and I also confirmed it's not going into the exists IF statement and getting kicked out.

The other 3 scripts run fine, it's just this one, and the kicker is that I ran it an hour before and it ran fine!

For good measure I copied the code from the first one to the second one. No luck. I also copied the script task from the first loop to the second, changed the 0 to a 1 and that didn't work either. I then copied the script task from the first loop to the second and DIDN'T make any changes, and it creates the folder (doesn't work with the loop, but it creates the folder). I finally deleted the offending script task, and created a brand new one, and hand entered the code. I added a debug popup for good measure so I could confirm the folderName was correct. folderName is correct, and it is doing the same thing!

I'm seriously at a loss here. This makes NO sense!

Any help would be greatly appreciated!

Upvotes: 3

Views: 226

Answers (2)

billinkc
billinkc

Reputation: 61221

Besides Alexander's excellent answer, I'd point out that there is a native SSIS approach to directory creation: File System Task

In your case, I'd create variables like Folder0, Folder1, Folder2 and specify that they should be evaluated as expressions. The expression then becomes

@[User::FolderName] + "\\Folder_T0_" + @[User::OrderID]

The nice thing about this approach is that you can set a breakpoint on the various File System Tasks to ensure the string you built is the string you expected to build.

You can also put a Script Task into the package to automatically put those FolderN names into the run log of the package for retrospective debugging. Sample code in test for echo and the Biml is not required.

Upvotes: 3

Alexander Volok
Alexander Volok

Reputation: 5940

Your script task ends succesfully because you suppress an exception going outside by using CATCH without rethrow or redirection to SSIS Events.

Another thing is that you are using MessageBox.Show. It will break the package execution when you place it to production

Therefore consider to replace this code:

catch (Exception e)
 { 
     System.Windows.Forms.MessageBox.Show(e.ToString(), "Process Failed"); 
 }

With:

catch (Exception e)
 { 
    Dts.Events.FireError(-1, "Task Name", e.message, String.Empty, 0);
    Dts.TaskResult = (int)ScriptResults.Failure;  
 }
  • The real exception message will be passed to the SSIS infrastructure, including logs, reports
  • The task will be in failed state, so you will have a clear indication that something is going wrong

As a general design tip, you perhaps can inject Dts.Events.FireInformation in a flow of your logic to get understanding what is going on in the script task or mere debugging using VS shell

Upvotes: 3

Related Questions