CODEWITHSUNDEEP
ssissql-server-data-tools
casenonsensitive
casenonsensitive

Reputation: 950

How to find out if a package is run locally in visual studio or on the server?

I have some script code in an SSIS package that would work differently when run in SSDT/Visual Studio than on the server. Development tests are done in Visual Studio and the production runs on the SQL server (version 2017)

What's the best approach to find out where the package is running?

I could use a project/package parameter, but it's less automatic and more error prone. E.g. when forgetting to re-set the parameter to the correct value for the target environment.

Upvotes: 1

Views: 552

Answers (1)

billinkc
billinkc

Reputation: 61211

While I've not tested all the ways it can be abused, my first thought would be to check the value of System::ServerExecutionID This is a system variable that is zero during design and run time in visual studio.

When executed on the server, this variable will be populated with the Operation ID that is stored in SSISDB.catalog.operations(operation_id). My packages use an audit table that explicitly logs that ServerExecutionID to the insert/update/delete metrics associated to that load.

Upvotes: 2

Related Questions