sql server - valor - ¿Cómo escribir la salida del procedimiento almacenado directamente en un archivo en un FTP sin usar archivos locales o temporales?

procedimiento almacenado sql server select

Question

Quiero obtener los resultados de un procedimiento almacenado y colocarlos en un archivo CSV en una ubicación FTP.

Sin embargo, la cuestión es que no puedo crear un archivo local / temporal sobre el que pueda pasar el FTP.

El enfoque que estaba tomando consistía en usar un paquete SSIS para crear un archivo temporal y luego tener una tarea FTP dentro del paquete para enviar el archivo por FTP, pero nuestros DBA no permiten que se creen archivos temporales en ningún servidor.

en respuesta a Yaakov Ellis

Creo que tendremos que convencer a los DBA para que me permitan usar al menos una acción en un servidor que no operan, o preguntarles cómo lo harían.

en respuesta a Kev

Me gusta la idea de la integración de CLR, pero no creo que nuestros DBA siquiera sepan lo que es jajaja y probablemente tampoco lo permitan. Pero probablemente pueda hacer esto en una tarea de secuencia de comandos en un paquete de SSIS que se puede programar.

Answer 1

Sin embargo, la cuestión es que no puedo crear un archivo local / temporal que pueda luego pasar por FTP.

Esta restricción no tiene ningún sentido, trate de hablar con el DBA y explíquele a él / ella. Es totalmente razonable para cualquier proceso o trabajo de Windows crear archivos temporales en la ubicación adecuada, es decir, la carpeta% TEMP%. En realidad, el tiempo de ejecución de SSIS a menudo crea archivos temporales allí, por lo que si DBA le permite ejecutar SSIS, le permite crear archivos temporales :).

Siempre que DBA entienda que estos archivos temporales no le generan problemas o una carga de trabajo adicional (explíquele que no tiene que establecer permisos especiales, hacer copias de seguridad de ellos, etc.), debe aceptar que usted los cree.

La única tarea de mantenimiento para DBA es limpiar periódicamente el directorio% TEMP% en caso de que su trabajo SSIS falle y deje el archivo. Pero debería hacer esto de todos modos, ya que muchos otros procesos pueden hacer lo mismo. Un simple trabajo de SQL Agent hará esto.

Answer 2

¿Hay algún servidor en alguna parte que pueda usar donde pueda crear un archivo temporal? Si es así, crea un servicio web que devuelva una matriz que contenga el contenido del archivo. Llame al servicio web desde la computadora donde puede crear un archivo temporal, usar los contenidos de la matriz para compilar el archivo temporal y volver a pasarlo.

Si no hay ningún lugar donde pueda crear un archivo temporal, no veo cómo podrá enviar nada por FTP.

Answer 3

Intente usar un procedimiento almacenado de CLR. Es posible que pueda encontrar algo, pero sin crear primero un archivo temporal, aún puede ser difícil. ¿Podría configurar un recurso compartido en otra máquina y escribir en eso, y luego ftp desde allí?

Answer 4

Script desde el servidor FTP, y solo llame al proceso almacenado.

Answer 5

Si se le permitiera implementar ensamblajes de integración CLR, podría usar FTP sin tener que escribir un archivo temporal:

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

Answer 6

Este ejemplo paso a paso es para otros que puedan tropezar con esta pregunta. Este ejemplo usa el servidor de Windows Server 2008 R2 y SSIS 2008 R2 . Aunque el ejemplo utiliza SSIS 2008 R2 , la lógica utilizada también se aplica a SSIS 2005 . Gracias a @Kev por el código FTPWebRequest .

Cree un paquete de SSIS ( Pasos para crear un paquete de SSIS ). He nombrado el paquete con el formato YYYYMMDD_hhmm al principio, seguido de SO significa , seguido de SO id de pregunta y finalmente una descripción. No estoy diciendo que debas nombrar tu paquete así. Esto es para mí referir esto fácilmente más tarde. Tenga en cuenta que también tengo dos fuentes de datos, a saber, Adventure Works y Practice DB . Utilizaré el origen de datos de Adventure Works , que apunta a la base de datos AdventureWorks que se descargó de este enlace . Consulte la captura de pantalla n . ° 1 en la parte inferior de la respuesta.

En la base de datos de AdventureWorks , cree un procedimiento almacenado llamado dbo.GetCurrency usando la secuencia de comandos indicada a continuación.

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

En la sección Connection Manager del paquete, haga clic con el botón derecho y seleccione New Connection From Data Source . En el cuadro de diálogo Seleccionar origen de datos , seleccione Adventure Works y haga clic en Aceptar . Ahora debería ver el origen de datos de Adventure Works en la sección Administradores de conexión. Refiérase a la captura de pantalla # 2 , # 3 y # 4 .

En el paquete, crea las siguientes variables. Referencia captura de pantalla # 5 .

• ColumnDelimiter : esta variable es de tipo String. Esto se usará para separar los datos de la columna cuando se escriben en el archivo. En este ejemplo, utilizaremos la coma (,) y el código se escribe para manejar únicamente caracteres que se pueden visualizar. Para caracteres que no se pueden visualizar como tab (/ t), es posible que deba cambiar el código utilizado en este ejemplo en consecuencia.

• FileName : esta variable es de tipo String. Contendrá el nombre del archivo. En este ejemplo, he nombrado el archivo como Currencies.csv porque voy a exportar la lista de nombres de moneda.

• FTPPassword : esta variable es de tipo String. Esto contendrá la contraseña para el sitio web FTP. Lo ideal es que el paquete se cifre para ocultar información confidencial.

• FTPRemotePath : esta variable es de tipo String. Esto contendrá la ruta de la carpeta FTP a la que se debe cargar el archivo. Por ejemplo, si el URI FTP completo es ftp://myFTPSite.com/ssis/samples/uploads , entonces RemotePath sería / ssis / samples / uploads.

• FTPServerName : esta variable es de tipo String. Esto contendrá el URI de raíz del sitio FTP. Por ejemplo, si el URI FTP completo es ftp://myFTPSite.com/ssis/samples/uploads , el FTPServerName contendrá ftp://myFTPSite.com . Puede combinar FTPRemotePath con esta variable y tener una sola variable. Depende de tu preferencia.

• FTPUserName : esta variable es de tipo String. Esto contendrá el nombre de usuario que se usará para conectarse al sitio web de FTP.

• ListOfCurrencies : esta variable es de tipo Object. Esto contendrá el conjunto de resultados del procedimiento almacenado y se realizará un bucle en la tarea de secuencia de comandos.

• ShowHeader : esta variable es de tipo Boolean. Esto contendrá valores verdadero / falso. Verdadero indica que la primera fila del archivo contendrá los nombres de las columnas y False indica que la primera fila no contendrá los nombres de las columnas.

• SQLGetData : esta variable es de tipo String. Esto contendrá la instrucción de ejecución del Procedimiento Almacenado. Este ejemplo usa el valor EXEC dbo.GetCurrency

En la pestaña Flujo de control del paquete, coloque una tarea Ejecutar SQL y asígnele el nombre Obtener datos . Haga doble clic en la tarea Ejecutar SQL para abrir el Editor de tareas SQL . En la sección General del Editor de tareas de SQL Execute , configure ResultSet en el Full result set , Connection to Adventure Works , SQLSourceType en Variable y SourceVariable en User::SQLGetData . En la sección Conjunto de resultados, haga clic en el botón Agregar. Establezca el Nombre del resultado en 0 , esto indica el índice y la Variable al User::ListOfCurrencies . La salida del procedimiento almacenado se guardará en esta variable de objeto. Haga clic en Aceptar . Refiérase a la captura de pantalla # 6 y # 7 .

En la ficha Flujo de control del paquete, coloque una Tarea de secuencia de comandos debajo de la Tarea Ejecutar SQL y asígnele el nombre Guardar a FTP . Haga doble clic en la tarea de secuencia de comandos para traer el Editor de tareas de secuencia de comandos . En la sección de Script, haga clic en el botón Edit Script… Referencia captura de pantalla # 8 . Esto abrirá el editor de Visual Studio Tools for Applications (VSTA). Reemplace el código dentro de la clase ScriptMain en el editor con el siguiente código. Además, asegúrese de agregar las instrucciones de uso a los espacios de nombres System.Data.OleDb , System.IO , System.Net , System.Text . Consulte la captura de pantalla n . ° 9 que resalta los cambios de código. Cierre el editor de VSTA y haga clic en Aceptar para cerrar el Editor de tareas de script. El código de script toma la variable de objeto ListOfCurrencies y la almacena en una DataTable con la ayuda de OleDbDataAdapter porque estamos usando la conexión OleDb. Luego, el código recorre cada fila y si la variable ShowHeader se establece en verdadero, el código incluirá los nombres de columna en la primera fila escritos en el archivo. El resultado se almacena en una variable de generador de cadenas. Después de que la variable del generador de cadenas se rellene con todos los datos, el código crea un objeto FTPWebRequest y se conecta al FTP Uri combinando las variables FTPServerName, FTPRemotePath y FileName utilizando las credenciales proporcionadas en las variables FTPUserName y FTPPassword. Luego, los contenidos de las variables del generador de cadenas completas se escriben en el archivo. El método WriteRowData se crea para recorrer las columnas y proporcionar los nombres de las columnas o la información de datos en función de los parámetros aprobados.

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

Una vez que las tareas han sido configuradas, el flujo de control del paquete debe verse como se muestra en la captura de pantalla n . ° 10 .

La captura de pantalla n. ° 11 muestra el resultado de la instrucción de ejecución del procedimiento almacenado EXEC dbo.GetCurrency.

Ejecuta el paquete. La captura de pantalla n. ° 12 muestra la ejecución exitosa del paquete.

Utilizando el complemento FireFTP disponible en el navegador FireFox , inicié sesión en el sitio web FTP y verifiqué que el archivo se ha subido correctamente al sitio web FTP. Consulte la captura de pantalla n. ° 13 .

Al examinar el contenido al abrir el archivo en Notepad ++ se muestra que coincide con el resultado del procedimiento almacenado. Referencia captura de pantalla # 14 .

Por lo tanto, el ejemplo demostró cómo escribir los resultados de la base de datos en un sitio web FTP sin tener que usar archivos temporales / locales.

Espero que ayude a alguien.

Capturas de pantalla:

# 1 : Solution_Explorer

# 2 : New_Connection_From_Data_Source

# 3 : Select_Data_Source

# 4 : Connection_Managers

# 5 : Variables

# 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