Had an interesting task last days. To make a script that would access a couple of links, get from each one zip file, extract CSVs from them and then import the recors to a MSSQL Database.
So with just a few ColdFusion tags like CFSET, CFHTTP, CFZIP, CFLOOP, CFDIRECTORY and CFQUERY it's a piece of cake to make it work. BUT, (yeah yeah again a BUT hehe) because the CSVs had a lot of records and it had to be done repeatedly for many of them, it came to be a serious issue from performance perspective. In other words it took about 28 minutes to run that task for 1 day data.
So, the smart thing to do in such situation is: let each tool do what it can do best. In other words i had to make MSSQL Server do the import instead of ColdFusion.
The steps was as follow:
  1. Created DTSXes for each of the import format
  2. Created a Stored Procedure that would execute the right DTSX for the right CSV with data
  3. In the ColdFusion script change the code that read and insert te records with just a call to the created Stored Procedure.
This decreased execution time to just around 3 minutes which sounds like a nice improvement to me.

For those interested, in the Stored Procedure i have something like this to execure the DTSX:
SELECT @Cmd = 'dtexec /FILE "' + @dtsxFL + '" /CONNECTION SourceConnectionFlatFile;"' + @fl + '"  /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING EWCDI'

   EXEC @ReturnCode = xp_cmdshell @Cmd
Where in @dtsxFL is the DTSX file name/path, and in @fl in the name/path to the CSV to be imported.
One thing to note though. In order to be able to execute dtexec, you may need to have the following code before it:
EXEC sp_configure 'show advanced options', 1

   RECONFIGURE

   EXEC sp_configure 'xp_cmdshell', 1

   RECONFIGURE
and the following after it (just to make sure you disable cmdshell calls for security reasons):
EXEC sp_configure 'show advanced options', 1

   RECONFIGURE

   EXEC sp_configure 'xp_cmdshell', 0

   RECONFIGURE