Execute stored procedure (SQL 2005) and place results into a CSV file on a FTP location

Question

I want to get the results of a stored procedure and place them into a CSV file onto a FTP location.

The catch though is that I cannot create a local/temporary file that I can then FTP over.

The approach I was taking was to use an SSIS package to create a temporary file and then have a FTP Task within the pack to FTP the file over, but our DBA's do not allow temporary files to be created on any servers.

in reply to Yaakov Ellis

I think we will need to convince the DBA's to let me use at least a share on a server that they do not operate, or ask them how they would do it.

in reply to Kev

I like the idea of the CLR integration, but I don't think our DBA's even know what that is lol and they would probably not allow it either. But I will probably be able to do this within a Script Task in an SSIS package that can be scheduled.

Answer 1

Try using a CLR stored procedure. You might be able to come up with something, but without first creating a temporary file, it might still be difficult. Could you set up a share on another machine and write to that, and then ftp from there?

Answer 2

Is there a server anywhere that you can use where you can create a temporary file? If so, make a web service that returns an array containing the contents of the file. Call the web service from the computer where you can create a temporary file, use the contents of the array to build the temp file and ftp it over.

If there is no where at all where you can create a temporary file, I don't see how you will be able to send anything by FTP.

Answer 3

If you were allowed to implement CLR integration assemblies you could actually use FTP without having to write a temporary file:

public static void DoQueryAndUploadFile(string uri, string username, string password, string filename)
{
    FtpWebRequest ftp = (FtpWebRequest)FtpWebRequest.Create(uri + "/" + filename);
    ftp.Method = WebRequestMethods.Ftp.UploadFile;
    ftp.Credentials = new System.Net.NetworkCredential(username, password);

    using(StreamWriter sw = new StreamWriter(ftp.GetRequestStream()))
    {
        // Do the query here then write to the ftp stream by iterating DataReader or other resultset, following code is just to demo concept:
     for (int i = 0; i < 100; i++)
     {
      sw.WriteLine("{0},row-{1},data-{2}", i, i, i);
     }
     sw.Flush();
    }
}

HTH - Kev

Answer 4

Script from the FTP server, and just call the stored proc.

Answer 5

The catch though is that I cannot create a local/temporary file that I can then FTP over.

This restriction does not make any sense, try to talk to DBA nicely and explain it to him/her. It is totally reasonable for any Windows process or job to create temporary file(s) in appropriate location, i.e. %TEMP% folder. Actually, SSIS runtime itself often creates temporary files there - so if DBA allows you to run SSIS, he is allowing you to create temporary files :).

As long as DBA understands that these temporary files do not create problem or additional workload for him (explain that he does not have to set special permissions, or back them up, etc), he should agree to let you create them.

The only maintenance task for DBA is to periodically clean %TEMP% directory in case your SSIS job fails and leaves the file behind. But he should do this anyway, as many other processes may do the same. A simple SQL Agent job will do this.