We have a very simple ssis package on Sql Server 2008 Developer edition 64 bit. It merely connects to a sql server 2000 box and executes a stored procedure as the oledb source for a data flow task. The data access mode in the ole db source editor is set to sql command. The command is exec sprocname.
The ole db destination specifies a table to load the data from the sproc into and the data access mode is table or view - fast load. Lock table check box checked. The destination server is sql server 2008.
This simple package was not performing as it should so we ran a profiler trace while it was running. We captured the sp:completed event class. What we saw when it hit the source server was that the sproc was run 4 times! The first couple of times the sproc was execute it was fast and we assumed this execution was due to the validation that the package does. Then one more execution of the sproc we would attribute to the data flow task running. But we can't explain the 2 extra iterations of the sproc. One of these was fast but one iteration was slow.
This is not a problem normally if the sproc is small and runs fast but if the sproc is complex and takes a while to run these extra executions of the sproc really kill the performance of the ssis package.
Has anyone see this same behavior and perhaps know of a way to stop it from occurring?