0

Basically I've written a C# script for a Script task in SSIS that looks in a User::Directory for 1 csv, if & only if there is one file, it stores that in the instance variable which then maps to the package variables of SSIS.

When I exicute, it gives me the red filled in box of the Script task. I think it's related to how I'm looking at the directory, but I'm not sure.

Please help!

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

namespace ST_e8b4bbbddb4b4806b79f30644240db19.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
       private String fileName = "";
       private String RootDirictory;
       private String FilePath;

       enum ScriptResults
       {
           Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
           Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
       };

       public ScriptMain()
       {
           RootDirictory = Dts.Variables["RootDir"].Value.ToString();
           FilePath = RootDirictory + "\\" + "SourceData" + "\\";
       }

       public void setFileName()
       {
           DirectoryInfo YDGetDir = new DirectoryInfo(FilePath);
           FileInfo[] numberOfFiles = YDGetDir.GetFiles(".csv");

           if (numberOfFiles.Length < 2)
           {
               fileName = numberOfFiles[0].ToString(); 
           }

          int fileNameLen = fileName.Length;

          String temp = fileName.Substring(0, fileNameLen - 5);

          fileName = temp; 
       }


       public void mapStateToPackage()
       {
           if((fileName!=null)||(fileName!=""))
           {
               Dts.Variables["ExDFileName"].Value = fileName; 
           }
       }

        public void Main()
        {
            setFileName();
            mapStateToPackage(); 
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}
Snow_Mac
  • 5,727
  • 17
  • 54
  • 80

1 Answers1

0

This could simply be done using Foreach loop container as explained in this Stack Overflow question, which was asked by you. :-)

Anyway, to answer your question with respect to Script Task code that you have provided. Below mentioned reasons could be cause of the issues:

  1. You are looking for .csv. This won't return any results because you are looking for a file with no name but extension .csv. The criteria should be *.csv

  2. If you are looking for exactly one file, then the condition if (numberOfFiles.Length < 2) should be changed to if (numberOfFiles.Length == 1)

  3. The section of code after the if section which extracts the file name should be within the above mentioned if condition and not out side of it. This has to be done to prevent applying substring functionality on an empty string.

  4. Modified code can be found under the Script Task Code section.

Sorry, I took the liberty to simplify the code a little. I am not suggesting this is the best way to do this functionality but this is merely an answer to the question.

Hope that helps.

Script Task Code:

C# code that can be used only in SSIS 2008 and above.

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

namespace ST_3effcc4e812041c7a0fea69251bedc25.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        Variables varCollection = null;
        String fileName = string.Empty;
        String fileNameNoExtension = string.Empty;
        String rootDirectory = string.Empty;
        String filePath = string.Empty;

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            Dts.VariableDispenser.LockForRead("User::RootDir");
            Dts.VariableDispenser.LockForWrite("User::ExDFileName");
            Dts.VariableDispenser.GetVariables(ref varCollection);

            rootDirectory = varCollection["User::RootDir"].Value.ToString();
            filePath = rootDirectory + @"\SourceData\";

            DirectoryInfo YDGetDir = new DirectoryInfo(filePath);
            FileInfo[] numberOfFiles = YDGetDir.GetFiles("*.csv");

            if (numberOfFiles.Length == 1)
            {
                fileName = numberOfFiles[0].ToString();
                fileNameNoExtension = fileName.Substring(0, fileName.LastIndexOf("."));
            }
            if (!String.IsNullOrEmpty(fileNameNoExtension))
            {
                varCollection["User::ExDFileName"].Value = fileNameNoExtension;
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}
Community
  • 1
  • 1