Comment écrire une sortie de procédure stockée directement dans un file sur un FTP sans utiliser de files locaux ou temporaires?

Cet exemple étape par étape est pour les autres qui pourraient trébucher sur cette question. Cet exemple utilise le server Windows Server 2008 R2 et SSIS 2008 R2 . Même si l'exemple utilise SSIS 2008 R2 , la logique utilisée est également applicable à SSIS 2005 . Merci à @Kev pour le code FTPWebRequest .

Créez un package SSIS ( étapes pour créer un package SSIS ). J'ai nommé le package au format YYYYMMDD_hhmm au début suivi de SO signifie Stack Overflow, suivi par l' identifiant de la question SO , et enfin une description. Je ne dis pas que vous devriez nommer votre package comme ceci. C'est pour moi de renvoyer facilement cela plus tard. Notez que j'ai aussi deux sources de données à savoir Adventure Works et Practice DB . J'utiliserai la source de données Adventure Works , qui pointe vers la database AdventureWorks téléchargée à partir de ce lien . Reportez-vous à la capture d'écran # 1 au bas de la réponse.

Dans la database AdventureWorks , créez une procédure stockée nommée dbo.GetCurrency en utilisant le script ci-dessous.

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

Dans la section Gestionnaire de connections du package, cliquez avec le button droit de la souris et select Nouvelle connection à partir de la source de données . Dans la boîte de dialog Sélectionner la source de données , select Adventure Works et click OK . Vous devriez maintenant voir la source de données Adventure Works dans la section Connection Managers. Reportez-vous aux captures d'écran # 2 , # 3 et # 4 .

Sur le package, créez les variables suivantes. Référez la capture d'écran # 5 .

• ColumnDelimiter : Cette variable est de type Ssortingng. Cela sera utilisé pour séparer datatables de la colonne lorsqu'il est écrit dans le file. Dans cet exemple, nous allons utiliser la virgule (,) et le code est écrit pour gérer uniquement les caractères affichables. Pour les caractères non affichables comme tab (\ t), vous devrez peut-être modifier le code utilisé dans cet exemple en conséquence.

• FileName : Cette variable est de type Ssortingng. Il contiendra le nom du file. Dans cet exemple, j'ai nommé le file Currencies.csv car je vais exporter la list des noms de devises.

• FTPPassword : Cette variable est de type Ssortingng. Cela contiendra le mot de passe pour le site FTP. Idéalement, le package devrait être chiffré pour cacher les informations sensibles.

• FTPRemotePath : Cette variable est de type Ssortingng. Cela contiendra le path du dossier FTP vers lequel le file doit être téléchargé. Par exemple, si l'URI FTP complet est ftp://myFTPSite.com/ssis/samples/uploads , RemotePath sera / ssis / samples / uploads.

• FTPServerName : cette variable est de type Ssortingng. Cela contiendra l'adresse URI racine du site FTP. Par exemple, si l' adresse URI FTP complète est ftp://myFTPSite.com/ssis/samples/uploads , alors FTPServerName contiendra ftp://myFTPSite.com . Vous pouvez combiner FTPRemotePath avec cette variable et avoir une seule variable. C'est à votre preference.

• FTPUserName : Cette variable est de type Ssortingng. Cela contiendra le nom d'user qui sera utilisé pour se connecter au site FTP.

• ListOfCurrencies : cette variable est de type Object. Cela contiendra le jeu de résultats de la procédure stockée et il sera boukey dans la tâche de script.

• ShowHeader : Cette variable est de type Boolean. Cela contiendra des valeurs vrai / faux. La valeur True indique que la première ligne du file contiendra des noms de colonne et False indique que la première ligne ne contiendra pas de noms de colonne.

• SQLGetData : cette variable est de type Ssortingng. Cela contiendra l'instruction d'exécution de la procédure stockée. Cet exemple utilise la valeur EXEC dbo.GetCurrency

Dans l'onglet Flux de contrôle du package, placez une tâche SQL d'exécution et nommez-la comme Get Data . Double-click la tâche d' exécution SQL pour afficher l'éditeur de tâche d'exécution SQL . Dans la section Général de l' éditeur de tâche d'exécution SQL , définissez ResultSet sur Full result set , login à Adventure Works , SQLSourceType à Variable et SourceVariable à User::SQLGetData . Dans la section Ensemble de résultats, click le button Ajouter. Définissez le nom du résultat sur 0 , cela indique l'index et la variable à User::ListOfCurrencies . La sortie de la procédure stockée sera enregistrée dans cette variable d'object. Cliquez sur OK Reportez-vous aux captures d'écran # 6 et # 7 .

Dans l'onglet Flux de contrôle du package, placez une tâche de script sous la tâche d'exécution SQL et nommez-la en tant que Enregistrer sur FTP . Double-click la tâche de script pour amener l' éditeur de tâche de script . Dans la section Script, click le button Edit Script… Référez la capture d'écran # 8 . Cela fera apparaître l'éditeur Visual Studio Tools pour Applications (VSTA). Remplacez le code dans la class ScriptMain dans l'éditeur avec le code donné ci-dessous. Assurez-vous également que vous ajoutez les instructions using aux namespaces System.Data.OleDb , System.IO , System.Net , System.Text . Reportez-vous à la capture d'écran # 9 qui met en évidence les changements de code. Fermez l'éditeur VSTA et click OK pour fermer l'éditeur de tâche de script. Le code de script prend la variable object ListOfCurrencies et la stocke dans un DataTable à l'aide de OleDbDataAdapter, car nous utilisons la connection OleDb. Le code parcourt ensuite chaque ligne et si la variable ShowHeader est définie sur true, le code inclura les noms de colonne dans la première ligne écrite dans le file. Le résultat est stocké dans une variable ssortingngbuilder. Une fois la variable de générateur de string remplie avec toutes datatables, le code crée un object FTPWebRequest et se connecte à l'Uri FTP en combinant les variables FTPServerName, FTPRemotePath et FileName en utilisant les informations d'identification fournies dans les variables FTPUserName et FTPPassword. Ensuite, le contenu de la variable de générateur de string complète est écrit dans le file. La méthode WriteRowData est créée pour parcourir les colonnes et fournir les noms de colonnes ou les informations de données en fonction des parameters transmis.

 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; ssortingng columnDelimiter = varCollection["User::ColumnDelimiter"].Value.ToSsortingng(); SsortingngBuilder sb = new SsortingngBuilder(); 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); } ssortingng ftpUri = ssortingng.Concat(varCollection["User::FTPServerName"].Value, varCollection["User::FTPRemotePath"].Value, varCollection["User::FileName"].Value); FtpWebRequest ftp = (FtpWebRequest)FtpWebRequest.Create(ftpUri); ftp.Method = WebRequestMethods.Ftp.UploadFile; ssortingng ftpUserName = varCollection["User::FTPUserName"].Value.ToSsortingng(); ssortingng ftpPassword = varCollection["User::FTPPassword"].Value.ToSsortingng(); ftp.Credentials = new System.Net.NetworkCredential(ftpUserName, ftpPassword); using (StreamWriter sw = new StreamWriter(ftp.GetRequestStream())) { sw.WriteLine(sb.ToSsortingng()); sw.Flush(); } Dts.TaskResult = (int)ScriptResults.Success; } public void WriteRowData(DataTable currencies, DataRow row, ssortingng columnDelimiter, bool isHeader, ref SsortingngBuilder sb) { int counter = 0; foreach (DataColumn column in currencies.Columns) { counter++; if (isHeader) { sb.Append(column.ColumnName); } else { sb.Append(row[column].ToSsortingng()); } if (counter != currencies.Columns.Count) { sb.Append(columnDelimiter); } } sb.Append(System.Environment.NewLine); } } } 

Une fois les tâches configurées, le stream de contrôle du package devrait ressembler à celui montré dans la capture d'écran n ° 10 .

La capture d'écran n ° 11 montre la sortie de l'instruction d'exécution de procédure stockée EXEC dbo.GetCurrency.

Exécute le package. La capture d'écran # 12 montre l'exécution réussie du package.

En utilisant le module complémentaire FireFTP disponible dans le browser FireFox , j'ai ouvert une session sur le site FTP et vérifié que le file a bien été téléchargé sur le site FTP. Référez la capture d'écran # 13 .

L'examen du contenu en ouvrant le file dans Notepad ++ montre qu'il correspond à la sortie de la procédure stockée. Référez la capture d'écran # 14 .

Ainsi, l'exemple a montré comment écrire les résultats de la database sur un site FTP sans avoir à utiliser de files temporaires / locaux.

J'espère que ça aide quelqu'un.

Captures d'écran:

# 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 : Fichier_In_FTP

# 14 : File_Contents

Si vous étiez autorisé à implémenter des assemblages d'intégration CLR, vous pouvez utiliser FTP sans avoir à écrire un file temporaire:

 public static void DoQueryAndUploadFile(ssortingng uri, ssortingng username, ssortingng password, ssortingng 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(); } } 

Y a-t-il un server où vous pouvez utiliser où vous pouvez créer un file temporaire? Si c'est le cas, créez un service Web qui renvoie un tableau contenant le contenu du file. Appelez le service Web depuis l'ordinateur sur lequel vous pouvez créer un file temporaire, utilisez le contenu du tableau pour créer le file temporaire et dépassez-le.

S'il n'y a pas du tout où vous pouvez créer un file temporaire, je ne vois pas comment vous pourrez envoyer quelque chose par FTP.

Essayez d'utiliser une procédure stockée CLR. Vous pourriez être en mesure de find quelque chose, mais sans d'abord créer un file temporaire, il pourrait être difficile. Pourriez-vous créer un partage sur une autre machine et y écrire, puis ftp à partir de là?

Script à partir du server FTP, et appelez simplement le proc stocké.

Le problème, c'est que je ne peux pas créer un file local / temporaire que je peux ensuite transférer par FTP.

Cette ressortingction n'a aucun sens, essayez de parler gentiment à DBA et expliquez-lui. Il est tout à fait raisonnable pour tout process Windows ou travail de créer des files temporaires à l'location approprié, par exemple% TEMP% folder. En fait, l'exécution de SSIS elle-même crée souvent des files temporaires là-bas – donc si DBA vous permet d'exécuter SSIS, il vous permet de créer des files temporaires :).

Tant que DBA comprend que ces files temporaires ne créent pas de problème ou de charge de travail supplémentaire pour lui (expliquez qu'il n'a pas besoin de définir des permissions spéciales, ou les sauvegarder, etc.), il devrait accepter de vous laisser les créer.

La seule tâche de maintenance pour DBA consiste à nettoyer périodiquement le directory% TEMP% au cas où votre travail SSIS échoue et laisse le file derrière. Mais il devrait le faire de toute façon, car de nombreux autres process peuvent faire de même. Un travail SQL Agent simple fera cela.