Figure out name of project in SSIS

问题:

Is it possible to figure out the name of the project while in SSIS either through a script task, or a variable?

I know how to get the PackageName, and the TaskName, but in this case, I need the ProjectName.

回答1:

This is, admittedly, holy-set-the-house-on-fire-hacktastic, but it works when run via SSDT... You need a couple of things for this Script Task to work:
1. A variable to access so you can climb to its parent.
2. A reference to Microsoft.SqlServer.DTSRuntimeWrap

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

    public void Main()
    {
        RuntimeWrapper.IDTSProject100 p = ((RuntimeWrapper.IDTSProject100)((Package)Dts.Variables["User::myVar"].Parent).Project);
        string projectName = GetProjectName(p);

        Dts.TaskResult = (int)ScriptResults.Success;
    }

    private string GetProjectName(RuntimeWrapper.IDTSProject100 proj) 
    {
        System.Reflection.PropertyInfo pInfo = proj.GetType().GetProperty("Project", 
        System.Reflection.BindingFlags.Public | 
        System.Reflection.BindingFlags.NonPublic | 
        System.Reflection.BindingFlags.Instance);
        Project p = (Project)pInfo.GetValue(proj, null);

        return p.Name;
    }


    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
}

回答2:

This is a possible solution I tried a while ago.

Just Map System::VersionGUID to @Version and System::PackageID to @Package as the below query.

DECLARE @Package uniqueidentifier = ?
DECLARE @Version uniqueidentifier = ?
DECLARE @ProjectName nvarchar(100) = (
SELECT proj.name 
FROM [catalog].[packages] pkg
INNER JOIN catalog.projects proj ON pkg.project_id = proj.project_id
WHERE package_guid= @Package
AND version_guid = @Version
)

SELECT ISNULL(@ProjectName, 'running from ssdt') as ProjectName;

If the project is deployed in SSIS Catalogs and it is not running from SSDT you will get the project name, otherwise it will return null using the ISNULL function you can put whatever. I hope this can help you someway.

EDIT: version_guid clarification

The version_guid will be different if the project has been deployed in several folders.

Looking for documentation msdn site is very short about version_guid property.

Gets the version GUID generated when the package is first created. This field is read-only.

Asumming the creation of the package in the SSISDB will be at deployment the version_guid should be different for every project deployment.

I didn't test in deep but I recreated that scenario: deploy the same project in two different folders.

Note package_guid and version_guid columns.