Execute SQL Task speed

I've created a SSIS package, in a sql 2005 instance, that uses an Execute SQL Task" to call a stored proc as its last step. When run from BIDS, the last step takes about 2 to 3 minutes, consistently. When I run the exact same query from Management Studio (either via exec <spname> or by copying the sp's t-sql code into a query window) it consistently takes about 1 minute. I've run sevral test and these number are quite reproducible.

Any ideas to account for the "slowness" of the Execute SQL Task?

TIA,

Barkingdog

Hi, are you running your package in debug mode?

Try run the package without Visual Studio.

John Bocachica - Colombia

www.iquos-bi.com

|||

The dropdown box at the top of BIDs says "Development"

Here is what I have found. My package runs three control tasks. When I run all three, the Exec SQL tasks takes about 2 minutes to run but when I execute ONLY the Exec SQL task that task runs in about 1 minute!

I saved the package (in BIDS) to a .dtsx file and ran it. The whole process took about 2.5 miniutes which tells me that the Exec SQL task still took about 2 minutes.

barkingdog

|||

What are the other tasks doing? Do they use the same database connection? Are there transactions involved?

|||

The first task truncates a table called Contact. The second task imports a CSV file into a table (uses a SQL Server Destination. Does a straight copy of the data; no transformations. The file imported is on the sql 2005 server and database I'm importing into). The third task (Exec SQL Task) applies various UPDATE statements to the table populated in step 2. Steps 2 and 3 use the same sql connection.

II don't know how to tell if all the tasks belong to the same transaction. I set up three control flows in the same pane but they are not contained in any container object, if that helps at all.)

TIA,

Barkingdog