Getting Error Description and Error Column Name in SSIS 2016...

If you check the Error Output Path of a SSIS Data Flow component, it only contains the ErrorCode and the ErrorColumn (actually this the ID value of the column). By looking at those two values, we cannot get a clear idea about the error.

I checked how it is possible to get the Error Description and the Error Column Name and found the below detail post.

But that post is written before the SQL Server 2016 is released and due to that there are some differences for the Code.

In this post I am going to provide the code and some enhancements done for how to get the Error Description and Error Column Name for SSIS 2016.

To get the Error Description and the Error Column Name, for the Error Output path, you have to add a Script Component.
Sample implementation will be as below, where we
- Union all the error records
- Get the Error Description and Error Column Name using a Script Component
- Write the error records with those details to a CSV file

In the Script Component, ErrorCode and ErrorColumn are selected as Input Columns

As Output Columns we need to add columns as ErrorDescription and ErrorColumnName, where the relevant values will be assigned in the code.

Then to add the require code, click on the Edit Script

In that the added code is as below

public override void Input0_ProcessInputRow(Input0Buffer Row)
         * Add your code here
        Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode).Trim();
        var componentMetaData130 = this.ComponentMetaData as IDTSComponentMetaData130;

        string errorColumnName = "";

        if (componentMetaData130 != null)
            if (Row.ErrorColumn > 0)
                errorColumnName = componentMetaData130.GetIdentificationStringByID(Row.ErrorColumn);
                errorColumnName = "NA";


        if (errorColumnName != "NA")
            string searchStr = "Columns[";
            int lenSearchStr = searchStr.Length;
            int startIndex = errorColumnName.IndexOf(searchStr) + lenSearchStr;
            errorColumnName = errorColumnName.Substring(startIndex);

        Row.ErrorColumnName = errorColumnName;

If you just get the column name using the GetIdentificationStringByID method, the output will be as below.
OLE DB Destination.Inputs[OLE DB Destination Input].Columns[COLUMN_NAME]

Actually for the who ever managing the data, they do not need to know the SSIS Component which produced the error and they will be only interested about the column name and the error.
That is why only the Column Name is extracted using the below section in the above code

if (errorColumnName != "NA")
            string searchStr = "Columns[";
            int lenSearchStr = searchStr.Length;
            int startIndex = errorColumnName.IndexOf(searchStr) + lenSearchStr;
            errorColumnName = errorColumnName.Substring(startIndex);

After that error records will be written to a CSV file and we can share it with responsible parties.

Email Alerts are not received...

In one of application, Email alerts are sent using SQL sp_send_dbmail stored procedure. Email alerts were working properly, but suddenly noticed email alerts are not received.

To troubleshoot the issue, first I checked the msdb.dbo.sysmail_event_log table using the below query.

SELECT log_id, event_type, log_date, description, process_id, mailitem_id, account_id, last_mod_date, last_mod_user
log_date DESC

If there is any error, you should see records with the event_type value as error.
In my case there are records with event_type value as error, and the description value for those records is same as below

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2018-02-01T10:41:27). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: Authentication required).  )

As per the error description, it should be due to the sender account issue.
The reason was the Sender password is changed and it is not updated in the Database Mail configurations. Once I updated the password in the Database Mail configurations, email alerts started to work properly.

Moving file from one folder to another in Azure Blob Storage using SSIS

In this post I'll explain how we can move a file from a folder in Azure Blob Storage Container to another folder in the same Container, and delete the original file.

Lets assume the Blob Container name is BlobContainer and the folder names are Folder1 and Folder2. Assume the file name as BlobFile.csv
File will be moved from one folder to another based on the file URL, by using WebClient in C#.

You can generate the File URL as below
(blobstorage is the account name)

Then existing file URL is

URL for the move file is

As I mentioned in my previous post, you can get the file name of the file, available in the Azure Blob Storage using a Foreach Loop Container and possible to set those File URL values based on the file name.

Addition to above we need to know the Shared Access Signature value for each folder.
If you do not have those values, you can get the Shared Access Signature value as below.
To get it for Folder1, go to the Folder1 using Microsoft Azure Storage Explorer and right click on Folder1.
Then select Get Shared Access Signature.

Then you will get a window as below.

In that first things is make sure that you have provided the Expiry time accordingly.
Then put the tick on Write and Delete as well, since it is needed for our requirement.
After that click on Create and you will get the below window

To get the Shared Access Signature value, click on the Copy button under Query string.
The best things is to have that value in a Project Parameter in SSIS. Then when ever needed, you will be able to easily change it.

Follow the same steps for Folder2 as well.

As a best practice, I stored the Shared Access Signature values in two Project Parameters as SASFolder1 and SASFolder2.
Blob file URLs are store in package variables as OriginalBlobFileURL and MoveBlobFileURL

To move the file, I used a Script Task and Variables section of the Script Task Editor is as below

Click on the Edit Script to add the below code

string sasFolder1 = Dts.Variables["$Project::SASFolder1"].Value.ToString();
string sasFolder2 = Dts.Variables["$Project::SASFolder2"].Value.ToString();
string originalBlobFileURL = Dts.Variables["User::OriginalBlobFileURL"].Value.ToString();
string moveBlobFileURL = Dts.Variables["User::MoveBlobFileURL"].Value.ToString();

        HttpClient client = new HttpClient();
        client.DefaultRequestHeaders.Add("x-ms-copy-source", originalBlobFileURL + sasFolder1);
        //copy file from Folder1 to Folder2
       HttpResponseMessage response = await client.PutAsync(moveBlobFileURL + sasFolder2, null);
       if (response.StatusCode == HttpStatusCode.Accepted || response.StatusCode == HttpStatusCode.Created)
              // delete the original file in the Folder1
              HttpResponseMessage result = await client.DeleteAsync(originalBlobFileURL + sasFolder1);
              if (result.StatusCode == HttpStatusCode.Accepted || result.StatusCode == HttpStatusCode.Created)
                        Dts.TaskResult = (int)ScriptResults.Success;
       Dts.TaskResult = (int)ScriptResults.Failure;
catch (Exception ex)
      Dts.TaskResult = (int)ScriptResults.Failure;

Build the code and Save it.
Click Ok button in the Script Task Editor.
That is all...

Configuring SSIS Foreach Loop Container for Azure Blob Storage

Like we iterate through files in File System, we can iterate through files in Azure Blob Storage, using SSIS Foreach Loop Container.
If you consider about the Blob Storage structure, there will be Containers and inside those containers, you will find Folders.

Below Project Parameters are added to store those configurations information
BlobContainer - Stores the Blob Container name
BlobFolder - Stores the Folder name
ConnStr_AzureStorage - Stores the connection string value for the Azure Blob Storage
(Ex: DefaultEndpointsProtocol=https;AccountName=blobstorageaccountname;AccountKey=accountkey;

Addition to those, package variable is added to store the Blob File Name as BlobFileName.

To create a connection to the Azure Blob Storage, right click on the Connection Managers section in the Package and select New Connection

In the Add SSIS Connection Manager window, select AzureStorage type and click Add

Then you will get the Azure Storage Connection Manager Editor window.

In that select Use Azure Account and provide the relevant Storage account name and Account key values.
Then test the connection using Test Connection button.
If it is Successful, click on the OK button. Otherwise please check the provided Storage account name and Account key values.
To set the Connection String value using the ConnStr_AzureStorage Project Parameter, select the Azure Connection and go the Properties tab.
In that under Expressions section, add the ConnStr_AzureStorage project parameter value to the ConnectionString property.

Once it is done, to configure Foreach Loop Container, drag and drop Foreach Loop Container to the package Control Flow and double click on it.
In the Collection section, click on the down arrow under Enumerator, and select Foreach Azure Blob Enumerator

Then select the previously added Azure Connection for the Azure storage connection manager and to provide the relevant values for the Blob container name and Blob directory values, click on the ... in front of the Expressions.

Set the BlobContainer  and BlobPath property values using the BlobContainer and BlobFolder project parameters.

To assign the Blob File Name to the BlobFileName variable, go to the Variable Mappings section and set the variable.

Now you have configured the Foreach Loop Container for Azure Blob Storage and you can do the required operations inside the Foreach Loop Container by using the File Name available in the BlobFileName variable.

Getting Error Description and Error Column Name in SSIS 2016...

If you check the Error Output Path of a SSIS Data Flow component, it only contains the ErrorCode and the ErrorColumn (actually this the I...