Hello All, i have an archive process run monthly this month all steps went succssefully except the delete step and the package reported a succssefully status i don't know what's occured make this step didn't execute althought all steps execute succssefully and i check all data transfare to the historical tables.. any clue please , thank you in advance

how would you expect that someone answer your question with no materials?! you should tell us what your steps do actually? what changes applied on the system currently? and any other more information about package. then we can help you in that issue.http://www.rad.pasfu.com

for that i named the post normal delete step, since i have a very strighforword archiving process Select from the online table Insert into the hestorical table delete from the online table my packge looking like that validation script to get the period i should archive data flow select from the source table destination containe the hestory table if the insret process went sucsseded i start delete the same rows archived from the online table exit from the packges my problem is the last step "the delete working very fine and clearly stated, green" but i found that the data haven't delete from the online table the delete script is very simple "delete from dbo.XXX where date ='mm\dd\yyy' " BTW i am using the same date format used in the select and retireve the correct data . for system changes nothing applied recently and this process ran manually over 100 times before that problem happend after use the SSIS to automate the process i am using SSIS 2008 SP2 thanks in advance

Hello, You can do some test, take the same delete statement and execute in SSMS and see if it deletes anything from table. Are you sure your the connection manager you used in Execute SQL Task points to correct database? Check the connection Manager you used in Execute SQL task. Please let us know your findings. Thankshttp://sqlage.blogspot.com/

1) Are you having the date value for the delete as a parameter or is it hard coded? 2) If it is a paramater, try debug the package and find out the exact value during run time. 3)If not what Aamir said is the way to go.Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.

Thank you Guys for your replies, please note that , i have checked all the basics connactivity with the database , and executed the same script using the SSMA and EM as my destination i delete from is is SQL Server 2000 and the script worked fine.. for your questions Deepak i am not using any parameter in the package. what do you think ? i am pinging my head aginst the wall

Did you enable transactions anywhere in the package? Or may be it runs within a Sequence Container? Perhaps the transaction gets rolled back (say by a trigger). There other options to troubleshoot by the way: I had a situation like that I recall, too, and I used SQL Profiler to see what was the actual SQL statement executed against the database. Do you have access to the SQL Profiler? If not, try to log the SQL being executed.Arthur My Blog

non of these optiones were enabled or i used them it's just SQL Script component using normal delete script !!!

Thank you Guys for your replies, please note that , i have checked all the basics connactivity with the database , and executed the same script using the SSMA and EM as my destination i delete from is is SQL Server 2000 and the script worked fine.. for your questions Deepak i am not using any parameter in the package. what do you think ? i am pinging my head aginst the wall What's the data type on the table for that column? Can you try to pass something like that '2011-04-30 00:00:00.000' Ok, then the last resort is to create a stored procedure that accepts the date as input and call the SP from SSIS. CREATE PROCEDURE SP_Delete @Input_Date Datetime AS delete from dbo.XXX where date = @Input_Date --Call stored procedure EXEC SP_Delete '2011-04-30 00:00:00.000' MCITP - BI 2008 http://asqlb.blogspot.com/