ssis - how to set customized variable to system variable

Question

I have added a variable named "failed_msg"

how can I set the value @[System::TaskName] to my new add variable?

I would like to insert error message with task name into ssis log table in case there is failure

how to do that?

I am using visual studio 2012

Answer 1

Hi @nononame2021 ,

It is done automatically, i.e. logging, including SSIS Task name.
You just need to know your execution operation_id value.

Please see how get that info below.
It contains lots of useful info: project name, package name, event name, message, etc.
One column - message_source_name - is a SSIS Task name.

SQL

USE SSISDB;  
GO  
  
SELECT TOP(1000) *  
FROM SSISDB.CATALOG.EVENT_MESSAGES  
WHERE operation_id = 3673  
	--AND package_name = 'RESTAPI_Calls.dtsx'  
	--AND [message] LIKE '%:Information:%'  
	--AND [message] LIKE '%:Error:%'  
	--AND [message] LIKE '%Fetch%'  
	--AND [message] LIKE '%"Oracle Destination" wrote%'  
	--AND event_name IN ('OnError', 'OnWarning')  
ORDER BY message_time DESC;  

Answer 2

Hi @nononame2021 ,

You may also refer how-to-query-ssisdb-to-find-out-the-errors-in-the-packages which has provided several codes to find the error in SSMS.

Hope it will be helpful.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.