Search code examples
c#sql-serverssisetlscript-task

SSIS Getting Exception When Accessing Int Variable

I am trying to create a script that will generate folders that I need on the fly.

The script takes a FolderName and OrderID variable to generate the folder names to create. I Added them to the Read Only variables for the script.

enter image description here

Everything works fine with the FolderName, it's only when I try to access the OrderID variable that things blow up. I get the nasty Exception error below.

    public void Main()
    {
        //  Output File Expression
        //     "" + @[User::FolderName]+"\\Folder_"+ (DT_WSTR,20) @[User::T1_ID]  +"\\SalesOrderHeader_T1_"+ (DT_WSTR,20) @[User::T1_ID]+ ".txt"

        String folderName = (string)Dts.Variables["User::FolderName"].Value;
        String OrderIDStr = (string)Dts.Variables["User::OrderID"].Value;
        System.Windows.Forms.MessageBox.Show(OrderIDStr, "OrderID Var");

        folderName = string.Concat(folderName, "\\Folder_");
        System.Windows.Forms.MessageBox.Show(folderName, "Folder Name");

        try
        {
            // Determine whether the directory exists.
            if (Directory.Exists(folderName))
            {
                Console.WriteLine("That path exists already.");
                System.Windows.Forms.MessageBox.Show("That path exists already.", "WARNING");
                return;
            }

            // Try to create the directory.
                DirectoryInfo di = Directory.CreateDirectory(folderName);
                Console.WriteLine("The directory was created successfully at {0}.", Directory.GetCreationTime(folderName));
        }
        catch (Exception e)
        {
            Console.WriteLine("The process failed: {0}", e.ToString());
        }
        finally { }
    }

I'm setting the value to 1 at the package level, and I'm casting it to String, so I'm not sure what the issue is.

enter image description here

The text gets cut off in the box, here is everything:

at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at system.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke (Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

Solution

  • Try to use .ToString() method instead of casting (string)

    String OrderIDStr = Dts.Variables["User::OrderID"].Value.ToString();

    Based on this article, explicit casting is not allowed from int to string.

    References