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,

select

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.

create

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,

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.

add

Now click on the Details tab,

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)

code

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.

error

7 Comments

Join the conversation! Your thoughts help the community grow.

Subash10y ago
Good share
Raja10y ago
Nice share...
Bhuvanesh Mohankumar10y ago
Good one
Vignesh Mani10y ago
Good one
Thiruppathi R10y ago
good job..
Madhan Raghu10y ago
Thank you Humayun Kabir Mamun
Humayun Kabir Mamun10y ago
Nice...