SSIS: Write Custom Message Or Variable Value In SSIS Log File

Scenario

I have one SSIS package which will loop through the list of SharePoint sites which loads the data from SharePoint lists to SQL Server. I deployed this SSIS package in my production server, and this package fails due to access issues of some SharePoint lists. The client wants to know the exact SharePoint site at which the package fails.

Solution

Logging is an important part of SSIS package development, so in this scenario we need to enable the log to debug errors in a package and we want to write a for Loop mapping variables in the log file to know at which point SSIS package gets failure.

In order to demonstrate the above scenario I created two tables in SQL Server, namely Source table and Destination table, source table contains one field with varchar data type which contains both integer value and string value. Destination table contains one field with integer data type.

In SSIS package I am going to loop through each and every row in Source table then insert the value into the destination so when I try to load the string value from source to destination table I will get an error. I want to know at which poin,t meaning at which value, I will get error.

Step 1

Below is the script to create and populate the values into the source table,

--Creating Source table

CREATE TABLE [dbo].[SourceTable]
(
[Column_Value] [varchar](10)
)

--Inserting values into Source table

INSERT INTO [dbo].[SourceTable] VALUES
('100'),('200'),('300'),('400'),('500'),('Apple'),('Ball'),('Cat')

--Creting Destination table

CREATE TABLE [dbo].[DestinationTable]
(
[Column_Value] [int]
)

Step 2

Open SQL server business intelligence development studio.

Go to File -> New -> Project (Shortcut: CTRL + SHIFT + N)

Select -> integration service project -> Give project Name, Folder Name,



Step 3

Right click on the package design area in control flow tab then click Variables



Drag and Drop Execute Sql Task and configure the properties like below,



Create one object variable and map that variable in the result set tab.



Step 4

Drag and drop for each loop container then connect execute sql task with for each loop container. Configure the for/each loop container properties.



Then go to variable mapping; map one string variable with the each looping value,



Step 5

Drag and drop another Execute SQL Task inside the for each loop container.



Then go to parameter mapping configure like below,



Step 6

Right click on the package design area in control flow tab then click Logging…

Check the package in container pane -> then click Text file in provider type -> then click ADD button.



Now click on the Details tab,



Step 7

Select Execute sql task 1 in the executable tree then OnError in the Event handler.



Drag and drop Script task then write the below code.

Dts.Events.FireInformation(-1, "CustomMessage", "Values : " & Dts.Variables("LoopsValues").Value.ToString, String.Empty, -1, False)



Result

When I run the package it shows error. So I open the Log file to know at which value I will get error. So I understand now that I will get error when I try to convert string Apple to integer.