vb.net 设置 SSIS 数据库包路径

Set SSIS database package path

提问by serializer

I am trying to execute a SSIS package located in a database programatically.

我正在尝试以编程方式执行位于数据库中的 SSIS 包。

I am using this API:

我正在使用这个 API：

Imports Microsoft.SqlServer.Dts.Runtime

I have an image describing the path (in database) to package but I cannot figure out how to set the packagePathproperty properly in the LoadFromSqlServermethod.

我有一张图片描述了要打包的路径（在数据库中），但我无法弄清楚如何在LoadFromSqlServer方法中正确设置packagePath属性。

Here is the image describing my package path in database:

这是描述我在数据库中的包路径的图像：

回答by billinkc

You will need to add a reference to Microsoft.SqlServer.Management.IntegrationServices. For me, it does not show up in the SQL Server folders and I could only find it in the GAC.

您将需要添加对 Microsoft.SqlServer.Management.IntegrationServices 的引用。对我来说，它没有出现在 SQL Server 文件夹中，我只能在 GAC 中找到它。

C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.IntegrationServices.dll

C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.IntegrationServices.dll

There's also a dependency from that assembly to

该程序集还依赖于

C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll

C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll

Sub Main()
    '
    ' Do not fault me for my poor VB skills nor my lack of error handling
    ' This is bare bones code adapted from 
    ' http://blogs.msdn.com/b/mattm/archive/2011/11/17/ssis-and-powershell-in-sql-server-2012.aspx

    Dim folderName As String
    Dim projectName As String
    Dim serverName As String
    Dim packageName As String
    Dim connectionString As String
    Dim use32BitRuntime As Boolean
    Dim executionId As Integer

    Dim integrationServices As Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices
    Dim catalog As Microsoft.SqlServer.Management.IntegrationServices.Catalog
    Dim catalogFolder As Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder
    Dim package As Microsoft.SqlServer.Management.IntegrationServices.PackageInfo

    ' Dimensions in your example
    folderName = "SSISHackAndSlash"
    ' dimCalendar in your example
    projectName = "SSISHackAndSlash2012"
    serverName = "localhost\dev2012"

    ' dimCalendar in your example (no file extension)
    packageName = "TokenTest.dtsx"
    connectionString = String.Format("Data Source={0};Initial Catalog=msdb;Integrated Security=SSPI;", serverName)

    integrationServices = New Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices(New System.Data.SqlClient.SqlConnection(connectionString))
    ' There is only one option for an SSIS catalog name as of this posting
    catalog = integrationServices.Catalogs("SSISDB")

    ' Find the catalog folder. Dimensions in your example
    catalogFolder = catalog.Folders(folderName)

    ' Find the package in the project folder
    package = catalogFolder.Projects(projectName).Packages(packageName)

    ' Run the package. The second parameter is for environment variables
    executionId = package.Execute(use32BitRuntime, Nothing)

End Sub

回答by Gowdhaman008

If you want to find the package location deployed in SQL server.

如果要查找部署在 SQL Server 中的包位置。

1. Open SSMS.
2. Connect to Integration Services.
3. Go to View and Click "Object Explorer Details".
4. Now you select your package to know the package path in SQL server. Take a look at the screenshot below.

1. 打开 SSMS。
2. 连接到集成服务。
3. 转到查看并单击“对象资源管理器详细信息”。
4. 现在您选择您的包以了解 SQL Server 中的包路径。看看下面的截图。

Ignore the server name because it will be parameter for the LoadFromSqlServermethod.

忽略服务器名称，因为它将是该LoadFromSqlServer方法的参数。

So package path should be : \Stored Package\MSDB\Data Collector\PerfCountersUpload.

所以包路径应该是：\Stored Package\MSDB\Data Collector\PerfCountersUpload。

Hope this helps.

希望这可以帮助。

回答by EvZ

In addition to billinkc answer.

除了billinkc回答。

Here is the C# version of the code:

这是代码的 C# 版本：

string folderName = "name";
string projectName = "name";
string serverName = "localhost";
string packageName = "name";
string connectionString = string.Format("Data Source={0};Initial Catalog=msdb;Integrated Security=SSPI;", serverName);

var integrationServices = new IntegrationServices(newSystem.Data.SqlClient.SqlConnection(connectionString));
var catalog = integrationServices.Catalogs["SSISDB"];
var catalogFolder = catalog.Folders[folderName];

var package = catalogFolder.Projects[projectName].Packages[packageName];

long execId = package.Execute(false, null);

In my case I had to add 4 dlls:

就我而言，我必须添加 4 个 dll：

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.IntegrationServices.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.Smo.dll

All the dependencies can be found C:\Windows\assembly\GAC_MSIL\

可以找到所有依赖项 C:\Windows\assembly\GAC_MSIL\