SSIS performance: SSMS Import Data vs BIDS Execute Package
Posted on 2013-06-26
I'm working on an ETL project from SQL 2005 to SQL 2012. I'm starting with a source table that has about 450,000 records. Using the SSMS Import Data Wizard, I specified the source SQL Query as
SELECT Contact_ID, Contact_TS, Sex, Date_Of_Birth FROM Contacts. Contact_TS is a timestamp column which is translated as binary(8) for the target table. The target table is created new with a name of Query. This package runs in just a few seconds.
I then created target tables on the target database for Contacts and other tables including some basic foreign keys, which I disabled using EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all". The target table does have a primary key on Contact_ID but it is not an IDENTITY.
I used BIDS to create a new package which uses the same source query but which inserts to the target Contacts table. When I use the Execute Package Utility to run this package, it takes over 6 minutes to process the exact same number of records. I run it from my development computer, not from the SQL Server machine.
Can someone explain why using Execute Package Utility in this way causes it to take more than 30 times as long? What are my options to make this run more quickly.
For production use, I don't expect to use the Execute Package Utility. I expect it to be scheduled or run from some .NET application or process. But I do need to know that I can get sufficient performance with my packages.
Thanks in advance.