SSIS/C#:脚本任务，C# 脚本查看目录并将 1 个文件的名称存储在变量中

问题描述

基本上我已经为 SSIS 中的脚本任务编写了一个 C# 脚本，该脚本在 User::Directory 中查找 1 个 csv，如果 &仅当有一个文件时，它才会将其存储在实例变量中，然后该实例变量会映射到 SSIS 的包变量.

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.

请帮忙！

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;
        }
    }
}

推荐答案

这可以简单地使用 Foreach 循环容器 来完成，如 这个堆栈溢出问题，这是你问的.:-)

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. 您正在寻找 .csv.这不会返回任何结果，因为您正在寻找一个没有名称但扩展名为 .csv 的文件.标准应为 *.csv

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

如果您只查找一个文件，则条件 if (numberOfFiles.Length < 2) 应更改为 if (numberOfFiles.Length == 1)

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

提取文件名的 if 部分之后的代码部分应该在上述 if 条件之内，而不是超出它的范围.必须这样做以防止在空字符串上应用子字符串功能.

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.

修改后的代码可以在脚本任务代码部分下找到.

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.

希望有所帮助.

脚本任务代码:

C# 只能在 SSIS 2008 及更高版本 中使用的代码.

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;
        }
    }
}

这篇关于SSIS/C#:脚本任务，C# 脚本查看目录并将 1 个文件的名称存储在变量中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！