如何在不使用本地或临时文件的情况下直接将存储过程输出写入FTP上的文件？

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

但问题是,我无法创建一个本地/临时文件,然后我可以FTP.

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

回复Yaakov Ellis

我想我们需要说服DBA让我至少在他们不操作的服务器上使用它,或者问他们如何做.

回答凯夫

我喜欢的CLR集成的想法,但我不认为我们的DBA的甚至不知道那是什么笑,他们可能不会允许它的.但我可能会在可以安排的SSIS包中的脚本任务中执行此操作.

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

创建SSIS包(创建SSIS包的步骤).我在开头的YYYYMMDD_hhmm格式中命名了包,然后是SO代表Stack Overflow,后面是SO问题ID,最后是描述.我不是说你应该像这样命名你的包裹.这对我来说很容易在以后再提到.请注意,我还有两个数据源,即Adventure Works和Practice DB.我将使用Adventure Works数据源,它指向从此链接下载的AdventureWorks数据库.请参阅答案底部的屏幕截图#1.

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

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公司,然后单击确定.您现在应该在"连接管理器"部分下看到Adventure Works数据源.请参阅屏幕截图#2,#3和#4.

在包上,创建以下变量.参见截图#5.

ColumnDelimiter:此变量的类型为String.这将用于在将列数据写入文件时将其分离.在这个例子中,我们将使用逗号(,),并编写代码以仅处理可显示的字符.对于tab(\ t)等不可显示的字符,您可能需要相应地更改此示例中使用的代码.

FileName:此变量的类型为String.它将包含文件的名称.在这个例子中,我将文件命名为Currencies.csv,因为我要导出货币名称列表.

FTPPassword:此变量的类型为String.这将包含FTP网站的密码.理想情况下,应对包进行加密以隐藏敏感信息.

FTPRemotePath:此变量的类型为String.这将包含文件应上载到的FTP文件夹路径.例如,如果完整的FTP URI是ftp://myFTPSite.com/ssis/samples/uploads,则RemotePath将是/ ssis/samples/uploads.

FTPServerName:此变量的类型为String.这将包含FTP站点根URI.例如,如果完整的FTP URI是ftp://myFTPSite.com/ssis/samples/uploads,则FTPServerName将包含ftp://myFTPSite.com.您可以将FTPRemotePath与此变量组合使用,并具有单个变量.这取决于您的偏好.

FTPUserName:此变量的类型为String.这将包含将用于连接到FTP网站的用户名.

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

ShowHeader:此变量的类型为Boolean.这将包含值true/false.True表示文件中的第一行包含列名,False表示第一行不包含列名.

SQLGetData:此变量的类型为String.这将包含存储过程执行语句.此示例使用值EXEC dbo.GetCurrency

在程序包的" 控制流"选项卡上,放置" 执行SQL任务"并将其命名为" 获取数据".双击"执行SQL任务"以执行"执行SQL任务编辑器".在常规的部分执行SQL任务编辑器,设置的ResultSet来Full result set,在连接到Adventure Works的SQLSourceType到Variable和SourceVariable来User::SQLGetData.在"结果集"部分,单击"添加"按钮.将Result Name设置为0,这表示索引和Variable to User::ListOfCurrencies.存储过程的输出将保存到此对象变量中.单击确定.参见截图#6和#7.

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

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所示.

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

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

使用FireFox浏览器中提供的FireFTP插件,我登录FTP网站并验证该文件已成功上传到FTP网站.参见截图#13.

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

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

希望能帮助别人.

截图:

#1:Solution_Explorer

#2:New_Connection_From_Data_Source

#3:Select_Data_Source

#4:Connection_Managers

#5:变量

#6:Execute_SQL_Task_Editor_General

#7:Execute_SQL_Task_Editor_Result_Set

#8:Script_Task_Editor

#9:Script_Task_VSTA_Code

#10:Control_Flow_Tab

#11:Query_Results

#12:Package_Execution_Successful

#13:File_In_FTP

#14:File_Contents

如果您被允许实现CLR集成程序集,您实际上可以使用FTP而无需编写临时文件:

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();
    }
}