Using SQL 2008 r2 I successfuly extracted table data and wrote it to an Access database. At the same time I created a SSIS job to automate the process. The first step following the extract in both cases was to delete the Access table entries so the load would be fresh each time... again the extract worked... but when the SSIS job is created and run, it completes without error and the Access table has been emptied but no data was loaded. any suggestions? This has me baffled.
- Edited by exmfcoro Tuesday, August 14, 2012 6:19 PM
This is fairly common problem with SSIS schedule package run as when you schedule it from SQL agent, SSIS runs under context of SQL agent service account.
You can change the service account but that can cause some loop holes in security. The best solution is to use the Agent Proxy for SSIS execution. Kindly have a look at below post to see how to create proxy and assign it it to SSIS job step.
- Chintak (My Blog)
If the package runs fine and there is no error then may be there is something to do with you Package configurations. you can check your configuration settings to make sure everything there is alright.
Please mark the post as answered if it answers your question
I just tried running the SSIS package without using a SQL job and the result showed the potential problem just before deleting the Access table successfuly it gets this message twice:
"Warning Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."
I'm looking for the OLE DB status now.