I have written quite a few feeds/interfaces over the last few years which i’m now trying to “tighten up”. There’s one in particular which i’ve been struggling with- this is essentially all it does:

– Download and rename a bunch of files from an FTP
– Manipulate these files a little
– Execute a 3rd party application to import the files

There are 2 problems:

– No applications can be run n the SQL server
– The 3rd party application cannot import files from a network location (so they must be copied to a local area imported then the output files copied back to the network location)

This particular feed  is a SQL Server Integration Services (SSIS). The package needs to be executed on demand and passed some variables.

Here’s how I achieved my goal (i don’t remember the details of all the problems i encountered trying to get this working, but there were a lot!):

– Created an .asp page which collects the variables and executes a stored procedure
– The stored procedure:
— creates a sql job with the SSIS package and relevant variables
— executes the sql job and waits for it to complete

 WAITFOR DELAY '000:00:02'
  SELECT * FROM OPENROWSET('sqloledb','server=servername;trusted_connection=yes','exec msdb.dbo.sp_help_job @job_name=N''job name''') WHERE current_execution_status = 1

— deletes the sql job

The SSIS package contains a script task which takes care of the “copy files to local area”, “import files” and “copy files back to network location (one of the tricky bits here was getting my code to wait for the remote process to complete:

Dim path As New System.Management.ManagementPath("\\" & strMachineName &"\root\cimv2:Win32_Process")
Dim scope As New System.Management.ManagementScope(path)
Dim opt As New System.Management.ObjectGetOptions()
Dim classInstance As New System.Management.ManagementClass(scope, path, opt)
Dim inParams As System.Management.ManagementBaseObject = classInstance.GetMethodParameters("Create")
inParams("CommandLine") = strCommand
Dim outParams As System.Management.ManagementBaseObject = classInstance.InvokeMethod("Create", inParams, Nothing)
 Do While Process.GetProcessById(CInt(outParams("processId")), strMachineName).ProcessName.Length > 0
Catch ex As Exception
End Try