如何使用 vs2010 在 SqlServer 2012 中运行 SSIS 包? [英] How do I run an SSIS package in SqlServer 2012 using vs2010?

问题描述

我有一个可以从 Visual Studio 运行的 vs2010 SSIS 包.我已将它部署到我的本地 2012 服务器，并且我还可以从 SSMS 执行该包.在 SSMS 中，我在这里看到了这个包:

I have a vs2010 SSIS package that I can run from visual studio. I have deployed it to my local 2012 server, and I can also execute that package from SSMS. In SSMS, I see the package here:

\Integration Services Catalogs\SSISDB\DAT_Load\Projects\UploadSYS.dtsx

\Integration Services Catalogs\SSISDB\DAT_Load\Projects\UploadSYS.dtsx

注意:vs2010 没有给我一个选项，可以在服务器之外的任何地方部署包，然后只能在集成服务目录中.进入那里后，MSDB 数据库在 sysssispackages 表中没有条目.

Note: vs2010 does not give me an option to deploy a package anywhere but in a server, and then only in Integration Services Catalogs. Once in there, the MSDB database does not have an entry in the sysssispackages table.

以前，启动 SSMS 并从那里运行包就足够了(右键单击并执行).现在，我必须从 C# Web 应用程序执行它.此外，我需要通过事件捕获进度消息等.

Previously, it was adequate to bring up SSMS and run the package from there(right-click & execute). Now, I have to execute this from a C# web application. Furthermore, I need to trap progress messages and such through events.

我能够确定如何设置事件捕获，并且我已经到了应该能够从代码执行包的地步:

I was able to determine how to set up the event trapping and I got myself to the point where I should have been able to execute the package from code:

    public DTSExecResult ExecutePackage(string packageName, HttpContextBase context)
    {
        string ppath = ConfigurationManager.AppSettings[packageName + "Package"];
        string pserv = ConfigurationManager.AppSettings[packageName + "Server"];
        string puser = ConfigurationManager.AppSettings[packageName + "User"];
        string ppass = ConfigurationManager.AppSettings[packageName + "Pwd"];
        _context = context;
        _pkgLocation = "";
        _app = new Application();
        _pkg = _app.LoadFromSqlServer(ppath, pserv, puser, ppass, _SSISEvents);
        _pkgResults = _pkg.Execute(_connections, _variables, _SSISEvents, _log, null);
        return _pkgResults;
    }

问题

我找不到包裹.当我到达 LoadFromSqlServer 语句时，我收到一条错误消息:

Problem

I cannot locate the package. When I reach the LoadFromSqlServer statement, I receive an error that says:

找不到文件夹\Integration Services Catalogs\SSISDB\DAT_Load\Projects\UploadSYS.dtsx"

Cannot find folder "\Integration Services Catalogs\SSISDB\DAT_Load\Projects\UploadSYS.dtsx"

同样的事情发生在路径的变化上(变量 = ppath):

The same thing happens for variations in the path (variable = ppath):

• \Integration Services Catalogs\SSISDB\DAT_Load\Projects\UploadSYS.dtsx
• \SSISDB\DAT_Load\Projects\UploadSYS.dtsx
• \DAT_Load\Projects\UploadSYS.dtsx
• 等

不能从命令行或存储过程运行它.

Running this from the command line or a stored procedure is not an option.

那么，谁能告诉我在这里缺少什么?Application 对象是否需要初始化一些东西?这甚至可能吗?

So, can anyone tell what I am missing here? Does the Application object need to initialize something? Is this even possible?

推荐答案

再次解决这个问题，但请参阅 设置 SSIS 数据库包路径 和 SSIS 组织以供后台阅读.

Taking another bite at the problem but see Set SSIS database package path and SSIS Organization for background reading.

在 SSIS 2012 之前，如果包被部署到 SQL Server，它们就存在于 msdb 中.用于与它们交互的 .NET API 跨版本相同.

Until SSIS 2012, if packages were deployed to SQL Server, they lived in the msdb. The .NET API for interacting with them was the same across versions.

在 2012 年发布的 SSIS 中，我们有两种不同的部署模型.包部署，这是经典"模型是活跃和完全支持的.用于在 2005 上运行包的相同代码将适用于 2012 包部署模型项目.这是 Microsoft.SqlServer.Dts.Runtime 命名空间

With the 2012 release of SSIS, we have two different deployment models. Package deployment, which is the "classic" model is alive and fully supported. The same code for running a package on 2005 will work for 2012 package deployment model projects. This is the Microsoft.SqlServer.Dts.Runtime Namespace

您的代码正在尝试加载使用项目部署模型"和包部署模型"API 构建的 2012 解决方案.这是 Microsoft.SqlServer.Management.IntegrationServices Namespace 和两者不能混用.

Your code is attempting to load a 2012 solution built using the "project deployment model" with the "package deployment model" API. This is the Microsoft.SqlServer.Management.IntegrationServices Namespace and the two don't mix.

您的选择是将您的项目切换回包部署模型或更新您的代码.在第一个链接问题中，我提供了用于在 SSISDB 目录中运行 SSIS 包的 VB.NET 实现.有some 运行.ispac 文件的方法，因为我在dtexec 中看到了该选项，但我没有看到具体的方法.这是 VS/SSDT 在本地运行包时使用的机制.

Your options are to switch your project back to the Package deployment model or update your code. In the first linked question, I provided the VB.NET implementation for running an SSIS package in the SSISDB catalog. There is some way of running a .ispac file because I see the option in dtexec but I'm not seeing the specific method. This is mechanism VS/SSDT uses when it runs the packages locally.

这篇关于如何使用 vs2010 在 SqlServer 2012 中运行 SSIS 包?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！