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

发布于 2024-11-14 01:36:53 字数 1895 浏览 0 评论 0原文

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

当我兴奋时,它会给我脚本任务的红色填充框。我认为这与我查看目录的方式有关,但我不确定。

请帮忙!

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

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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

昔日梦未散 2024-11-21 01:36:53

这可以简单地使用 Foreach 循环容器 来完成,如 这个 Stack Overflow 问题,这是您提出的。 :-)

无论如何,回答有关您提供的脚本任务代码的问题。以下提到的原因可能是导致问题的原因:

  1. 您正在寻找 .csv。这不会返回任何结果,因为您正在查找没有名称但扩展名为 .csv 的文件。标准应为 *.csv

  2. 如果您正在查找一个文件,则条件 if (numberOfFiles.Length < 2) 应更改为 < code>if (numberOfFiles.Length == 1)

  3. if 部分之后提取文件名的代码部分应位于上述 if 内 条件而不是在它之外。必须这样做是为了防止在空字符串上应用子字符串功能。

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

抱歉,我冒昧地简化了代码。我不建议这是实现此功能的最佳方式,但这只是问题的答案

希望有帮助。

脚本任务代码:

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

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

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;
        }
    }
}
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文