StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POScript Task in SSIS package to FTP
text
Body
copied!<p>All,</p> <p>I have an SSIS package (Sql Server 2008), that does a few data flow and file operations and then finally uploads the file to an FTP Server.</p> <p>I was able to get most of the work done, but one last critical piece. Using 'Data Flow' a text file is generated with data from the database. Now that file is zipped with a timestamp at the end Ex: "filename_08132012.zip".</p> <p>The timestamp changes every day, so its a new file every time to upload to the FTP server. So, instead of "FTP Task" (I could not figure out a way to make this work), I have a "Script Task" that looks for a file with today's date and uploads it to the FTP server. Following is the code, but I have two questions related to this:</p> <ol> <li>I want this "Script Task" to pick up that day's file ex: filename_08132012.zip, filename_08142012.zip. I have the code to convert date to string with proper format. But for some reason, it is not. </li> <li>How to step through the execution of this script file, the was Visual Studio allows for VB.net code? This script task GUI is not allowing to step through.</li> <li>It uploads an empty file to the FTP server. A file with 0 bytes. How do I troubleshoot this?</li> </ol> <pre class="lang-vb prettyprint-override"><code>' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic 2008. ' The ScriptMain is the entry point class of the script. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _ <System.CLSCompliantAttribute(False)> _ Public Class ScriptMain Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase Enum ScriptResults Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure End Enum Public Sub Main() ' ' Add your code here ' Try 'Create the connection to the ftp server Dim cm As ConnectionManager = Dts.Connections.Add("FTP") 'Set the properties like username & password cm.Properties("ServerName").SetValue(cm, "172.24.97.21") cm.Properties("ServerUserName").SetValue(cm, "bbxuser") cm.Properties("ServerPassword").SetValue(cm, "blockbuster") cm.Properties("ServerPort").SetValue(cm, "21") cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb cm.Properties("Retries").SetValue(cm, "1") 'create the FTP object that sends the files and pass it the connection created above. Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing)) 'Connects to the ftp server ftp.Connect() 'Build a array of all the file names that is going to be FTP'ed (in this case only one file) Dim files(0) As String Dim FileDate As Date FileDate = System.DateTime.Today files(0) = "D:\NCR\Apps\RBX.SSIS.BBX_Customer_Extract\Email_Campaign\Redbox_EmailCampaign_and_Analytics_Extract_" + FileDate.ToString("mmddyyyy") + ".zip" 'ftp the file 'Note: I had a hard time finding the remote path directory. I found it by mistake by creating both the FTP connection and task in the SSIS package and it defaulted the remote path setting in the FTP task. ftp.SendFiles(files, "/Email Campaign", True, False) ' the True makes it overwrite existing file and False is saying that it is not transferring ASCII ftp.Close() Catch ex As Exception Dts.TaskResult = ScriptResults.Failure End Try Dts.TaskResult = ScriptResults.Success End Sub </code></pre> <p>End Class</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