Show SSIS error messages in the ADF monitor

Case
I want to see SSIS error messages in the ADF monitor or in the ADF email notification so that I can have a quick look to determine the severity of the problem without logging in with SSMS to the catalog in Azure. Is that possible?

Need more details in case of an error

Solution
It is possible but it requires to adjust the T-SQL statement that executes the package. Please follow all steps in this blog post, but replace the T-SQL code from step 4 with the code below.

The change is in the last part only. Previously it only showed that the execution failed, but now it also retrieves error messages from the catalog. Because the space is a bit limited we only show 7 errors. Errors with 'validation' in the text are less useful for the quick look we want. So those are filtered out as well. All messages are separated with a linefeed for a better overview.

-- Variables for execution and error message
declare @err_msg as varchar(8000) 
declare @err_msg_part as varchar(1000) 
declare @execution_id as bigint

-- Create execution and fill @execution_id variable
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'SSISJoost', @project_name=N'MyAzureProject', @use32bitruntime=False, @reference_id=2, @useanyworker=True, @runinscaleout=True
 
-- Set logging level: 0=None, 1=Basic, 2=Performance, 3=Verbose 
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=1 
 
-- Set synchonized option 0=A-SYNCHRONIZED, 1=SYNCHRONIZED 
-- A-SYNCHRONIZED: don't wait for the result
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'SYNCHRONIZED',  @parameter_value=1 
 
-- Execute the package with parameters from above
EXEC [SSISDB].[catalog].[start_execution] @execution_id, @retry_count=0

-- Check if the package executed successfully (only for SYNCHRONIZED execution)
IF (SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id=@execution_id) <> 7
BEGIN
    SET @err_msg = 'Your package execution did not succeed for execution ID: ' + CAST(@execution_id AS NVARCHAR(20)) + CHAR(13) + CHAR(10)
    DECLARE err_cursor CURSOR FOR 
    SELECT  top(7) CAST([message] as varchar(1000)) as message -- Max 7 errors
    FROM  [catalog].[event_messages]
    WHERE  [event_name] = 'OnError' -- Only show errors
    AND   [operation_id] = @execution_id
    AND   [message]  not like '%validation%'
    -- Exclude less usefull validation messages like:    
    -- Error: One or more component failed validation.
    -- Error: There were errors during task validation.
    -- Error: Error 0xC0012050 while executing package from project reference package "xxx". Package failed validation from the ExecutePackage task. The package cannot run.
    -- Error: xxx failed validation and returned error code 0xC020801C.
    -- Error: "xxx" failed validation and returned validation status "VS_ISBROKEN".

    OPEN err_cursor  
    FETCH NEXT FROM err_cursor INTO @err_msg_part  
    WHILE @@FETCH_STATUS = 0  
    BEGIN
        SET @err_msg = @err_msg + @err_msg_part + CHAR(13) + CHAR(10) 
        FETCH NEXT FROM err_cursor INTO @err_msg_part 
    END 
    CLOSE err_cursor  
    DEALLOCATE err_cursor 

    RAISERROR(@err_msg,15,1)
END

Now it shows more details in the ADF monitor and if you are also using the ADF email notifications then the same messages will appear in the email. Feel free to suggest improvements in the comments.

More error details

Note that it is not a complete overview of all messages, but in most cases it should be enough for the seasoned developer to quickly identify the problem and take actions to solve it.

*update 13-04-2018: There is a new Execute SSIS Package activity, but without error options.*

SSIS Snack: The semaphore timeout period has expired

Case
My SSIS package that runs in Azure Data Factory V2 (and gets data from an on-premises source) suddenly stops working and throws an communication error. After this error it won't run again. What is happening?

TCP Provider: The semaphore timeout period has expired

DFT - DIM_xxxxx:Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Communication link failure".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "TCP Provider: The semaphore timeout period has expired.".


Solution
The error seems to be caused by a network communication hiccup with our on-premises source which is connected with a VNET in Azure. Although I do not know the actual cause of the error. There is a solution: restart your Integration Runtime.

1) ADF dashboard
Go to your Azure Data Factory (ADF) that hosts your Integration Runtime and click on Author & Monitor within the Quick links section. This will open the ADF dashboard.

Author & Monitor

2) Stop & Start IR
Click on Author (pencil), on Connections and then on Integration Runtimes (IR). Then Stop and start your IR. This could take up to 30 minutes! After that rerun your package and it should be working again.

Stop & Start IR

Please let me know in the comments whether it worked for you and if you found the actual cause of the error.

Getting error column name in SSIS 2016

Case
In SQL 2016 CTP 2.3 Microsoft introduced a new simple way with to get the name of the column causing the error with some .NET code in a Script Component. In the final release this code doesn't work.

Solution
Not sure why, but they changed the code. Instead of one line we now need two lines. Below the complete example with the new code.


1) Data Flow Task
For this example we have a Flat File Source and to throw an error there is a column in the textfile with a too large value causing a truncation error. To catch the error details we redirect all errors of the Flat File Source to an Error Output. You can find these settings by editing the Flat File Source component or by connecting its red output to an other transformation.

Redirect errors to Error Output

2) Add Script Component
The Error Output is redirected to a Script Component (type transformation). It should look something like this below. Give it a suitable name like "SCR- Get Error Details".

Add Script Component Transformation

3) Input Columns
Edit the Script Components and go to the Input Columns page and select the ErrorCode (for getting an error description) and the ErrorColumn (for getting a column name) as ReadOnly input columns.

Input Columns

4) Output Columns
Create two output columns with the Data Type String (DT_STR). For this example I used 255 for the length, but that could probably be a little smaller. The names are ErrorDescription and ErrorColumnName.

Output Columns

5) The Code
Now go to the first page to choose your Scripting Language and then click on the Edit Script button to open the VSTA environment. Then locate the Input0_ProcessInputRow method at the bottom and add the following three lines of code to it.

// C# Code
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    // Getting description already worked in previous versions of SSIS
    Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);

    // componentMetaData (starting with a lowercase "c") is just a name.
    // You can change that name if you like, but also change it in the
    // second row.
    IDTSComponentMetaData130 componentMetaData = this.ComponentMetaData as IDTSComponentMetaData130;
    Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);

And VB.NET code

' VB Code
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    ' Getting description already worked in previous versions of SSIS
    Row.ErrorDescription = Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)

    ' componentMetaData (starting with a lowercase "c") Is just a name.
    ' You can change that name if you Like, but also change it in the
    ' second row.
    Dim componentMetaData As IDTSComponentMetaData130 = TryCast(Me.ComponentMetaData, IDTSComponentMetaData130)
    Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn)
End Sub

6) Testing
Close the VSTA environment to save the code and press OK the close editor. Now add a Data Viewer behind the Script Component Transformation to see the results

Error Description and ColumnName

SQL Server 2016 CTP 2.3 Get error columnname

Case
Before SSIS 2016 there was no way to get the name of the column that caused the error. Atleast not without a custom transformation or a very complicated script that looped through a copy of your package to get all details. Now there is a simple solution available.

Solution
There was already a script available to get the error description. Now you can use a similar way to get the columnname. First download SSDT 2015 (SSDT and SSDT-BI have been merged!).

* UPDATE: code below outdated / changed click here for new code *

1) Data Flow Task
For this example we have a Flat File Source and to throw an error there is a column in the textfile with a too large value causing a truncation error. To catch the error details we redirect all errors of the Flat File Source to an Error Output. You can find these settings by editing the Flat File Source component or by connecting its red output to an other transformation.

Redirect errors to Error Output

2) Add Script Component
The Error Output is redirected to a Script Component (type transformation). It should look something like this below. Give it a suitable name like "SCR- Get Error Details".

Add Script Component Transformation

3) Input Columns
Edit the Script Components and go to the Input Columns page and select the ErrorCode (for getting an error description) and the ErrorColumn (for getting a column name) as ReadOnly input columns.

Input Columns

4) Output Columns
Create two output columns with the Data Type String (DT_STR). For this example I used 255 for the length, but that could probably be a little smaller. The names are ErrorDescription and ErrorColumnName.

Output Columns

5) The Code
Now go to the first page to choose your Scripting Language and then click on the Edit Script button to open the VSTA environment. Then locate the Input0_ProcessInputRow method at the bottom and add the following two lines of code.

// C# Code
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
    Row.ErrorColumnName = this.ComponentMetaData.GetIdentificationStringByLineageID(Row.ErrorColumn);
}

And VB.NET code

' VB Code
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    Row.ErrorDescription = Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)
    Row.ErrorColumnName = Me.ComponentMetaData.GetIdentificationStringByLineageID(Row.ErrorColumn)
End Sub

6) Testing
Now add a Data Viewer behind the Script Component Transformation to see the results

Error Description and ColumnName

BIML doesn't recognize system variable ServerExecutionID

Case
I want to use the SSIS System Variable ServerExecutionID as a parameter for an Execute SQL Task in a BIML Script, but it doesn't recognize it and gives an error:


Could not resolve reference to 'System.ServerExecutionID' of type 'VariableBase'. 'VariableName="System.ServerExecutionID"' is invalid.

Solution
The current version of BIDS/BIML doesn't recognize all system variables (for example
LocaleId and ServerExecutionID). Other system variables like VersionMajor or VersionBuild will work. You can overcome this by manually adding these variables in your BIML Script.



<Variable Name="ServerExecutionID" DataType="Int64" Namespace="System">0</Variable>

And if you now run the package (in the catalog) the table gets filled with the System variable ServerExecutionID:

Number added, it works!

The process cannot acces the file 'ssisproject.ispac' because it is being used by another process.

Case
I want to run an SSIS 2012 package but I'm getting an error:

ispac file in use by other process

System.IO.IOException: The process cannot access the file 'c:\folder\ssisproject.ispac' because it is being used by another process.
     at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
     at System.IO.File.Delete(String path)
     at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow)
     at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow)

Solution
You have probably already restarted SSDT, but that didn't help. Open the Task Manager (Ctrl + Shift + Esc) and locate DtsDebugHost.exe under Processes and end that process (could be multiple times, end them all). Now try running the package again.

Task Manager, End SSIS Debug Host

This happens when SSDT/Visual Studio crashes during runtime. If you kill SSDT then the SSIS Debug Host will still be active locking the ISPAC file.

BIML: An error occurred while parsing EntityName

Case
I'm creating an SSIS package with BIML and I want to add an expression in my Derived Column with an Logical AND &&.

    
        ISNULL([Column1]) && ISNULL([Column2])
    

But when I check the BIML Script for errors with the 'Check Biml for Errors'-option in the context menu, I got an error: An error occurred while parsing EntityName

An error occurred while parsing EntityName

When I replace it with an Logical Or || it works without errors. What's wrong?

Solution
An XML document doesn't like an ampersand (&). You have to replace it by &amp; or enclose it with CDATA.

    
        ISNULL([Column1]) && ISNULL([Column2])
    



    
        ISNULL([Column1]) <![CDATA[&&]]> ISNULL([Column2])
    

Now you can build the Biml Script without errors.

Stop package when foreach loop does not find any files

Case
When the Foreach Loop File Enumerator is empty, SSIS will throw a warning, but I want to fail the package. How do I do that?

The For Each File enumerator is empty.

Solution
This solution will count the number of loops of the Foreach Loop and fire an error if the count is zero.

Validate number of files

1) Add variable
Add an integer variable, named "FileCount", to count the number of files. The variable scope is package.

integer variable

2) Add Script Task for counting
Add a Script Task within the Foreach Loop and name it "Increment Counter". You can connect it with a Precedemce Constraint to other tasks within your Foreach Loop, but that's not necessary.
Edit the Script Task and add the variable from step 1 as a ReadWrite variable.

ReadWriteVariables

3) The script for counting
Edit the script and add the follow C# code to the Main method.

// C# Code
public void Main()
{
    // Get value of counter variable and increment with 1
    Dts.Variables["User::FileCount"].Value = Convert.ToInt32(Dts.Variables["User::FileCount"].Value) + 1;

    Dts.TaskResult = (int)ScriptResults.Success;
}

or VB.Net

' VB.Net Code
Public Sub Main()
 ' Get value of counter variable and increment with 1
 Dts.Variables("User::FileCount").Value = Convert.ToInt32(Dts.Variables("User::FileCount").Value) + 1

 Dts.TaskResult = ScriptResults.Success
End Sub

4) Add Script Task for validating
Add a Script Task outside the Foreach Loop and connect it with a Precendence Constraint to your Foreach Loop. Name it "Validate Counter". Edit the Script Task and add the variable from step 1 as ReadOnly variable.

ReadOnlyVariables

5) The Script for validating
Edit the script and add the follow C# code to the Main method.

// C# Code
public void Main()
{
    // Check if counter is zero
    if (Dts.Variables["User::FileCount"].Value.ToString() == "0")
    {
        // Throw error event and fail Script Task
        Dts.Events.FireError(-1, "Foreach Loop", "The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.", String.Empty, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
    else
    {
        // Files where found so no error
        Dts.TaskResult = (int)ScriptResults.Success;
    }
}

or VB.Net

' VB.Net Code
Public Sub Main()
 ' Check if counter is zero
 If (Dts.Variables("User::FileCount").Value.ToString() = "0") Then
  ' Throw error event and fail Script Task
  Dts.Events.FireError(-1, "Foreach Loop", "The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.", String.Empty, 0)
  Dts.TaskResult = ScriptResults.Failure
 Else
  ' Files where found so no error
  Dts.TaskResult = ScriptResults.Success
 End If
End Sub

6) The Result
Run the package and check the Progress tab in your package.

The result

Alternative solution
If there are no tasks behind your Foreach Loop you could also try something with an event handler. Because you know the Foreach Loop will throw a warning you could check for that warning and thrown an error. Haven't test it thoroughly, but it will look something like this:

A) Add Script Task in OnWarning event handler
Go to the eventhandler tab (1) and add an OnWarning event handler (2) for your Foreach Loop and add Script Task (3) that reads two system variables (4) System::ErrorCode and System::ErrorDescription as ReadOnly variables

OnWarning Event handler

B) The script
Edit the script and add the follow code to the Main method.

// C# Code
public void Main()
{
 // Check if last error(/warning) is about empty foreach loop: 
 // Warning: The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.
 if (Dts.Variables["System::ErrorCode"].Value.ToString().Equals("-2147368956"))
 {
  // Then throw error with message of last warning (or throw your own message)
  Dts.Events.FireError(0, "Foreach Loop", Dts.Variables["System::ErrorDescription"].Value.ToString(), String.Empty, 0);
  Dts.TaskResult = (int)ScriptResults.Failure;
 }
 else
 {
  Dts.TaskResult = (int)ScriptResults.Success;
 }
}

or VB.Net

' VB.Net Code
Public Sub Main()
 ' Check if last error(/warning) is about empty foreach loop: 
 ' Warning: The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.
 If (Dts.Variables("System::ErrorCode").Value.ToString().Equals("-2147368956")) Then
  ' Then throw error with message of last warning (or throw your own message)
  Dts.Events.FireError(0, "Foreach Loop", Dts.Variables("System::ErrorDescription").Value.ToString(), String.Empty, 0)
  Dts.TaskResult = ScriptResults.Failure
 Else
  Dts.TaskResult = ScriptResults.Success
 End If
End Sub

Let me know if you have an other alternative.