[dba-SQLServer] Call a DTS Package via sproc(?) from VBA...

Hi all,

Been thinking over the weekend (I know - scarey hey!).  I have data in an excel workbook which is set up nice and clean to mirror the SQL table.  I am currently using VBA to call a sproc which is writing that data (Line x line via ADO) into SQL Server.  This works great, although it is kinda slow (maybe 45 seconds for 20,000 lines of data).  I notice when I use DTS to import data it is much faster (and easier too, as the package is already saved).

sooo...  in this instance I already have the DTS Package set up.  The Excel file is going to be in a known location with a known name range to import in a SQL Table.  Can I execute the DTS package as part of a sproc?  or maybe even directly from Excel's VBA code?

I guess the other issue is (since others will be doing this). Will there be any security 'gotchas' I should consider?

Any thoughts?

cheers
Darryl

This email and any attachments may contain privileged and confidential information
and are intended for the named addressee only. If you have received this e-mail in
error, please notify the sender and delete this e-mail immediately. Any
confidentiality, privilege or copyright is not waived or lost because this e-mail
has been sent to you in error. It is your responsibility to check this e-mail and
any attachments for viruses.  No warranty is made that this material is free from
computer virus or any other defect or error.  Any loss/damage incurred by using this
material is not the sender's responsibility.  The sender's entire liability will be
limited to resupplying the material.