how to implement restartability in individual mappings of SSIS packages?

restartability, is there any other tool option? like informatica

or

whether we have to implement through hand written query.

if query, then tell me how to do by taking scenarios like loading data from source sql table to target table.

suppose, in my target table, i have to load 100 records.

After loading 10 records,due to database downtime,or failure,

the loading stopped.

Either i have to truncate table or load it from 11th record.

Can u give me how to do this inside mapping

1)for table truncation

2) loading it from 11 th record.

1. Another option instead of table truncation is to use transactions if your database supports it. If something goes wrong, the transaction can be rolled back. Look in Books Online on how to set up transactions with SSIS.

2. There is currently no built-in mechanism in the data flow for restartability at a certain row, so you will need to put something together on your own. For example, have a incrementing key in the source table that you can use to track the rows inserted in the destination. Then, when something goes wrong, modify your source query to return only the rows that haven't been inserted yet.

How to identify Memory Leak caused by bad written stored procedures?

Does anyone has experience in monitoring Sql server memory leak? I am suspicious of some of the user stored procedures causing the memory leak. Can anyone who has such experience explaine how to find the offened stored procedures?

Thank you very much for any kind of suggestions and recommendations!

Unless you're talking about extended stored procedures, then stored procs themselves don't have memory leaks as such (like normal code), however, they may be doing things inefficiently, or you may have missing table indexes.

The best way to start is to use the SQL Profiler tool to monitor stored procedure execution and look at the duration field. If you capture results to a table, you can then select the slowest queries by ordering on the duration column descending. You should also group by the procedure name to look at the procedures executed most often.

The combination of the slowest queries, and those executed most often will give you the best place to start. From there it's a case of looking at the execution plan (in query analyser) of the procs and looking for ways to optimise the tsql or add/modify indexes.