One morning, you discovered your SSIS package failed to run with the following error message:

Started: 5:00:03 AM
Error: 2021-10-08 05:00:04.62
Code: 0xC0016016
Source: SSIS_Package_ITNota
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error

This is typically caused by a simple setting that is too often overlooked during deployment.

In general, you do not want to save any connection ID and password in your package so you can inject the value from variables that you store either in a database or if you use a Package Deployment Model, then in an XML saved in the *.dtsConfig file.

So what you want to do before you build the package for PROD deployment, you want to set the package property Protection Level to DontSaveSensitive and you set all the values for your variables in your configuration file.

Steps to Set SSIS Package Property Level

1. With your SSIS package opened, under the Control Flow tab, on a blank area, right-click your mouse and select Properties or press ALT+ENTER.

2. Under Properties, go to Security, and look for ProtectionLevel. Check the value. By default, it’s set to EncryptSensitiveWithUserKey.

   

   The ProtectionLevel should be set to DontSaveSensitive.

   

Save the package and if the configuration is already setup, it’s ready to be deployed.

Further Reading

Failed to decrypt protected XML node “DTS:Password” with error 0x8009000B “Key not valid for use in specified state”

This post will demonstrate one way to add custom logging to SSIS Package. This is still considered one of the better practice that comes in handy when we need to troubleshoot SSIS packages and track progress.

There’s no better way to explain the concept other than giving a simple case example on how to add it to a real SSIS package.

For starter, the package in this example does a simple data load from LDAP table to a SQL Server table with some minimal check done with a Script Component.

Setup Global Variables for Logging

Before we do anything else, it’s recommended to setup a few global variables that we can use to keep track of the log variables.

Setup global variables for the log folder and filename with a prefix of gv for Global Variable. The use of prefix is very handy since all those global variables will be sorted nicely and will be easy to find during troubleshooting.

*One of the global variable is made up with an expression to dynamically name the log file according to today’s date.

@[Log::gv_RootPath] + "\\" +  @[Log::gv_LogDir] + "\\" + REPLACE(@[Log::gv_LogFileName], ".log", "") + "-" + SUBSTRING((DT_STR, 50, 1252) GETDATE(), 1, 10) + ".log"

Setup Log File Connection Manager

We are going to write the log to a text file, so create a new connection manager by selecting New Flat File Connection… and name it CM_FF_DST_Log.

It’s not too important what you want to name the file, because we want to use a dynamic file name based on the date that we’ve already setup with gv_LogFile global variable. Once, it’s setup you can do a right-click on the CM_FF_DST_Log and check on the Properties.

Expand the Expressions and click on the ellipsis in ConnectionString, an Expression Builder window will be opened.

Expand the Variables and Parameters and double-click on the Log::gv_LogFile so it will go to the Expression: box and click OK.

Now, when you double-click on the CM_FF_DST_Log connection manager, the log filename will be dynamically populated according to today’s date (see File: below). You can close the window by clicking OK or Cancel as we do not need to modify this setup further.

Enable SSIS Logging

We finished the connection manager for the log file, but we still need to enable SSIS Logging and associate that with the log connection manager. In order to do this, go to the top menu and click on SSIS and select Logging… as shown below.

You will next see a new Configure SSIS Logs window. Make sure all checkboxes under the Containers are checked. We also need to make sure that on the right pane of that window, under Details we check at least OnError, OnTaskFailed, and OnWarning.

Important: Make sure this step is done for each container listed on the left side (yes, it’s tedious, but fortunately you only need to set this up once per project).

Go to the Providers and Logs, now we want to setup the log provider to use the flat file connection manager CM_FF_DST_Log. If you setup the connection manager correctly, CM_FF_DST_Log should be available on the Configuration drop-down list. This part also needs to be done for each container.

Once this setup is complete, we’re ready to add our log in SSIS workflow.

Write the Log

Try to put the tasks within containers for each task you want to add log to.

If you double-click on the SCR Log Start and click on the Edit Script…, here’s the C# script contained in the task:

SCR Log Start

public void Main()
{
  Dts.Log("Package starting...", 99, null);

  foreach(Variable v in Dts.Variables)
  {
    Dts.Log("Name: " + v.Name, 99, null);
    Dts.Log("Value: " + v.Value.ToString(), 99, null);
  }
 
  Dts.TaskResult = (int)ScriptResults.Success;
}

Data Flow Counters

Under the Data Flow tab, you can see that from each data source, there’s a Row Count component that is attached to it. In this process, the values from the Row Count component will be passed on to the global variables we setup. In this example, they are gvCNT_LDAPRowCount and gvCNT_LDAPRowCountBlanks.

And we include a detailed count of each count and grand total in a Script Task called SCR LDAP Row Counts. Just make sure we also include the global variables in the ReadOnlyVariables like so, then click on the Edit Script….

Add the calculation logic and write to the log.

public void Main()
{
  // Get LDAP row count with sAMAccountName row counts
  int rowLDAPCount = (int)Dts.Variables["CNT::gvCNT_LDAPRowCount"].Value;
 
  // Get LDAP row count that are blank
  int rowLDAPBlanks = (int)Dts.Variables["CNT::gvCNT_LDAPRowCountBlanks"].Value;
 
  // Total rows
  int rowTotalLDAPCount = 0;
  rowTotalLDAPCount = rowLDAPCount + rowLDAPBlanks;
 
  // Log if LDAP has no records.
  if (rowTotalLDAPCount > 0)
  {
    Dts.Log(String.Format(" Total records with NT ID accounts: {0}", rowLDAPCount.ToString("###,###,###")), 99, null);
    Dts.Log(String.Format(" Total records without NT ID accounts: {0}", rowLDAPBlanks.ToString("###,###,###")), 99, null);
    Dts.Log(String.Format(" Total records from LDAP source: {0}", rowTotalLDAPCount.ToString("###,###,###")), 99, null);
  } else
  {
    Dts.Log(String.Format(" No record is found in LDAP source. {0}", rowTotalLDAPCount.ToString("###,###,###")), 99, null);
  }
  
  Dts.TaskResult = (int)ScriptResults.Success;
}

At the end of the process flow, just add another script SCR Log Finish to indicate the end of the logging session after the package run.

SCR Log Finish

public void Main()
{
  Dts.Log("~ Log Cleanup Package completed. ~", 99, null);
 
  Dts.TaskResult = (int)ScriptResults.Success;
}

So what does the log file look like? Here’s an example what’s reported. It’s probably easier to read if you look at the raw file instead.

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
PackageStart,ITNOTA-SERVER,ITNOTA\UserId,SSIS-ITNota,{GUID1},{MACHINEGUID},3/5/2019 3:40:08 AM,3/5/2019 3:40:08 AM,0,0x,Beginning of package execution.

User:ScriptTaskLogEntry,ITNOTA-SERVER,ITNOTA\UserId,SCR_LogStart,{GUID2},{MACHINEGUID},3/5/2019 3:40:08 AM,3/5/2019 3:40:08 AM,99,(null),Package starting...
User:ScriptTaskLogEntry,ITNOTA-SERVER,ITNOTA\UserId,SCR LDAP Row Counts,{GUID3},{MACHINEGUID},3/5/2019 3:41:07 AM,3/5/2019 3:41:07 AM,99,(null), Total records with NT ID accounts: 11,236
User:ScriptTaskLogEntry,ITNOTA-SERVER,ITNOTA\UserId,SCR LDAP Row Counts,{GUID3},{MACHINEGUID},3/5/2019 3:41:07 AM,3/5/2019 3:41:07 AM,99,(null), Total records without NT ID accounts: 10,009
User:ScriptTaskLogEntry,ITNOTA-SERVER,ITNOTA\UserId,SCR LDAP Row Counts,{GUID3},{MACHINEGUID},3/5/2019 3:41:07 AM,3/5/2019 3:41:07 AM,99,(null), Total records from LDAP source: 21,245
User:ScriptTaskLogEntry,ITNOTA-SERVER,ITNOTA\UserId,SCR Log Finish,{GUID4},{MACHINEGUID},3/5/2019 3:41:07 AM,3/5/2019 3:41:07 AM,99,(null),~ Log Cleanup Package completed. ~
PackageEnd,ITNOTA-SERVER,ITNOTA\UserId,SSIS-ITNota,{GUID1},{MACHINEGUID},3/5/2019 3:41:07 AM,3/5/2019 3:41:07 AM,0,0x,End of package execution.

That’s all there is to it to add a custom logging to SSIS package.

This may look like a lot of work initially, but as your SSIS package grows in complexity it will save you so much time in diagnosis and troubleshooting that you will thank yourself later.

Hope it helps.

Further Reading

SSIS Best Practice with Naming Conventions
How to Create SSIS Package in Visual Studio 2017
How to Build SSIS Package for Different SQL Server Version with Visual Studio 2017 and SSDT