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

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

<p>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.</p>

<h2><a href="https://stackoverflow.com/questions/20587/execute-stored-procedure-sql-2005-and-place-results-into-a-csv-file-on-a-ftp-lo#20596">in reply to Yaakov Ellis</a></h2>

<p>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.</p>

<h2><a href="https://stackoverflow.com/questions/20587/execute-stored-procedure-sql-2005-and-place-results-into-a-csv-file-on-a-ftp-lo#20689">in reply to Kev</a></h2>

<p>I like the idea of the CLR integration, but I don't think our DBA's even know what that is <em>lol</em> 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.</p>

[{'answer_id': 20593, 'author': 'Eric Z Beard', 'author_id': 1219, 'author_profile': 'https://Stackoverflow.com/users/1219', 'pm_score': 0, 'selected': False, 'text': '<p>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?</p>\n'}, {'answer_id': 20596, 'author': 'Yaakov Ellis', 'author_id': 51, 'author_profile': 'https://Stackoverflow.com/users/51', 'pm_score': 1, 'selected': False, 'text': "<p>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.</p>\n\n<p>If there is no where <em>at all</em> where you can create a temporary file, I don't see how you will be able to send anything by FTP.</p>\n"}, {'answer_id': 20689, 'author': 'Kev', 'author_id': 419, 'author_profile': 'https://Stackoverflow.com/users/419', 'pm_score': 3, 'selected': False, 'text': '<p>If you were allowed to implement CLR integration assemblies you could actually use FTP without having to write a temporary file:</p>\n\n<pre><code>public static void DoQueryAndUploadFile(string uri, string username, string password, string filename)\n{\n    FtpWebRequest ftp = (FtpWebRequest)FtpWebRequest.Create(uri + "/" + filename);\n    ftp.Method = WebRequestMethods.Ftp.UploadFile;\n    ftp.Credentials = new System.Net.NetworkCredential(username, password);\n\n    using(StreamWriter sw = new StreamWriter(ftp.GetRequestStream()))\n    {\n        // Do the query here then write to the ftp stream by iterating DataReader or other resultset, following code is just to demo concept:\n        for (int i = 0; i &lt; 100; i++)\n        {\n            sw.WriteLine("{0},row-{1},data-{2}", i, i, i);\n        }\n        sw.Flush();\n    }\n}\n</code></pre>\n'}, {'answer_id': 37239, 'author': 'Mark Brackett', 'author_id': 2199, 'author_profile': 'https://Stackoverflow.com/users/2199', 'pm_score': 0, 'selected': False, 'text': '<p>Script from the FTP server, and just call the stored proc.</p>\n'}, {'answer_id': 110552, 'author': 'Michael Entin', 'author_id': 19880, 'author_profile': 'https://Stackoverflow.com/users/19880', 'pm_score': 0, 'selected': False, 'text': '<blockquote>\n  <p>The catch though is that I cannot create \n  a local/temporary file that I can then FTP over.</p>\n</blockquote>\n\n<p>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 <strong>is</strong> allowing you to create temporary files :). </p>\n\n<p>As long as DBA understands that these temporary files do not create problem or additional  workload for him (explain that he does <strong>not</strong> have to set special permissions, or back them up, etc), he should agree to let you create them.</p>\n\n<p>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.</p>\n'}, {'answer_id': 6800413, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 5, 'selected': True, 'text': '<p>This step-by-step example is for others who might stumble upon this question. This example uses <em>Windows Server 2008 R2 server</em> and <em>SSIS 2008 R2</em>. Even though, the example uses <em>SSIS 2008 R2</em>, the logic used is applicable to <em>SSIS 2005</em> as well. Thanks to <code>@Kev</code> for the <em>FTPWebRequest</em> code.</p>\n\n<p>Create an SSIS package (<a href="http://learnbycoding.com/2011/07/creating-a-simple-ssis-package-using-bids/">Steps to create an SSIS package</a>). I have named the package in the format YYYYMMDD_hhmm in the beginning followed by <em>SO</em> stands for Stack Overflow, followed by the <em>SO question id</em>, and finally a description. I am not saying that you should name your package like this. This is for me to easily refer this back later. Note that I also have two Data Sources namely <em>Adventure Works</em> and <em>Practice DB</em>. I will be using <em>Adventure Works</em> data source, which points to <em>AdventureWorks</em> database downloaded from <a href="http://msftdbprodsamples.codeplex.com/">this link</a>. Refer screenshot <strong>#1</strong> at the bottom of the answer.</p>\n\n<p>In the <em>AdventureWorks</em> database, create a stored procedure named <em>dbo.GetCurrency</em> using the below given script.</p>\n\n<pre><code>CREATE PROCEDURE [dbo].[GetCurrency]\nAS\nBEGIN\n    SET NOCOUNT ON;\n    SELECT \n    TOP 10      CurrencyCode\n            ,   Name\n            ,   ModifiedDate \n    FROM        Sales.Currency\n    ORDER BY    CurrencyCode\nEND\nGO\n</code></pre>\n\n<p>On the package’s Connection Manager section, right-click and select <em>New Connection From Data Source</em>. On the <em>Select Data Source</em> dialog, select <em>Adventure Works</em> and click <em>OK</em>. You should now see the Adventure Works data source under the Connection Managers section. Refer screenshot <strong>#2</strong>, <strong>#3</strong> and <strong>#4</strong>.</p>\n\n<p>On the package, create the following variables. Refer screenshot <strong>#5</strong>.</p>\n\n<ul>\n<li><p><em>ColumnDelimiter</em>: This variable is of type String. This will be used to separate the column data when it is written to the file. In this example, we will be using comma (,) and the code is written to handle only displayable characters. For non-displayable characters like tab (\\t), you might need to change the code used in this example accordingly.</p></li>\n<li><p><em>FileName</em>: This variable is of type String. It will contain the name of the file. In this example, I have named the file as Currencies.csv because I am going to export list of currency names.</p></li>\n<li><p><em>FTPPassword</em>: This variable is of type String. This will contain the password to the FTP website. Ideally, the package should be encrypted to hide sensitive information.</p></li>\n<li><p><em>FTPRemotePath</em>: This variable is of type String. This will contain the FTP folder path to which the file should be uploaded to. For example if the complete FTP URI is <a href="ftp://myFTPSite.com/ssis/samples/uploads">ftp://myFTPSite.com/ssis/samples/uploads</a>, then the RemotePath would be /ssis/samples/uploads.</p></li>\n<li><p><em>FTPServerName</em>: This variable is of type String. This will contain the FTP site root URI. For example if the complete FTP URI is <a href="ftp://myFTPSite.com/ssis/samples/uploads">ftp://myFTPSite.com/ssis/samples/uploads</a>, then the FTPServerName would contain <a href="ftp://myFTPSite.com">ftp://myFTPSite.com</a>. You can combine FTPRemotePath with this variable and have a single variable. It is up to your preference.</p></li>\n<li><p><em>FTPUserName</em>:This variable is of type String. This will contain the user name that will be used to connect to the FTP website.</p></li>\n<li><p><em>ListOfCurrencies</em>: This variable is of type Object. This will contain the result set from the stored procedure and it will be looped through in the Script Task.</p></li>\n<li><p><em>ShowHeader</em>: This variable is of type Boolean. This will contain values true/false. True indicates that the first row in the file will contain Column names and False indicates that the first row will not contain Column names.</p></li>\n<li><p><em>SQLGetData</em>: This variable is of type String. This will contain the Stored Procedure execution  statement. This example uses the value EXEC dbo.GetCurrency</p></li>\n</ul>\n\n<p>On the package’s <em>Control Flow</em> tab, place an <em>Execute SQL Task</em> and name it as <em>Get Data</em>. Double-click on the Execute SQL Task to bring the <em>Execute SQL Task Editor</em>. On the <em>General</em> section of the <em>Execute SQL Task Editor</em>, set the <em>ResultSet</em> to <code>Full result set</code>, the <em>Connection</em> to <code>Adventure Works</code>, the <em>SQLSourceType</em> to <code>Variable</code> and the <em>SourceVariable</em> to <code>User::SQLGetData</code>. On the Result Set section, click Add button. Set the Result Name to <code>0</code>, this indicates the index and the Variable to <code>User::ListOfCurrencies</code>. The output of the stored procedure will be saved to this object variable. Click <em>OK</em>. Refer screenshot <strong>#6</strong> and <strong>#7</strong>.</p>\n\n<p>On the package’s <em>Control Flow</em> tab, place a Script Task below the Execute SQL Task and name it as <em>Save to FTP</em>. Double-click on the Script Task to bring the <em>Script Task Editor</em>. On the Script section, click the <code>Edit Script…</code> button. Refer screenshot <strong>#8</strong>. This will bring up the Visual Studio Tools for Applications (VSTA) editor. Replace the code within the class <code>ScriptMain</code> in the editor with the code given below. Also, make sure that you add the using statements to the namespaces <code>System.Data.OleDb</code>, <code>System.IO</code>, <code>System.Net</code>, <code>System.Text</code>. Refer screenshot <strong>#9</strong> that highlights the code changes. Close the VSTA editor and click Ok to close the Script Task Editor. Script code takes the object variable ListOfCurrencies and stores it into a DataTable with the help of OleDbDataAdapter because we are using OleDb connection. The code then loops through each row and if the variable ShowHeader is set to true, the code will include the Column names in the first row written to the file. The result is stored in a stringbuilder variable. After the string builder variable is populated with all the data, the code creates an FTPWebRequest object and connects to the FTP Uri by combining the variables FTPServerName, FTPRemotePath and FileName using the credentials provided in the variables FTPUserName and FTPPassword. Then the full string builder variable contents are written to the file. The method WriteRowData is created to loop through columns and provide the column names or data information based on the parameters passed.</p>\n\n<pre><code>using System;\nusing System.Data;\nusing Microsoft.SqlServer.Dts.Runtime;\nusing System.Windows.Forms;\nusing System.Data.OleDb;\nusing System.IO;\nusing System.Net;\nusing System.Text;\n\nnamespace ST_7033c2fc30234dae8086558a88a897dd.csproj\n{\n    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]\n    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase\n    {\n\n        #region VSTA generated code\n        enum ScriptResults\n        {\n            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,\n            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure\n        };\n        #endregion\n\n        public void Main()\n        {\n            Variables varCollection = null;\n\n            Dts.VariableDispenser.LockForRead("User::ColumnDelimiter");\n            Dts.VariableDispenser.LockForRead("User::FileName");\n            Dts.VariableDispenser.LockForRead("User::FTPPassword");\n            Dts.VariableDispenser.LockForRead("User::FTPRemotePath");\n            Dts.VariableDispenser.LockForRead("User::FTPServerName");\n            Dts.VariableDispenser.LockForRead("User::FTPUserName");\n            Dts.VariableDispenser.LockForRead("User::ListOfCurrencies");\n            Dts.VariableDispenser.LockForRead("User::ShowHeader");\n            Dts.VariableDispenser.GetVariables(ref varCollection);\n\n            OleDbDataAdapter dataAdapter = new OleDbDataAdapter();\n            DataTable currencies = new DataTable();\n            dataAdapter.Fill(currencies, varCollection["User::ListOfCurrencies"].Value);\n\n            bool showHeader = Convert.ToBoolean(varCollection["User::ShowHeader"].Value);\n            int rowCounter = 0;\n            string columnDelimiter = varCollection["User::ColumnDelimiter"].Value.ToString();\n            StringBuilder sb = new StringBuilder();\n            foreach (DataRow row in currencies.Rows)\n            {\n                rowCounter++;\n                if (rowCounter == 1 &amp;&amp; showHeader)\n                {\n                    WriteRowData(currencies, row, columnDelimiter, true, ref sb);\n                }\n\n                WriteRowData(currencies, row, columnDelimiter, false, ref sb);\n            }\n\n            string ftpUri = string.Concat(varCollection["User::FTPServerName"].Value,\n                                          varCollection["User::FTPRemotePath"].Value,\n                                          varCollection["User::FileName"].Value);\n\n            FtpWebRequest ftp = (FtpWebRequest)FtpWebRequest.Create(ftpUri);\n            ftp.Method = WebRequestMethods.Ftp.UploadFile;\n            string ftpUserName = varCollection["User::FTPUserName"].Value.ToString();\n            string ftpPassword = varCollection["User::FTPPassword"].Value.ToString();\n            ftp.Credentials = new System.Net.NetworkCredential(ftpUserName, ftpPassword);\n\n            using (StreamWriter sw = new StreamWriter(ftp.GetRequestStream()))\n            {\n                sw.WriteLine(sb.ToString());\n                sw.Flush();\n            }\n\n            Dts.TaskResult = (int)ScriptResults.Success;\n        }\n\n        public void WriteRowData(DataTable currencies, DataRow row, string columnDelimiter, bool isHeader, ref StringBuilder sb)\n        {\n            int counter = 0;\n            foreach (DataColumn column in currencies.Columns)\n            {\n                counter++;\n\n                if (isHeader)\n                {\n                    sb.Append(column.ColumnName);\n                }\n                else\n                {\n                    sb.Append(row[column].ToString());\n                }\n\n                if (counter != currencies.Columns.Count)\n                {\n                    sb.Append(columnDelimiter);\n                }\n            }\n            sb.Append(System.Environment.NewLine);\n        }\n    }\n}\n</code></pre>\n\n<p>Once the tasks have been configured, the package’s Control Flow should look like as shown in screenshot <strong>#10</strong>.</p>\n\n<p>Screenshot <strong>#11</strong> shows the output of the stored procedure execution statement EXEC dbo.GetCurrency. </p>\n\n<p>Execute the package. Screenshot <strong>#12</strong> shows successful execution of the package.</p>\n\n<p>Using the <em>FireFTP</em> add-on available in <em>FireFox</em> browser, I logged into the FTP website and verified that the file has been successfully uploaded to the FTP website. Refer screenshot #<strong>13</strong>.</p>\n\n<p>Examining the contents by opening the file in Notepad++ shows that it matches with the stored procedure output. Refer screenshot #<strong>14</strong>.</p>\n\n<p>Thus, the example demonstrated how to write results from database to an FTP website without having to use temporary/local files.</p>\n\n<p>Hope that helps someone.</p>\n\n<p><strong>Screenshots:</strong></p>\n\n<p><strong>#1</strong>: Solution_Explorer</p>\n\n<p><img src="https://i.stack.imgur.com/1C8VG.png" alt="Solution_Explorer"></p>\n\n<p><strong>#2</strong>: New_Connection_From_Data_Source</p>\n\n<p><img src="https://i.stack.imgur.com/hYZqX.png" alt="New_Connection_From_Data_Source"></p>\n\n<p><strong>#3</strong>: Select_Data_Source</p>\n\n<p><img src="https://i.stack.imgur.com/ccxdu.png" alt="Select_Data_Source"></p>\n\n<p><strong>#4</strong>: Connection_Managers</p>\n\n<p><img src="https://i.stack.imgur.com/Rw8xv.png" alt="Connection_Managers"></p>\n\n<p><strong>#5</strong>: Variables</p>\n\n<p><img src="https://i.stack.imgur.com/Ov6Ac.png" alt="Variables"></p>\n\n<p><strong>#6</strong>: Execute_SQL_Task_Editor_General</p>\n\n<p><img src="https://i.stack.imgur.com/sMP6f.png" alt="Execute_SQL_Task_Editor_General"></p>\n\n<p><strong>#7</strong>: Execute_SQL_Task_Editor_Result_Set</p>\n\n<p><img src="https://i.stack.imgur.com/D7iKl.png" alt="Execute_SQL_Task_Editor_Result_Set"></p>\n\n<p><strong>#8</strong>: Script_Task_Editor</p>\n\n<p><img src="https://i.stack.imgur.com/Yz20f.png" alt="Script_Task_Editor"></p>\n\n<p><strong>#9</strong>: Script_Task_VSTA_Code</p>\n\n<p><img src="https://i.stack.imgur.com/C39Xc.png" alt="Script_Task_VSTA_Code"></p>\n\n<p><strong>#10</strong>: Control_Flow_Tab</p>\n\n<p><img src="https://i.stack.imgur.com/kFLeF.png" alt="Control_Flow_Tab"></p>\n\n<p><strong>#11</strong>: Query_Results</p>\n\n<p><img src="https://i.stack.imgur.com/t3aGT.png" alt="Query_Results"></p>\n\n<p><strong>#12</strong>: Package_Execution_Successful</p>\n\n<p><img src="https://i.stack.imgur.com/tYKwu.png" alt="Package_Execution_Successful"></p>\n\n<p><strong>#13</strong>: File_In_FTP</p>\n\n<p><img src="https://i.stack.imgur.com/Yt8fD.png" alt="File_In_FTP"></p>\n\n<p><strong>#14</strong>: File_Contents</p>\n\n<p><img src="https://i.stack.imgur.com/nuf2M.png" alt="File_Contents"></p>\n'}]
['https://Stackoverflow.com/questions/20587', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1950/']