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
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
Leave a Reply