SSIS:通过 SSIS 执行 Ironpython 或 Ironruby 脚本 [英] SSIS: Execute Ironpython or Ironruby scripts through SSIS

问题描述

我有一个小的 python 脚本，它遍历一个网页(http-crawling).此网页托管在 Intranet 内，并使用 NTLM 身份验证来收集对其的访问权限.

I have a little python script, which goes throught a web page (http-crawling). This web-page is hosted inside the intranet and uses NTLM authentication to gather access to it.

所以，我发现这个任务(检索 http 内容)可以使用 python 轻松编程，而不是尝试将整个 python 脚本重新编写为 C#，然后通过 SSIS 上的脚本任务"使用它，以完成任务.

So, I found this task (retrieve http-content) easily programmable using python, instead of trying to re-write the whole python script to C# and then use it througth "Script Task" on SSIS, in order to complete the task.

我仔细查看了 SSIS 工具，发现有一个名为执行进程任务"的控制流，可让您执行 Win32 可执行文件.

I've looked up closely to SSIS tools and I found that there is a Control Flow named "Execute Process Task", which lets you to execute Win32 executables.

但问题在于如何调用我的 python 脚本，因为它不可执行，需要由 python 解释器解释(如果你能原谅重复的话).因此，我可以轻松地构建一个简单的.bat"文件，该文件同时调用 python 脚本和解释器.然后通过SSIS执行进程任务"执行该文件.

But the problem resides in how to call my python script since it's not executable and needs to be interpreted by the python interpreter (if you'll forgive the repetition). So, I could easily end up building a simple ".bat" file that calls both the python script and the interpreter. And then execute that file through SSIS "Execute Process Task".

还有其他方法可以实现吗?(整洁的方式)

Is there any other way to implement this? (neat way)

从脚本中检索到的信息会将该信息存储到数据库中的表中，以便从另一个 SSIS 进程通过数据库表访问该信息.

The information retrieved from the script will be storing that information into a table from a database, So that information will be accessed trough the database table from another SSIS process.

我正在从不同来源(平面文件、数据库表、http 请求等)检索信息，以便将该信息存档到可以发布在 Web 服务中然后从 Excel 项目访问的数据库中.

I'm retrieving the information from different sources (flat files, database tables, http request, ...) in order to archive that information into a database that could be posted in a web services and then accessed from a Excel project.

提前致谢！

推荐答案

在 SSIS 的范围内使用 IronPython 的最简单的机制，至少在我看来，是调用外部进程并转储到文件，然后使用作为数据流的来源.

The easiest, at least to my brain, mechanism for using IronPython from the confines of SSIS would be to invoke the external process and dump to a file and then use that as a source for a dataflow.

也就是说，我能够从 C# 托管 IronPython 应用程序，并使用返回的数据填充输出缓冲区并与管道中的数据进行交互.我只有一台机器可以执行此操作，所以我列出了我记得在包裹变绿之前所做的一切.

That said, I was able to host an IronPython app from C# and use the returned data to populate the output buffers and interact with that data in the pipeline. I've only had one machine to perform this on so I'm listing everything I recall doing until the package went green.

这篇文章为我指明了如何进行这项工作.在 C# 4.0 程序中托管 IronPython 我强烈建议您创建一个 C#/VB.NET 控制台应用程序并首先让您的 IronPython 集成在那里工作，因为 SSIS 将为所有内容添加一个附加层.

This article set me down the path of how to make this work. Hosting IronPython in a C# 4.0 program I would strongly urge you to create a C#/VB.NET console app and get your IronPython integration working there first as SSIS is going to add an additional layer to everything.

也许可以在不需要 4.0 框架的情况下在 C# 中托管旧版本的 IronPython，但这远远超出了我的能力范围.我能说的是，要使用 4.0 框架，您正在查看 SQL Server 2012.2008 包最多可以针对 3.5 框架(默认为 2.0).

There may be the ability to host older versions of IronPython within C# without requiring the 4.0 framework but that's far beyond the realm of my competency. What I can say is that to use the 4.0 framework, you are looking at SQL Server 2012. A 2008 package can target up to the 3.5 framework (default is 2.0).

全局程序集缓存，简称 GAC.它是 Windows 中一个特殊的地方，签名的程序集可以在其中驻留.SSIS 可能能够使用不在 GAC 中的程序集，但我没有这样做.这个案子也不例外.我的控制台应用程序运行良好，但是当我将该代码复制到 SSIS 时，它会出现 Could not load file or assembly 'Microsoft.Scripting... 错误消息.幸运的是，IronPython-2.7.2.1(可能还有以前的版本)是强签名的 dll.这意味着您可以而且必须将它们添加到 GAC 中.

Global Assembly Cache, GAC for short. It is a special place in Windows where signed assemblies can live. SSIS may be able to use assemblies that aren't in the GAC, but I've not had luck doing so. This case was no different. My Console app worked fine but when I copied that code into SSIS, it'd tank with Could not load file or assembly 'Microsoft.Scripting... error messages. Blessedly, IronPython-2.7.2.1 (and probably previous versions) are strongly signed dlls. That means you can and must add them into the GAC.

在您的 Visual Studio 目录中，查找 Visual Studio 命令提示符 (2010).假设你的 IronPython 安装文件夹是 C: mpIronPython-2.7.2.1IronPython-2.7.2.1 你会输入 cd C: mpIronPython-2.7.2.1IronPython-2.7.2.1 然后我注册了以下3个程序集

In your Visual Studio directory, look for the Visual Studio Command Prompt (2010). Assuming your IronPython installation folder is C: mpIronPython-2.7.2.1IronPython-2.7.2.1 you would type cd C: mpIronPython-2.7.2.1IronPython-2.7.2.1 Then I registered the following 3 assemblies

C:	mpIronPython-2.7.2.1IronPython-2.7.2.1>gacutil -if Microsoft.Dynamic.dll
Microsoft (R) .NET Global Assembly Cache Utility.  Version 4.0.30319.1
Copyright (c) Microsoft Corporation.  All rights reserved.

Assembly successfully added to the cache

C:	mpIronPython-2.7.2.1IronPython-2.7.2.1>gacutil -if IronPython.dll
Microsoft (R) .NET Global Assembly Cache Utility.  Version 4.0.30319.1
Copyright (c) Microsoft Corporation.  All rights reserved.

Assembly successfully added to the cache

C:	mpIronPython-2.7.2.1IronPython-2.7.2.1>gacutil -if Microsoft.Scripting.dll
Microsoft (R) .NET Global Assembly Cache Utility.  Version 4.0.30319.1
Copyright (c) Microsoft Corporation.  All rights reserved.

Assembly successfully added to the cache

我的 SSIS 项目，我已将 Run64bitRuntime 设置为 False 但在重新测试时，这无关紧要.默认为 True，这似乎工作正常.

My SSIS project, I had set the Run64bitRuntime to False but in retesting, it does not matter. The default it True and that seems to work fine.

Python 脚本 - 我没有足够的背景来使 C# 和 .NET DLR 语言之间的集成更加优雅.提供一个字符串或包含我想要执行的脚本的东西会很好，也许这就是脚本块的内容，但我没有时间进行调查.因此，此解决方案需要一个位于磁盘某处的脚本文件.我在从托管脚本(没有名为 X 异常的模块)进行导入时遇到了问题.毫无疑问，类路径和所有需要提供给主机才能使其正常工作的东西有一些魔力.顺便说一句，这可能是一个不同的 SO 问题.

Python script - I don't have enough of a background to make the integration between C# and .NET DLR languages more graceful. It'd have been nice to supply a string or something containing the script I wanted to execute and perhaps that's what a script block is about but I don't have time to investigate. So, this solution requires a script file sitting out somewhere on disk. I had trouble with the imports working from a hosted script (no module named X exceptions). Undoubtedly there's some magic with class paths and all that stuff that needs to provided to the host to make it work well. That's probably a different SO question btw.

我有一个文件位于 C:ssisdatasimplePy.py

I have a file sitting at C:ssisdatasimplePy.py

# could not get a simple import to work from hosted
# works fine from "not hosted"
#import os

def GetIPData():
    #os.listdir(r'C:\')
    return range(0,100)

向数据流添加脚本任务后，我将其配置为在输出缓冲区 (wstr 1000) 上有一个单列.然后我将其用作我的源代码.

After adding a script task to the Data Flow, I configured it to have a single column on the output buffer (wstr 1000). I then used this as my source code.

using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using IronPython.Hosting;
using Microsoft.Scripting.Hosting;

/// <summary>
/// Attempt to use IP script as a source
/// http://blogs.msdn.com/b/charlie/archive/2009/10/25/hosting-ironpython-in-a-c-4-0-program.aspx
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    /// <summary>
    /// Create data rows and fill those buckets
    /// </summary>
    public override void CreateNewOutputRows()
    {
        foreach (var item in this.GetData())
        {
            Output0Buffer.AddRow();
            Output0Buffer.Content = item;
        }

    }

    /// <summary>
    /// I've written plenty of code, but I'm quite certain this is some of the ugliest.
    /// There certainly must be more graceful means of 
    /// * feeding your source code to the ironpython run-time than a file
    /// * processing the output of the code the method call
    /// * sucking less at life
    /// </summary>
    /// <returns>A list of strings</returns>
    public List<string> GetData()
    {
        List<string> output = null;
        var ipy = Python.CreateRuntime();
        dynamic test = ipy.UseFile(@"C:ssisdatasimplePy.py");
        output = new List<string>();
        var pythonData = test.GetIPData();
        foreach (var item in pythonData)
        {
            output.Add(item.ToString());
        }

        return output;
    }
}

快速了解我的参考资料

点击运行按钮，成功

这篇关于SSIS:通过 SSIS 执行 Ironpython 或 Ironruby 脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！