I need to log the status of the package processing into the SQL Server database table using the stored procedure created which will insert details like start date, end date and the status(sucess/failure). Right now, I am logging the package process status into the log file using the SCRIPT TASK. now the client wants to log the status into the database also. Please let me know do i need to write .NET code into order into insert into the database.

You can use Event handler to log details in DB. see Jamie's blog on same.... http://consultingblogs.emc.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspxCheers, Shailesh

you can use event handlers for custom logging , inside event handlers you can use execute sql task to log into database. a sample of custom logging exists here : http://consultingblogs.emc.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx also you can use built-in logging feature of SSIS, in the menu->SSIS->Logging and add provider for sql server, and select any container, and any event you like .http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

I used Execute SQL task in OnPostExecute event handler, but the event handler ran for 3 times.

have you defined the Event handler on package level ?? otherwise at what level you have defined Event handler?? pls provide more details..Cheers, Shailesh

For which task is the OnPostExecute event enabled in the package? I hope it should be OnPostExecute for the entire package. Is it enabled for any container like for or for each loop?Happy to help! Thanks. Regards and good Wishes, Deepak. In a revamp of my Blog!

yes, i have defined the event handler at the package level. I need to log the package name, start time, end time, package execution status(success/failure). for this I created a stored procedure which is used in the Execute SQL Task in the event handler(OnPostExecute)

if you have defined Event handler on Package level then it will raise for every executable of package see this http://technet.microsoft.com/en-us/library/ms140223.aspx and to capture start time and end time of package you need to use Execute sql task at the start and end of package . and call onPostExecute at the last executable of package and pass the status as Success status to SP to capture failure of package you need to log in table at Onerror event and pass the Failure status to package to SP. package name you can get from system variables what end time you want to log when package fails?? hope this helps!! Cheers, Shailesh

Can you please provide the screen shots of your package ? If not able to , try to debug the package using break points and check where exactly the repeated behavior is visibleHappy to help! Thanks. Regards and good Wishes, Deepak. In a revamp of my Blog!

Hi, this is how i have implemented the logging for SQL server. 1. Created stored procedure which will insert package execution details in SQL Server table. 2. Created EXECUTE SQL TASK in the event handler section on OnPostExecute for the last executed task in the package to log package execution status as SUCCESS. 3. Created EXECUTE SQL TASK in the OnError event to log FAILURE as status if there is any error during the package execution. please let me know if the above approach is correct.

did you try to execute?? let us know your observation..Cheers, Shailesh

Hi, this is how i have implemented the logging for SQL server. 1. Created stored procedure which will insert package execution details in SQL Server table. 2. Created EXECUTE SQL TASK in the event handler section on OnPostExecute for the last executed task in the package to log package execution status as SUCCESS. 3. Created EXECUTE SQL TASK in the OnError event to log FAILURE as status if there is any error during the package execution. please let me know if the above approach is correct. Yes, I tested with the above implemention. So if there is no error then the status is logged as SUCCESS, if any error occurs during the package execution then the status is logged as FAILURE from the OnError event.

Yes, I tested with the above implemention. So if there is no error then the status is logged as SUCCESS, if any error occurs during the package execution then the status is logged as FAILURE from the OnError event.

I think this design is correct to achieve what you wanted to achieve using custom logging. and make sure that last task on which you defined OnPostExecute execute every time when package runs successfully . Cheers, Shailesh

I think this design is correct to achieve what you wanted to achieve using custom logging. and make sure that last task on which you defined OnPostExecute execute every time when package runs successfully . Cheers, Shailesh Hi, thanks for replying. You mentioned that 'make sure that last task on which you defined OnPostExecute execute every time when package runs successfully ' Can there be a possibility that the last task is executed successfully and the OnPostExecute does is not executed. need clarification on this as i suppose the event will always fire when the package runs successfully.

I think this design is correct to achieve what you wanted to achieve using custom logging. and make sure that last task on which you defined OnPostExecute execute every time when package runs successfully . Cheers, Shailesh Hi, thanks for replying. You mentioned that 'make sure that last task on which you defined OnPostExecute execute every time when package runs successfully ' Can there be a possibility that the last task is executed successfully and the OnPostExecute does is not executed. need clarification on this as i suppose the event will always fire when the package runs successfully. I mean to say if say you have two branches in package then package may end at any branch in that case you need to take care that you have assign event handler at the end of both branch hope this helps Cheers, Shailesh

Hi, this is how i have implemented the logging for SQL server. 1. Created stored procedure which will insert package execution details in SQL Server table. 2. Created EXECUTE SQL TASK in the event handler section on OnPostExecute for the last executed task in the package to log package execution status as SUCCESS. 3. Created EXECUTE SQL TASK in the OnError event to log FAILURE as status if there is any error during the package execution. please let me know if the above approach is correct. Yes, I tested with the above implemention. The SUCCESS status is logged after the last executed task if there are no errors raised, if any error occurs during the package execution then the status is logged as FAILURE from the OnError event.

I think this design is correct to achieve what you wanted to achieve using custom logging. and make sure that last task on which you defined OnPostExecute execute every time when package runs successfully . Cheers, Shailesh Hi, thanks for replying. You mentioned that 'make sure that last task on which you defined OnPostExecute execute every time when package runs successfully ' Can there be a possibility that the last task is executed successfully and the OnPostExecute does is not executed. need clarification on this as i suppose the event will always fire when the package runs successfully. I mean to say if say you have two branches in package then package may end at any branch in that case you need to take care that you have assign event handler at the end of both branch hope this helps Cheers, Shailesh the only thing which i found some what terrible is that I have to use the same EXECUTE SQL TASK for both OnPostExecute and OnError event handler in which case I need to do the copy paste of the same code. let me know if this solution is ok.

how you can use same Execute SQL Task in two event handler ?? see the executable name in Event handler tab. you need to create two separate Execute SQL Task for both events.Shailesh Please mark the post as answered if it answers your question.

how you can use same Execute SQL Task in two event handler ?? see the executable name in Event handler tab. you need to create two separate Execute SQL Task for both events. Shailesh Please mark the post as answered if it answers your question. Yes, It would be two separate Execute SQL Task for both these events but the functionality that they are going to do is same. Can i create some thing which can be reused where ever required

Yes, you can copy the same execute sql task for two different event handlers, that should not be a problem. This means tow events can fire the same function but physically you have two execute sql tasks. Happy to help! Thanks. Regards and good Wishes, Deepak. In a revamp of my Blog!

in different event handler you cannot use same task ...and to re-use something many times you can create one separate package and call it whenever require from execute package task ..but for only one execute sql task ..another package it's not worth .Shailesh Please mark the post as answered if it answers your question.

hi there I have the same situation. Need to log Package duration in below format: 0 hr(s) 0 min(s) 0 sec(s) I am able to fetch duration in hh, mm, ss separately. Here is my Query: "INSERT INTO SSIS_EXECUTION_TIME(PackageName, PackageDuration) VALUES ( '" + @[System::PackageName] + "', " + (DT_WSTR, 50) DATEDIFF ( "ss" , @[System::StartTime], GETDATE() ) + " ) " Please suggest. Thanks in advance SabySabi