Post reply

Which SSIS Package / Task created which SPID?

  • August 2, 2017 at 10:57 am

    #402383

    Hello there,

    I am working on a big data warehouse and over time and with the increase of data sources, we have begun running into more and more deadlocks other concurrency issues.
    Debugging this and reconstructing / understanding what's happening is a royal pain.

    I would like to be able to tell which SSIS Package & Task has created which SPID on the SQL Server.
    Is there any way to accomplish this? Usually a while of scouring Stackoverflow and other sources resolves these issues, but I'm at a loss here.

  • August 3, 2017 at 6:37 am

    #1953945

    Velym - Wednesday, August 2, 2017 10:57 AM

    Hello there,

    I am working on a big data warehouse and over time and with the increase of data sources, we have begun running into more and more deadlocks other concurrency issues.
    Debugging this and reconstructing / understanding what's happening is a royal pain.

    I would like to be able to tell which SSIS Package & Task has created which SPID on the SQL Server.
    Is there any way to accomplish this? Usually a while of scouring Stackoverflow and other sources resolves these issues, but I'm at a loss here.

    There is something which you may be able to use, though it will require some config/development work to set up.
    Within any particular package, you will have one or more OLEDB connections to your database. I will make the assumption that you are passing the connection strings for the database via parameters.
    For each connection, create an Expression which overrides the connection string, making use of the Application Name property:
    @[$Package::ConnStr] + "Application Name=" + @[System::PackageName] + ";"
    Now, whenever the connection is used by the package, the connection will have a package-specific application name.
    This application name shows up in the 'ProgramName' column when executing sp_who2:

  • August 3, 2017 at 9:24 am

    #1953992

    Thank you! I did not know the Application Name Parameter for connection strings.
    We will test that and may use it in the future!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply