Hi, I am looking for storing the audit information in a seperate table, so that troubleshooting gets easier, in case of any issues in future. This is what I am planning to do. Create an even handler to be invoked at OnPostExecute. This event will invoke a SQL task. The SQL Task would insert the following information into the Audit_Log table. 1) Data Flow Task Name - System::TaskName2) Package Name - System::PackageName3) Executing Date - GETDATE()4) # Input Rows - Name of the variable, that points to the Row count transformation immediately after the Source.5) # Target Insert Rows - Name of the variable, that points to the Row count transformation immediately before the target (where data is inserted).6) # Target Update Rows - Name of the variable, that points to the Row count transformation immediately before the target (where data is updated).7) Load Status - Where to get this information?8) Duration - DATEDIFF([System::ContainerStartTime] , GETDATE()] A similar kind of logging will be done at the package level, which will contain the following information:1) Package Name - System::PackageName2) Executing Date - GETDATE()3) Load Status - Where to get this information?4) Duration - DATEDIFF([System::StartTime] , GETDATE()]Apart from this, I'll be creating an event for error handling where in an email will be triggered to the Admin.I am looking for a nod from the experts who have been helping me out the past few days, regarding my approach for logging audit information.

Hi, What you want to store in [Load Status] column ? Lakshman

The status of the task (success/error/warning etc).

If the task is failed how can you insert error/warnig, why because it won't insert any records in case of failure. For 'success' you can directly hardcode your value.Lakshman

If you want to log the status you can use precedence constraints.Take 2 execute sql tasks and connect to the data flow task using Precedence Constraint (for success and failure). Then hardcode the value for Load Status as Success and failure in the Execute SQL Task.I have no idea how to achieve the warning logic.Nitesh Rai- Please mark the post as answered if it answers your question

Hi,Typically any piece of program when executed will return the code, which will indicate whether the code execution is successful or not. In a similar manner, I wanted to know if a Data flow Task will have a 'return code'. And if there is any, is there a way to capture this return code?

3) Load Status - Where to get this information? Apart from this, I'll be creating an event for error handling where in an email will be triggered to the Admin. I am looking for a nod from the experts who have been helping me out the past few days, regarding my approach for logging audit information. Sounds like a good approach. Very similar to the one I outline here: http://consultingblogs.emc.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx As for LoadStatus, you say it should give the "status of the task (success/error/warning etc)". I'll take the easy one first, there is no notion of a task status being "warning". It either succeeds or fails. Yes, it might raise warnings - but that's subtlely different. If you are using eventhandlers (which I believe you should be) then LoadStatus is easy. The OnTaskFailed eventhander will fire if the task fails - in that eventhandler you can hardcode a value for "LoadStatus" that indicates failure. TO be honest though I don't think LoadStatus is all that useful; I'm more interested in the events (i.e. errors, information, warnings) than the status of the actual task itself. Hope that helps. -Jamie http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Typically any piece of program when executed will return the code, which will indicate whether the code execution is successful or not. In a similar manner, I wanted to know if a Data flow Task will have a 'return code'. And if there is any, is there a way to capture this return code? No. The eventhandlers are now aware of whether the task that raised the event has succeeded or not. In the majority of cases it wouldn't make sense for an eventhandler to know if a task has succeeded or not because most events get raised prior to the task completing. Having said that there is a good argument to be made for having a TaskStatus system variable in the OnPostExecute eventhandler. If I were you I'd request it at http://connect.microsoft.com with a good detailed explanation of why it would be useful and provide a link back to this thread. Also, post a link here back to the Connect submission - I will vote for it. As other people have indicated the only way (currently) to conditionally commit some action based on the success/failure of a task is to use precedence constraints. -Jamiehttp://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Thanks Nitesh, My next question was, is there a way we can modularise this, to make this SQL Task as a generic one? I can think of stored procedure. Apart from that, is there any other option?

Hi Jamie,How different is OnTaskFailed and OnError? Are they not the same?Thanks for your approval. I got most of my idea from your blog only (google search took me there ofcourse :))

How different is OnTaskFailed and OnError? Are they not the same? No, although I can see why you think that. A task can raise many errors during the course of its execution however it can only fail once - hence they are subtlely different I'm glad the blog helped! That's why I write it after all :) -Jamiehttp://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Ok agreed. But if one event happens, the other event is also bound to happen right? I mean if a task fails, it means an error has occured. In that case, which event will be triggered? OnTaskFail or OnError or both (assume I have defined some tasks under both events for the same task). Another difference I could guess is, OnTaskFail would occur only when a 'Task' fails. But OnError can occur when a task fails or a package fails.

Thanks Nitesh, My next question was, is there a way we can modularise this, to make this SQL Task as a generic one? I can think of stored procedure. Apart from that, is there any other option? 2 options: A stored procedure A dedicated package that does the logging and which can be called using the Execute Package Task. Careful though - using this technique can be problematic if you want to do it many many times during a package's execution (which it seems like you do).More: A warning about using child packages and Should you execute child packages in-process or out-of-process? http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

I recommend reading the following book: http://www.wrox.com/WileyCDA/WroxTitle/Microsoft-SQL-Server-2008-Integration-Services-Problem-Design-Solution.productCd-0470525762.htmlChapter 2 is about a SSIS framework which seems exactly what you are looking for. The code can be downloaded from the website.

Ok agreed. But if one event happens, the other event is also bound to happen right? I mean if a task fails, it means an error has occured. In that case, which event will be triggered? OnTaskFail or OnError or both (assume I have defined some tasks under both events for the same task). Both! Another difference I could guess is, OnTaskFail would occur only when a 'Task' fails. But OnError can occur when a task fails or a package fails. I suspect not, no. OnTaskFailed is (I suspect) misappropriately named - it should be named OnContainerFailed. Meaning, it gets raised when any container (which includes Sequence container, For Loop containers, ForEach Loop containers, tasks and indeed the package itself) fails.http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Thanks for your reply Jamie. I am now stuck at calculating the duration the data flow task (for which the event is created). Is there a way to get it? I see that the DATEDIFF([System::StartTime] , GETDATE()] would not work, since the System::StartTime is the time when the event started (whereas I am interested in the base task's start time). How to get the duration?

in that case why don't you take System::ContainerStartTime. Lakshman

I use the following query:"insert into scada_stg.dbo.SSIS_Task_Audit_Log (Task_Name,Package_Name,Load_Date,num_rows_selected,num_rows_inserted,num_rows_updated,num_rows_deleted,Load_Duration,Load_status) values ('"+@[System::TaskName] + "','"+@[System::PackageName] + "','" + (DT_STR, 4, 1252) DATEPART("yyyy", @[System::StartTime]) + "-" +(DT_STR, 4, 1252) DATEPART("mm", @[System::StartTime]) + "-" +(DT_STR, 4, 1252) DATEPART("dd", @[System::StartTime]) + " " +(DT_STR, 4, 1252) DATEPART("hh", @[System::StartTime]) + ":" +(DT_STR, 4, 1252) DATEPART("mi", @[System::StartTime]) + ":" +(DT_STR, 4, 1252) DATEPART("ss", @[System::StartTime]) +"',0," + (DT_STR,4,1252) @[User::var_num_rows_inserted] + "',0,0, " + (DT_STR, 10,1252) DATEDIFF("ss",@[System::StartTime],GETDATE()) + ",'SUCCESS')"and am getting the following error:[Execute SQL Task] Error: Executing the query "insert into scada_stg.dbo.SSIS_Task_Audit_Log (Tas..." failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.I get this issue when I introduce (DT_STR,4,1252) @[User::var_num_rows_inserted] in the expression.

Ok I figured out the reason behind the issue. I had included an extra single quote..

in that case why don't you take System::ContainerStartTime. Lakshman I think (but stand to be corrected) that System::ContainerStartTime, when used in an eventhandler, returns the time that the eventhandler started. Check it out though - I might be wrong.http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Hi Jamie, You are correct, System::ContainerStartTime is giving eventhandler starttime instead of container starttime. I referred your blog (http://consultingblogs.emc.com/jamiethomson/archive/2006/01/16/2615.aspx) as well. It seems the problem is not yet resolved. I tried to give my feedback in the link provided by you, but it is throwing some error message. Lakshman

Is there an option of creating a variable, and assigning it the value of the System::ContainterStartTime in the task invoking the Event Handler. We can then refer to this variable in the Event Handler to know the duration of the task. I have not yet figured out a way to achieve this though.

Yes, it may be possible. Any how you are handling on post execute event, so it is better to assign the System::ContainterStartTime value to one package variable (of type datetime) using script task. I think definitely this approach will work. Try this approach and let us know the status.Lakshman

Is there an option of creating a variable, and assigning it the value of the System::ContainterStartTime in the task invoking the Event Handler. We can then refer to this variable in the Event Handler to know the duration of the task. I have not yet figured out a way to achieve this though. yes, this will work. Steps: Create yourself a variable, scoped to the container in question, called User::_ContainerStartTime Set its property EvaluateAsExpression=TRUE Set its property Expression="[System::ContainerStartTime] That variable will be visible in the eventhandler and wll have the time that the container started to execute. This means you'll have to create that variable for every snigle container, but that's not a huge problem I guess. if you want a generic solution to this then take a read of: http://consultingblogs.emc.com/jamiethomson/archive/2006/01/16/2620.aspx . Its not a particualrly elegant approach but it works.http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Hi Jamie,I get the error 'Dts' is not declared.Not sure why!!

I think you got this error in Script task, please check whether you created a reference to the sqlserver dts runtime? You can create the reference by right clicking on your solution (in the solution explorer) and click "add reference". THen under the .NET tab, select "Microsoft.SqlServer.DTSRuntimeWrap".Lakshman

Got the resolution in http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/3ec90abe-703f-441f-bad1-b5965c87946a.

See this Article best practies for Auditing documented: http://pragmaticworks.com/Products/Business-Intelligence/BIxPress/ssis-logging-auditing-monitoring.aspx SSIS-Tutorials-FAQs | Convert DTS to SSIS | Document SSIS | SSIS Tasks | Real-time SSIS Monitoring