如何在不使用本地或临时文件的情况下将存储过程输出直接写入 FTP 上的文件?-数据库问题

How to write stored procedure output directly to a file on an FTP without using local or temp files?(如何在不使用本地或临时文件的情况下将存储过程输出直接写入 FTP 上的文件?)

本文介绍了如何在不使用本地或临时文件的情况下将存储过程输出直接写入 FTP 上的文件?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我想获取存储过程的结果，并将它们放入一个 CSV 文件中，放到一个 FTP 位置.

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

问题是我无法创建一个本地/临时文件，然后我可以通过 FTP 传输.

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

我采用的方法是使用 SSIS 包来创建一个临时文件，然后在包内有一个 FTP 任务来通过 FTP 传输文件，但我们的 DBA 不允许在任何服务器上创建临时文件.

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.

我认为我们需要说服 DBA 让我在他们不操作的服务器上至少使用一个共享，或者询问他们将如何操作.

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.

我喜欢 CLR 集成的想法，但我认为我们的 DBA 甚至不知道那是什么lol，他们可能也不会允许.但我可能能够在可调度的 SSIS 包中的脚本任务中执行此操作.

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.

推荐答案

这个分步示例适用于可能会偶然发现这个问题的其他人.此示例使用 Windows Server 2008 R2 服务器 和 SSIS 2008 R2.尽管该示例使用了 SSIS 2008 R2，但所使用的逻辑也适用于 SSIS 2005.感谢 @Kev 提供 FTPWebRequest 代码.

This step-by-step example is for others who might stumble upon this question. This example uses Windows Server 2008 R2 server and SSIS 2008 R2. Even though, the example uses SSIS 2008 R2, the logic used is applicable to SSIS 2005 as well. Thanks to @Kev for the FTPWebRequest code.

创建 SSIS 包(创建 SSIS 包的步骤).我在开头以 YYYYMMDD_hhmm 格式命名包，然后是 SO 代表堆栈溢出，然后是 SO 问题 id，最后是描述.我并不是说你应该这样命名你的包.这是让我以后轻松参考.请注意，我还有两个数据源，即 Adventure Works 和 Practice DB.我将使用 Adventure Works 数据源，该数据源指向从此链接下载的 AdventureWorks 数据库.请参阅答案底部的屏幕截图 #1.

Create an SSIS package (Steps to create an SSIS package). I have named the package in the format YYYYMMDD_hhmm in the beginning followed by SO stands for Stack Overflow, followed by the SO question id, 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 Adventure Works and Practice DB. I will be using Adventure Works data source, which points to AdventureWorks database downloaded from this link. Refer screenshot #1 at the bottom of the answer.

在 AdventureWorks 数据库中，使用下面给定的脚本创建一个名为 dbo.GetCurrency 的存储过程.

In the AdventureWorks database, create a stored procedure named dbo.GetCurrency using the below given script.

CREATE PROCEDURE [dbo].[GetCurrency]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 
    TOP 10      CurrencyCode
            ,   Name
            ,   ModifiedDate 
    FROM        Sales.Currency
    ORDER BY    CurrencyCode
END
GO

在包的连接管理器部分，右键单击并选择从数据源新建连接.在选择数据源对话框中，选择Adventure Works并点击确定.您现在应该在 Connection Managers 部分下看到 Adventure Works 数据源.请参阅屏幕截图 #2、#3 和 #4.

On the package’s Connection Manager section, right-click and select New Connection From Data Source. On the Select Data Source dialog, select Adventure Works and click OK. You should now see the Adventure Works data source under the Connection Managers section. Refer screenshot #2, #3 and #4.

在包上，创建以下变量.请参阅屏幕截图#5.

On the package, create the following variables. Refer screenshot #5.

ColumnDelimiter:该变量是字符串类型.这将用于在写入文件时分隔列数据.在此示例中，我们将使用逗号 (,)，并且编写的代码仅用于处理可显示的字符.对于制表符 ( ) 等不可显示的字符，您可能需要相应地更改本示例中使用的代码.

ColumnDelimiter: 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 ( ), you might need to change the code used in this example accordingly.

FileName:这个变量是字符串类型.它将包含文件的名称.在本例中，我将文件命名为 Currencies.csv，因为我要导出货币名称列表.

FileName: 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.

FTPPassword:这个变量是字符串类型.这将包含 FTP 网站的密码.理想情况下，应加密包以隐藏敏感信息.

FTPPassword: 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.

FTPRemotePath:该变量是字符串类型.这将包含文件应上传到的 FTP 文件夹路径.例如，如果完整的 FTP URI 是 ftp://myFTPSite.com/ssis/samples/uploads，则 RemotePath 将是/ssis/samples/uploads.

FTPRemotePath: 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 ftp://myFTPSite.com/ssis/samples/uploads, then the RemotePath would be /ssis/samples/uploads.

FTPServerName:这个变量的类型是字符串.这将包含 FTP 站点根 URI.例如，如果完整的 FTP URI 是 ftp://myFTPSite.com/ssis/samples/uploads，则 FTPServerName 将包含 ftp://myFTPSite.com.您可以将 FTPRemotePath 与此变量结合使用并拥有一个变量.这取决于您的喜好.

FTPServerName: This variable is of type String. This will contain the FTP site root URI. For example if the complete FTP URI is ftp://myFTPSite.com/ssis/samples/uploads, then the FTPServerName would contain ftp://myFTPSite.com. You can combine FTPRemotePath with this variable and have a single variable. It is up to your preference.

FTPUserName:这个变量是字符串类型.这将包含用于连接到 FTP 网站的用户名.

FTPUserName:This variable is of type String. This will contain the user name that will be used to connect to the FTP website.

ListOfCurrencies:此变量的类型为 Object.这将包含来自存储过程的结果集，并将在脚本任务中循环.

ListOfCurrencies: 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.

ShowHeader:这个变量是布尔类型的.这将包含真/假值.True 表示文件中的第一行将包含列名，False 表示第一行不包含列名.

ShowHeader: 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.

SQLGetData:该变量是字符串类型.这将包含存储过程执行语句.此示例使用值 EXEC dbo.GetCurrency

SQLGetData: This variable is of type String. This will contain the Stored Procedure execution statement. This example uses the value EXEC dbo.GetCurrency

在包的控制流选项卡上，放置一个执行 SQL 任务并将其命名为获取数据.双击执行 SQL 任务以打开执行 SQL 任务编辑器.在执行 SQL 任务编辑器的General部分，将ResultSet设置为Full result set，>Connection 到 Adventure Works，SQLSourceType 到 Variable，SourceVariable 到 User::SQLGetData.在结果集部分，单击添加按钮.将结果名称设置为 0，这表示索引和变量为 User::ListOfCurrencies.存储过程的输出将保存到这个对象变量中.点击确定.请参阅屏幕截图 #6 和 #7.

On the package’s Control Flow tab, place an Execute SQL Task and name it as Get Data. Double-click on the Execute SQL Task to bring the Execute SQL Task Editor. On the General section of the Execute SQL Task Editor, set the ResultSet to Full result set, the Connection to Adventure Works, the SQLSourceType to Variable and the SourceVariable to User::SQLGetData. On the Result Set section, click Add button. Set the Result Name to 0, this indicates the index and the Variable to User::ListOfCurrencies. The output of the stored procedure will be saved to this object variable. Click OK. Refer screenshot #6 and #7.

在包的控制流选项卡上，在执行 SQL 任务下方放置一个脚本任务，并将其命名为保存到 FTP.双击脚本任务以打开脚本任务编辑器.在脚本部分，单击Edit Script... 按钮.请参阅屏幕截图 #8.这将打开 Visual Studio Tools for Applications (VSTA) 编辑器.将编辑器中 ScriptMain 类中的代码替换为下面给出的代码.此外，请确保将 using 语句添加到名称空间 System.Data.OleDb、System.IO、System.Net、System.Text.请参阅突出显示代码更改的屏幕截图 #9.关闭 VSTA 编辑器并单击确定关闭脚本任务编辑器.脚本代码在 OleDbDataAdapter 的帮助下获取对象变量 ListOfCurrencies 并将其存储到 DataTable 中，因为我们使用的是 OleDb 连接.然后代码循环遍历每一行，如果变量 ShowHeader 设置为 true，则代码将在写入文件的第一行中包含列名称.结果存储在 stringbuilder 变量中.在用所有数据填充字符串构建器变量后，代码创建一个 FTPWebRequest 对象并通过使用变量 FTPUserName 和 FTPPassword 中提供的凭据组合变量 FTPServerName、FTPRemotePath 和 FileName 来连接到 FTP Uri.然后将完整的字符串构建器变量内容写入文件.创建方法 WriteRowData 以遍历列并根据传递的参数提供列名或数据信息.

On the package’s Control Flow tab, place a Script Task below the Execute SQL Task and name it as Save to FTP. Double-click on the Script Task to bring the Script Task Editor. On the Script section, click the Edit Script… button. Refer screenshot #8. This will bring up the Visual Studio Tools for Applications (VSTA) editor. Replace the code within the class ScriptMain in the editor with the code given below. Also, make sure that you add the using statements to the namespaces System.Data.OleDb, System.IO, System.Net, System.Text. Refer screenshot #9 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.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
using System.Net;
using System.Text;

namespace ST_7033c2fc30234dae8086558a88a897dd.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            Variables varCollection = null;

            Dts.VariableDispenser.LockForRead("User::ColumnDelimiter");
            Dts.VariableDispenser.LockForRead("User::FileName");
            Dts.VariableDispenser.LockForRead("User::FTPPassword");
            Dts.VariableDispenser.LockForRead("User::FTPRemotePath");
            Dts.VariableDispenser.LockForRead("User::FTPServerName");
            Dts.VariableDispenser.LockForRead("User::FTPUserName");
            Dts.VariableDispenser.LockForRead("User::ListOfCurrencies");
            Dts.VariableDispenser.LockForRead("User::ShowHeader");
            Dts.VariableDispenser.GetVariables(ref varCollection);

            OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
            DataTable currencies = new DataTable();
            dataAdapter.Fill(currencies, varCollection["User::ListOfCurrencies"].Value);

            bool showHeader = Convert.ToBoolean(varCollection["User::ShowHeader"].Value);
            int rowCounter = 0;
            string columnDelimiter = varCollection["User::ColumnDelimiter"].Value.ToString();
            StringBuilder sb = new StringBuilder();
            foreach (DataRow row in currencies.Rows)
            {
                rowCounter++;
                if (rowCounter == 1 && showHeader)
                {
                    WriteRowData(currencies, row, columnDelimiter, true, ref sb);
                }

                WriteRowData(currencies, row, columnDelimiter, false, ref sb);
            }

            string ftpUri = string.Concat(varCollection["User::FTPServerName"].Value,
                                          varCollection["User::FTPRemotePath"].Value,
                                          varCollection["User::FileName"].Value);

            FtpWebRequest ftp = (FtpWebRequest)FtpWebRequest.Create(ftpUri);
            ftp.Method = WebRequestMethods.Ftp.UploadFile;
            string ftpUserName = varCollection["User::FTPUserName"].Value.ToString();
            string ftpPassword = varCollection["User::FTPPassword"].Value.ToString();
            ftp.Credentials = new System.Net.NetworkCredential(ftpUserName, ftpPassword);

            using (StreamWriter sw = new StreamWriter(ftp.GetRequestStream()))
            {
                sw.WriteLine(sb.ToString());
                sw.Flush();
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        public void WriteRowData(DataTable currencies, DataRow row, string columnDelimiter, bool isHeader, ref StringBuilder sb)
        {
            int counter = 0;
            foreach (DataColumn column in currencies.Columns)
            {
                counter++;

                if (isHeader)
                {
                    sb.Append(column.ColumnName);
                }
                else
                {
                    sb.Append(row[column].ToString());
                }

                if (counter != currencies.Columns.Count)
                {
                    sb.Append(columnDelimiter);
                }
            }
            sb.Append(System.Environment.NewLine);
        }
    }
}

任务配置完成后，包的控制流应该如屏幕截图#10所示.

Once the tasks have been configured, the package’s Control Flow should look like as shown in screenshot #10.

屏幕截图 #11 显示了存储过程执行语句 EXEC dbo.GetCurrency 的输出.

Screenshot #11 shows the output of the stored procedure execution statement EXEC dbo.GetCurrency.

执行包.屏幕截图 #12 显示了包的成功执行.

Execute the package. Screenshot #12 shows successful execution of the package.

使用 FireFox 浏览器中提供的 FireFTP 附加组件，我登录到 FTP 网站并验证文件已成功上传到 FTP 网站.请参阅屏幕截图 #13.

Using the FireFTP add-on available in FireFox browser, I logged into the FTP website and verified that the file has been successfully uploaded to the FTP website. Refer screenshot #13.

通过在 Notepad++ 中打开文件来检查内容表明它与存储过程输出匹配.请参阅屏幕截图 #14.

Examining the contents by opening the file in Notepad++ shows that it matches with the stored procedure output. Refer screenshot #14.

因此，该示例演示了如何将结果从数据库写入 FTP 网站，而无需使用临时/本地文件.

Thus, the example demonstrated how to write results from database to an FTP website without having to use temporary/local files.

希望对某人有所帮助.

截图:

#1:Solution_Explorer

#1: Solution_Explorer

#2:New_Connection_From_Data_Source

#2: New_Connection_From_Data_Source

#3:Select_Data_Source

#3: Select_Data_Source