Phidiax Tech Blog

I was recently asked,when using Azure Data Factory to run SQL Server Integration Services (SSIS) packages, if there was a good way to log information about that somewhere in Azure as well outside of using SQL Server Management Studio (SSMS). That got me thinking about if we could use Application Insights here. Being that we don't have access to the SSIS IR GAC (At least as of this writing), that would mean decompiling the Azure Application Insights library to use for logging since we can't add to the GAC (this code is based on the decompiled code of Application Insights library 2.4).

Additionally, it means configuring the Telemetry Client in code, and making sure that it is immediately flushed and written.

Attached is a Package Part that can easily be used within an event handler to track progress or generally within SSIS to track anything necessary.

Download Package Part here: ApplicationInsightsImport.dtsxp (922.7KB)

Using the ApplicationInsights.dtsxp Package Part

Once you have downloaded the package part and imported to your project in the Package Parts\Control Flow folder, you can use the Application Insights part in the desired event handler. Once you drag the package part to the handler, you need to configure the variables to be used by the package part, either using expressions or hard-coded values. The following variables should be setup as follows:

• User::InstrumentationKey - setup to contain the desired Application Insights account instrumentation key
• User::Message - setup to contain the text of the message to be traced to the application insights account
• User::Severity - setup to contain the severity of the event (0 to 4, 4 being Critical)
• User::TrackType - setup to contain the application insights entity to track as (this version only supports "Trace" for now)