SSIS Location of currently running package

I have been wanting figure this out for a while.  Here is the problem.  You run your package in a package that is deployed using the SQL 2012 Project deployment model.  You want to get the folder and project that the current package is running under.  How do you do this?

First you need a project level connection manager that points to the SSISDB that you are running your package on.  The database administrator can verify that the connection manager is pointing to the current server.

Next you create an Execute SQL task that uses that connection and the following SQL Script:

declare @Package uniqueidentifier = ?;
declare @Version uniqueidentifier = ?;

declare @FullName nvarchar(1000) = (
SELECT f.name + '\' + pr.name + '\' + p.name as FullName

  FROM  [catalog].[packages] p
  inner join catalog.projects pr
  on p.project_id = pr.project_id
  inner join catalog.folders f
  on pr.folder_id = f.folder_id 
  where package_guid= @Package
  and version_guid = @Version
);

select isnull(@FullName, 'interactive') as FullName;

Map the System::PackageID variable to parameter 0 with data type GUID.  Map System::VersionGUID variable to parameter 1 with data type GUID. Next set the ResultSet to Single Row.  Map the FullName to whatever string variable you want to put the name of the package.

When you run this from SSDT, it will always return interactive, because the current version of the package has not been deployed to SQL Server.  When you deploy the package, the version GUID will change.  However, if you deploy this to SQL Server, the Execute SQL task should set your variable to the full path of your package.

I haven’t done extensive testing.  Let me know if it works for you.

About Russ

Russ Loski is a data warehouse developer focusing on SSIS and SQL Server development. He has worked with SQL Server since 1998. He has used various technologies to move data from one source to others. He has worked for well-known companies such as the Bank of America, the Dallas Cowboys Football Club, T-Mobile and Coca Cola Enterprises. Currently he is developing a Data Warehouse for a large health care organization.
This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

Leave a Reply