0

I have built a SSIS package to EXEC a SP in SQL 2008. When Run the package from BIDS, it generates all the results which insert into a table (took about 1 min). But when I scheduled to run as an Agent Job in SQL then it took only 2-3 sec with no results produced, table empty, Job history came back with succeeded. Do you know how to debug this situation? Thanks for all help.

tkvo
  • 171
  • 5
  • 15
  • What user is running the procedure in BIDS (your account) versus the SQL Agent? Are there Credentials being used for the specific job step? Are the permissions in alignment across all those accounts? Does the stored procedure take any parameters? Is the source data the same for the BIDS run vs server run (dev vs prod data)? If *you* execute the package on your local machine from the command line (dtexec /file mypackage.dtsx), does the table get populated? Have you enabled logging and if so, what messages are present? Are your connection managers using the correct values for the environments? – billinkc May 14 '13 at 04:16
  • Or Do you have any kind of error handling in your package or SP?. – Maximus May 14 '13 at 05:01
  • tried everything but could not find the issue, SP required parameter... my solution is disable the parameter and create a Job step with T-SQL EXEC the SP from here then it works this way. – tkvo May 16 '13 at 18:13

1 Answers1

0

Try debugging with SQL Profiler. Start a trace, and then kick off the SQL Agent Job, and see what is being executed.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52