SSIS:从文件夹中获取任何平面文件源并将名称缓存为超级全局变量

发布于 2024-11-13 09:46:47 字数 260 浏览 0 评论 0原文

我正在 SSIS 和 Visual Studio 2008 中工作。执行时,我需要让 SSIS 包执行以下任务:

  • 检查文件夹中的文件
  • 的源
  • 如果文件存在,则获取该文件并将其用作平面文件存储 文件名放入全局变量中,我可以在包的其他部分访问

该变量该包将由其他脚本运行。因此,我们需要它在每次包运行时检查文件。我们试图防止出现这样的情况:当文件出现时,我们必须监视文件夹并手动执行包。

有什么建议吗?

I'm working in SSIS and Visual Studio 2008. When executed, I need to have the SSIS package perform the following tasks:

  • Check a folder for a file
  • If a file exists take the file and use it as the source for the flat file
  • Store the name of the file into a global variable that I can access in other parts of my package

The package will be run by some other script. Thus we need it to check for the file every time the package runs. We are trying to prevent the scenario where we have to monitor the folder and execute the package manually when the file appears.

Any suggestions?

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

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

发布评论

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

评论(2

一向肩并 2024-11-20 09:46:47

最简单的方法是设置一个 Foreach 循环容器,其中包含包的所有“工作”(或者,您可以将其作为前导步骤并使用其中的条件表达式)。假设您有 2 个名为 FileName 的变量(您将为其分配值)和一个包含我们应该查找的“位置”的 InputFolder 变量

ForEach Loop Editor

Collection tab:  
Enumerator = Foreach File Enumerators
Expression: Directory = @[User:InputFolder]
FileSpec: "YD.*"

Retrieve file name
* Fully qualified

Variable Mappings tab:  
Variable: User::FileName 
Index:  0

集合选项卡
Variable Mappings Tab

您也可以通过脚本任务来执行此操作,如果您想查看,请告诉我。

编辑
该脚本再次假设您已定义变量 InputFolder 和 FileName。创建脚本任务组件并将 InputFolder 选中为只读变量,将 FileName 选中为读/写变量。

using System;
using System.Data;
using System.IO;  // this needs to be added
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

// namespace will vary 
namespace ST_bc177fa7cb7d4faca15531cb700b7f11.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

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

        public void Main()
        {
            string inputFolder;
            string fileName;
            inputFolder = Dts.Variables["InputFolder"].Value.ToString();

            // File, if exists will look like YD.CCYYMMDD.hhmmss.done
            string fileMask = "YD.*.done";

            // this array will catch all the files matching a given pattern
            string[] foundFiles = null;
            foundFiles = System.IO.Directory.GetFiles(inputFolder, fileMask);

            // Since there should be only one file, we will grab the zeroeth
            // element, should it exist
            if (foundFiles.Length > 0)
            {
                fileName = foundFiles[0];

                // write the value to our global SSIS variable
                Dts.Variables["FileName"].Value = fileName;
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

The easiest way would be to set up a Foreach Loop container that has all the "work" of your package inside of it (optionally, you can it as a precursor step and use a conditional expression off of it). Assuming you have 2 variables called FileName (which is what you will have the value assigned to) and an InputFolder variable that contains the "where" we should be looking

ForEach Loop Editor

Collection tab:  
Enumerator = Foreach File Enumerators
Expression: Directory = @[User:InputFolder]
FileSpec: "YD.*"

Retrieve file name
* Fully qualified

Variable Mappings tab:  
Variable: User::FileName 
Index:  0

Collection Tab
Variable Mappings Tab

You can also do this via a script task, if you'd like to see that, let me know.

EDIT
This script again assumes you have the variables InputFolder and FileName defined. Create a Script Task Component and check InputFolder as a read only variable, FileName as a read/write variable.

using System;
using System.Data;
using System.IO;  // this needs to be added
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

// namespace will vary 
namespace ST_bc177fa7cb7d4faca15531cb700b7f11.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

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

        public void Main()
        {
            string inputFolder;
            string fileName;
            inputFolder = Dts.Variables["InputFolder"].Value.ToString();

            // File, if exists will look like YD.CCYYMMDD.hhmmss.done
            string fileMask = "YD.*.done";

            // this array will catch all the files matching a given pattern
            string[] foundFiles = null;
            foundFiles = System.IO.Directory.GetFiles(inputFolder, fileMask);

            // Since there should be only one file, we will grab the zeroeth
            // element, should it exist
            if (foundFiles.Length > 0)
            {
                fileName = foundFiles[0];

                // write the value to our global SSIS variable
                Dts.Variables["FileName"].Value = fileName;
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}
撧情箌佬 2024-11-20 09:46:47

这是一个可能的选择。您可以使用 Foreach Loop 容器来实现此目的。请查找我在下面提供的示例。希望这能提供一个想法。

分步过程:

  1. 在 SSIS 包上,创建 3 个变量,如屏幕截图 #1 所示。范围 CheckFile 表示包名称。变量 Folder 将代表您要检查文件的文件夹。 Filename 表示要检查的文件名。变量 FilePath 将是您需要的全局变量。如果文件存在,则填写文件路径值,否则为空。

  2. 在包的控制流选项卡上,放置一个 Foreach 循环容器 和一个脚本任务。脚本任务是为了展示 Foreach 循环容器执行完成后变量保留值。请参阅屏幕截图 #2

  3. 配置 ForEach 循环容器,如屏幕截图 #3 和 #4 所示。

  4. 将 Script Task 中的 Main() 方法替换为 Script task code 部分中给出的代码。这是为了演示变量 FilePath 保留的值。

  5. 屏幕截图#5显示路径c:\temp\中不存在文件,屏幕截图#6显示相应的包执行。< /p>

  6. 屏幕截图 #7 显示文件 TestFile.txt 存在于路径 c:\temp\ 中,屏幕截图 #8< /strong> 显示相应的包执行。

  7. 如果您想在文件存在时对其进行处理,可以在 Foreach 循环容器 中放置一个数据流任务 来执行此操作。

希望有帮助。

脚本任务代码:

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

public void Main()
        {
            Variables varCollection = null;

            Dts.VariableDispenser.LockForRead("User::FilePath");
            Dts.VariableDispenser.GetVariables(ref varCollection);

            if (String.IsNullOrEmpty(varCollection["User::FilePath"].Value.ToString()))
            {
                MessageBox.Show("File doesn't exist.");
            }
            else
            {
                MessageBox.Show("File " + varCollection["User::FilePath"].Value.ToString() + " exists.");
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

截图#1:

1

屏幕截图 #2:

2

屏幕截图 #3:

3

屏幕截图 #4:

4

屏幕截图 # 5:

5

屏幕截图 #6:

6

屏幕截图 #7:

7

屏幕截图 # 8:

8

Here is a possible option. You can achieve this using the Foreach Loop container. Please find the example that I have provided below. Hopefully, that gives an idea.

Step-by-step process:

  1. On the SSIS package, create 3 variables are shown in screenshot #1. Scope CheckFile represents the package name. Variable Folder will represent the folder that you would like to check for the file. Filename represents the file name to check for. Variable FilePath will be the global variable that you will need. It will be filled in with the file path value if the file exists, otherwise it will be empty.

  2. On the package's Control Flow tab, place a Foreach Loop container and a Script Task. Script Task is to showcase that the variable retains the value after the Foreach Loop container execution is complete. Refer screenshot #2.

  3. Configure ForEach Loop container as shown in screenshots #3 and #4.

  4. Replace the Main() method within the Script Task with the code given under the Script task code section. This is to demonstrate the value retained by the variable FilePath.

  5. Screenshots #5 shows no files exist in the path c:\temp\ and screenshot #6 shows the corresponding package execution.

  6. Screenshots #7 shows the file TestFile.txt exists in the path c:\temp\ and screenshot #8 shows the corresponding package execution.

  7. If you would like to process the file when it exists, you can place a Data Flow Task within the Foreach Loop container to do that.

Hope that helps.

Script task code:

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

public void Main()
        {
            Variables varCollection = null;

            Dts.VariableDispenser.LockForRead("User::FilePath");
            Dts.VariableDispenser.GetVariables(ref varCollection);

            if (String.IsNullOrEmpty(varCollection["User::FilePath"].Value.ToString()))
            {
                MessageBox.Show("File doesn't exist.");
            }
            else
            {
                MessageBox.Show("File " + varCollection["User::FilePath"].Value.ToString() + " exists.");
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

Screenshot #1:

1

Screenshot #2:

2

Screenshot #3:

3

Screenshot #4:

4

Screenshot #5:

5

Screenshot #6:

6

Screenshot #7:

7

Screenshot #8:

8

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