DTS - Manual step excution gives different results than just hitting "Run"

Question

I have a DTS (not SSIS) package that hasn't been touched in years that I had to update a query with. When I run the package by manually executing each step in the editor, everything works out fine and generates a file of a couple thousand records as expected. When I hit the "Execute" button at the top of the editor to run the whole package, it doesn't error but the file is generated with only 1 record.

All tasks inside of the package are either transformation steps or Sql Tasks. There are not any ActiveX script tasks. When I watch the process as it's running the steps by itself, the execution is following the mapping correctly.

I'm at a loss on this one. Has anyone seen this issue before or have any idea where to start?

Answer 1

I just ran into a similar issue recently. While working with the senior DBA, we found that the server where the package ran did not have the right permissions to a directory on the network. The package ran fine in my box, but died on the production server. We need to give permissions to the sqlservice account on the production box, to write to the directory on the network.

You might also want to check out any ActiveX Script step that changes the connection string or destination of Data Pump steps. I've had cases where these were different on the destination server that the DTS packages run.

Answer 2

After going through all of the lines of all of the stored procedures and straight sql tasks used in the package, I located a SET ROWCOUNT 1 that was never reset. While I was manually executing each step separately, the RowCount would be automatically reset; however, when it was run as a complete package, the RowCount was never reset. Adding SET ROWCOUNT 0 at the end of the particular script resolved this issue.