Set SSIS database package path

Question

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

I am using this 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 packagePath property properly in the LoadFromSqlServer method.

Here is the image describing my package path in database:

Answer 1

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.

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

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

Answer 2

If you want to find the package location deployed in 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.

Ignore the server name because it will be parameter for the LoadFromSqlServer method.

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

Hope this helps.