已创建SSISDB目录，如何在Integrated Services Catalog中创建项目及下属包？

Hey there, let's break this down clearly because there's a common mix-up here between developing SSIS projects/packages and managing them in SSMS.

The Integrated Services Catalogs (specifically the SSISDB) in SSMS is a deployment and management hub—not a development environment. You can't create new projects or packages directly here; that work happens in Visual Studio with the SQL Server Data Tools (SSDT) component installed.

Your existing packages showing up in the MDSB folder means you're using the older "file system" or "MSDB" package storage (not the SSISDB catalog deployment model), which is why they aren't appearing under SSISDB.

First, make sure you have Visual Studio installed with the SQL Server Data Tools (SSDT) workload (it's a free component you can add via the Visual Studio Installer if you don't have it yet). Then:

• Open Visual Studio, go to File > New > Project
• Search for and select SQL Server Integration Services Project (the exact name might vary slightly by VS version)
• Once the project is created, add new packages by right-clicking the SSIS Packages folder in the Solution Explorer > New SSIS Package
• If you have existing packages (from the MDSB folder), right-click SSIS Packages > Add Existing Package, then select the package files (.dtsx) from their location to import them into the project

Once your project is ready, deploy it to the SSISDB so it shows up in SSMS:

1. In Visual Studio, right-click your SSIS project in the Solution Explorer > Deploy
2. The Deployment Wizard will open:
   • On the Select Destination page, enter your SQL Server instance name, select Integration Services Catalogs, then choose the SSISDB catalog. You can create a new folder under SSISDB first (in SSMS, right-click SSISDB > Create Folder) if you want to organize projects.
   • Follow the rest of the wizard to complete deployment.
3. Go back to SSMS, refresh the Integrated Services Catalogs > SSISDB node—you'll now see your deployed project and its packages listed under the target folder.

To trigger the package from an Access frontend button, you have two reliable options:

Option 1: Use VBA to call dtexec.exe (command-line tool) #

dtexec.exe is the built-in tool for running SSIS packages. Add this VBA code to your Access button's click event:

Private Sub btnRunSSISPackage_Click()
    Dim cmd As String
    Dim packagePath As String
    Dim serverInstance As String
    
    ' Set your values here
    serverInstance = "YOUR_SQL_SERVER_INSTANCE"
    packagePath = "\SSISDB\YourFolder\YourProject\YourPackage.dtsx"
    
    ' Build the dtexec command
    cmd = "dtexec.exe /SQL """ & packagePath & """ /Server """ & serverInstance & """"
    
    ' Run the command
    Shell cmd, vbNormalFocus
End Sub

Note: The user running Access needs permissions to execute dtexec.exe and access the SQL Server/SSISDB.

Option 2: Call the SSISDB stored procedure via VBA #

You can also trigger the package by calling the catalog.start_execution stored procedure in SQL Server. This is more integrated with the SSISDB catalog:

Private Sub btnRunSSISPackage_Click()
    Dim conn As Object
    Dim cmd As Object
    Dim executionId As Long
    
    Set conn = CreateObject("ADODB.Connection")
    Set cmd = CreateObject("ADODB.Command")
    
    ' Connection string (adjust for your server/database)
    conn.Open "Provider=SQLOLEDB;Data Source=YOUR_SQL_SERVER_INSTANCE;Initial Catalog=SSISDB;Integrated Security=SSPI;"
    
    With cmd
        .ActiveConnection = conn
        .CommandType = 4 ' Stored procedure
        .CommandText = "catalog.start_execution"
        ' First, generate an execution ID using catalog.create_execution (example snippet)
        ' This is a simplified version—adjust if you need to set parameters
        .Parameters.Append .CreateParameter("@execution_id", 3, 2) ' adInteger, adParamOutput
        .Execute
        executionId = .Parameters("@execution_id").Value
    End With
    
    conn.Close
    Set cmd = Nothing
    Set conn = Nothing
    
    MsgBox "Package started with Execution ID: " & executionId
End Sub

Note: The user needs EXECUTE permissions on the catalog.start_execution stored procedure and appropriate access to the SSISDB project/package.

内容的提问来源于stack exchange，提问作者Graham_C