Bill Boyer-284226 (5/13/2010)
James - Thank you. We're just looking at transitioning from SQL2000 DTS to 2005 SSIS and this helped me understand. Since you work a lot with SSIS can you tell me whether you have found how I might call a SSIS package to execute from VBA? Perhaps by executing a stored procedure from VBA where that stored proc executes the SSIS package?Thanks
Bill, if you are executing the VBA from the SQL Server then look into using DTSExec http://msdn.microsoft.com/en-us/library/ms162810.aspx. If it is from a remote machine, my approach would be to set up an unscheduled SQL Agent job to run the SSIS package. Then use sp_start_job to run the job. I think you will find that the SQL Agent and SSIS have been nicely integrated and it's easy to set up packages and run them in this fashion.