StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This step-by-step example is for others who might stumble upon this question. This example uses <em>Windows Server 2008 R2 server</em> and <em>SSIS 2008 R2</em>. Even though, the example uses <em>SSIS 2008 R2</em>, the logic used is applicable to <em>SSIS 2005</em> as well. Thanks to <code>@Kev</code> for the <em>FTPWebRequest</em> code.</p> <p>Create an SSIS package (<a href="http://learnbycoding.com/2011/07/creating-a-simple-ssis-package-using-bids/">Steps to create an SSIS package</a>). I have named the package in the format YYYYMMDD_hhmm in the beginning followed by <em>SO</em> stands for Stack Overflow, followed by the <em>SO question id</em>, 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 <em>Adventure Works</em> and <em>Practice DB</em>. I will be using <em>Adventure Works</em> data source, which points to <em>AdventureWorks</em> database downloaded from <a href="http://msftdbprodsamples.codeplex.com/">this link</a>. Refer screenshot <strong>#1</strong> at the bottom of the answer.</p> <p>In the <em>AdventureWorks</em> database, create a stored procedure named <em>dbo.GetCurrency</em> using the below given script.</p> <pre><code>CREATE PROCEDURE [dbo].[GetCurrency] AS BEGIN SET NOCOUNT ON; SELECT TOP 10 CurrencyCode , Name , ModifiedDate FROM Sales.Currency ORDER BY CurrencyCode END GO </code></pre> <p>On the package’s Connection Manager section, right-click and select <em>New Connection From Data Source</em>. On the <em>Select Data Source</em> dialog, select <em>Adventure Works</em> and click <em>OK</em>. You should now see the Adventure Works data source under the Connection Managers section. Refer screenshot <strong>#2</strong>, <strong>#3</strong> and <strong>#4</strong>.</p> <p>On the package, create the following variables. Refer screenshot <strong>#5</strong>.</p> <ul> <li><p><em>ColumnDelimiter</em>: 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 (\t), you might need to change the code used in this example accordingly.</p></li> <li><p><em>FileName</em>: 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.</p></li> <li><p><em>FTPPassword</em>: 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.</p></li> <li><p><em>FTPRemotePath</em>: 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 <a href="ftp://myFTPSite.com/ssis/samples/uploads">ftp://myFTPSite.com/ssis/samples/uploads</a>, then the RemotePath would be /ssis/samples/uploads.</p></li> <li><p><em>FTPServerName</em>: This variable is of type String. This will contain the FTP site root URI. For example if the complete FTP URI is <a href="ftp://myFTPSite.com/ssis/samples/uploads">ftp://myFTPSite.com/ssis/samples/uploads</a>, then the FTPServerName would contain <a href="ftp://myFTPSite.com">ftp://myFTPSite.com</a>. You can combine FTPRemotePath with this variable and have a single variable. It is up to your preference.</p></li> <li><p><em>FTPUserName</em>:This variable is of type String. This will contain the user name that will be used to connect to the FTP website.</p></li> <li><p><em>ListOfCurrencies</em>: 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.</p></li> <li><p><em>ShowHeader</em>: 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.</p></li> <li><p><em>SQLGetData</em>: This variable is of type String. This will contain the Stored Procedure execution statement. This example uses the value EXEC dbo.GetCurrency</p></li> </ul> <p>On the package’s <em>Control Flow</em> tab, place an <em>Execute SQL Task</em> and name it as <em>Get Data</em>. Double-click on the Execute SQL Task to bring the <em>Execute SQL Task Editor</em>. On the <em>General</em> section of the <em>Execute SQL Task Editor</em>, set the <em>ResultSet</em> to <code>Full result set</code>, the <em>Connection</em> to <code>Adventure Works</code>, the <em>SQLSourceType</em> to <code>Variable</code> and the <em>SourceVariable</em> to <code>User::SQLGetData</code>. On the Result Set section, click Add button. Set the Result Name to <code>0</code>, this indicates the index and the Variable to <code>User::ListOfCurrencies</code>. The output of the stored procedure will be saved to this object variable. Click <em>OK</em>. Refer screenshot <strong>#6</strong> and <strong>#7</strong>.</p> <p>On the package’s <em>Control Flow</em> tab, place a Script Task below the Execute SQL Task and name it as <em>Save to FTP</em>. Double-click on the Script Task to bring the <em>Script Task Editor</em>. On the Script section, click the <code>Edit Script…</code> button. Refer screenshot <strong>#8</strong>. This will bring up the Visual Studio Tools for Applications (VSTA) editor. Replace the code within the class <code>ScriptMain</code> in the editor with the code given below. Also, make sure that you add the using statements to the namespaces <code>System.Data.OleDb</code>, <code>System.IO</code>, <code>System.Net</code>, <code>System.Text</code>. Refer screenshot <strong>#9</strong> 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.</p> <pre><code>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); } } } </code></pre> <p>Once the tasks have been configured, the package’s Control Flow should look like as shown in screenshot <strong>#10</strong>.</p> <p>Screenshot <strong>#11</strong> shows the output of the stored procedure execution statement EXEC dbo.GetCurrency. </p> <p>Execute the package. Screenshot <strong>#12</strong> shows successful execution of the package.</p> <p>Using the <em>FireFTP</em> add-on available in <em>FireFox</em> browser, I logged into the FTP website and verified that the file has been successfully uploaded to the FTP website. Refer screenshot #<strong>13</strong>.</p> <p>Examining the contents by opening the file in Notepad++ shows that it matches with the stored procedure output. Refer screenshot #<strong>14</strong>.</p> <p>Thus, the example demonstrated how to write results from database to an FTP website without having to use temporary/local files.</p> <p>Hope that helps someone.</p> <p><strong>Screenshots:</strong></p> <p><strong>#1</strong>: Solution_Explorer</p> <p><img src="https://i.stack.imgur.com/1C8VG.png" alt="Solution_Explorer"></p> <p><strong>#2</strong>: New_Connection_From_Data_Source</p> <p><img src="https://i.stack.imgur.com/hYZqX.png" alt="New_Connection_From_Data_Source"></p> <p><strong>#3</strong>: Select_Data_Source</p> <p><img src="https://i.stack.imgur.com/ccxdu.png" alt="Select_Data_Source"></p> <p><strong>#4</strong>: Connection_Managers</p> <p><img src="https://i.stack.imgur.com/Rw8xv.png" alt="Connection_Managers"></p> <p><strong>#5</strong>: Variables</p> <p><img src="https://i.stack.imgur.com/Ov6Ac.png" alt="Variables"></p> <p><strong>#6</strong>: Execute_SQL_Task_Editor_General</p> <p><img src="https://i.stack.imgur.com/sMP6f.png" alt="Execute_SQL_Task_Editor_General"></p> <p><strong>#7</strong>: Execute_SQL_Task_Editor_Result_Set</p> <p><img src="https://i.stack.imgur.com/D7iKl.png" alt="Execute_SQL_Task_Editor_Result_Set"></p> <p><strong>#8</strong>: Script_Task_Editor</p> <p><img src="https://i.stack.imgur.com/Yz20f.png" alt="Script_Task_Editor"></p> <p><strong>#9</strong>: Script_Task_VSTA_Code</p> <p><img src="https://i.stack.imgur.com/C39Xc.png" alt="Script_Task_VSTA_Code"></p> <p><strong>#10</strong>: Control_Flow_Tab</p> <p><img src="https://i.stack.imgur.com/kFLeF.png" alt="Control_Flow_Tab"></p> <p><strong>#11</strong>: Query_Results</p> <p><img src="https://i.stack.imgur.com/t3aGT.png" alt="Query_Results"></p> <p><strong>#12</strong>: Package_Execution_Successful</p> <p><img src="https://i.stack.imgur.com/tYKwu.png" alt="Package_Execution_Successful"></p> <p><strong>#13</strong>: File_In_FTP</p> <p><img src="https://i.stack.imgur.com/Yt8fD.png" alt="File_In_FTP"></p> <p><strong>#14</strong>: File_Contents</p> <p><img src="https://i.stack.imgur.com/nuf2M.png" alt="File_Contents"></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload