Как записать выходные данные хранимой процедуры непосредственно в файл на FTP без использования локальных или временных файлов?

Этот пример step-by-step предназначен для тех, кто может наткнуться на этот вопрос. В этом примере используются Windows Server 2008 R2 server и SSIS 2008 R2 . Несмотря на то , что в примере используется SSIS 2008 R2, используемая логика применима и к SSIS 2005 . Спасибо @Kev за код FTPWebRequest.

Создайте пакет SSIS ( шаги для создания пакета SSIS). Я назвал пакет в формате YYYYMMDD_hhmm в начале, а затем SO означает переполнение стека, за которым следует идентификатор вопроса SO и, наконец, описание. Я не говорю, что вы должны так называть свой пакет. Это для меня, чтобы легко сослаться на это позже. Обратите внимание, что у меня также есть два источника данных, а именно 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 и нажмите кнопку OK . Теперь вы должны увидеть источник данных 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 и назовите его как получение данных . Дважды щелкните на задаче Execute SQL, чтобы открыть редактор задач Execute SQL . В разделе Общие редактора задач Execute SQL установите значение ResultSet в Full result set, соединение в Adventure Works, SQLSourceType в Variable и SourceVariable в User::SQLGetData . В разделе результирующий набор нажмите кнопку Добавить. Установите имя результата в 0 , это указывает на индекс и переменную в User::ListOfCurrencies . Выходные данные хранимой процедуры будут сохранены в этой переменной объекта. Нажмите кнопку OK . Смотрите скриншоты #6 и #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 и нажмите кнопку ОК, чтобы закрыть редактор задач сценария. Код скрипта принимает переменную объекта ListOfCurrencies и сохраняет ее в DataTable с помощью OleDbDataAdapter, потому что мы используем соединение OleDb. Затем код циклически проходит через каждую строку, и если переменная ShowHeader имеет значение true, то код будет включать имена столбцов в первой строке, записанной в файл. Результат сохраняется в переменной stringbuilder. После заполнения переменной string builder всеми данными код создает объект FTPWebRequest и подключается к объекту FTP Uri путем объединения переменных FTPServerName, FTPRemotePath и FileName с использованием учетных данных, предоставленных в переменных FTPUserName и FTPPassword. Затем в файл записывается полное содержимое переменной string builder. Метод 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 показывает успешное выполнение пакета.

Используя надстройку FireFTP, доступную в браузере FireFox, я вошел на сайт 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();
    }
}

Есть ли где-нибудь сервер, на котором можно создать временный файл? Если это так, создайте веб-службу, которая возвращает массив, содержащий содержимое файла. Вызовите веб-службу с компьютера, на котором можно создать временный файл, используйте содержимое массива для построения временного файла и ftp его перезаписи.

Если вообще нет места, где вы можете создать временный файл, я не вижу, как вы сможете отправить что-либо по FTP.

Попробуйте использовать хранимую процедуру CLR. Возможно, вам удастся что-то придумать, но без предварительного создания временного файла это все равно будет сложно. Не могли бы вы установить общий ресурс на другой машине и написать на него, а затем ftp оттуда?

Скрипт с сервера FTP, и просто вызовите сохраненный proc.

Но загвоздка в том что я не могу творить локальный / временный файл, который я могу затем FTP перезаписать.

Это ограничение не имеет никакого смысла, попробуйте поговорить с DBA красиво и объяснить его him/her. это абсолютно разумно для любого процесса Windows или задания, чтобы создать временный файл(ы) в соответствующем месте, т. е. папку %TEMP%. На самом деле, SSIS runtime сам часто создает там временные файлы - так что если DBA позволяет вам запускать SSIS, он позволяет вам создавать временные файлы :).

До тех пор, пока DBA понимает, что эти временные файлы не создают для него проблем или дополнительной нагрузки (объясните, что ему не нужно устанавливать специальные разрешения или создавать резервные копии и т. д.), Он должен согласиться позволить вам создать их.

Единственной задачей обслуживания для DBA является периодическая очистка каталога %TEMP% в случае сбоя задания SSIS и оставления файла позади. Но он должен сделать это в любом случае, так как многие другие процессы могут сделать то же самое. Простое задание агента SQL сделает это.