VB.Net: Download file from FTP and UnZip

Welcome to CodeSpread! Experience: Many application requires data migration tasks to execute on daily basis. To achieve this we generally seek help of SSIS package, if at all we are making use of SQL sever. So, consider a case where you need to download a file from FTP location. Besides this, what if the file present on the FTP is in Zip format. How will you unzip and download. . . ? Don’t worry!! Here is the solution: You need to import the following namespaces: System.IO.FileStream System.IO.Compression Microsoft.SqlServer.Dts.Runtime The below code is written in VB.Net: CODE: Dim success As Boolean = True Dim FilePath As String FilePath = Dts.Variables("FilePath").Value.ToString() Dim uncompressedFileName As String Dim bytes(Int16.MaxValue) As Byte Dim n As Integer = 1 uncompressedFileName = FilePath Dts.Events.FireInformation(0, "", "decompressing " + FeedPath + " to " + uncompressedFileName, Nothing, -1, True) Using writer As New IO.FileStream(uncompressedFileName, FileMode.Create) Using compressedStream As Stream = File.Open(FilePath, FileMode.Open, FileAccess.Read, FileShare.None) Using unzipper As New GZipStream(compressedStream, CompressionMode.Decompress) Do Until n = 0 n = unzipper.Read(bytes, 0, bytes.Length) writer.Write(bytes, 0, n) Loop unzipper.Close() End Using compressedStream.Close() End Using writer.Close() success = True End Using Brief Overview: Here “FilePath” is the location of the respective file on the FTP and Dts is the main object on which your package does all the operation and finally returns the results. GZipStream(Read more here) is the method used for compression task which is present inside System.IO.Compression Dts.Variables(“FilePath”).Value – This line is using the variable named “FilePath” which should be declared while creation of the package, probably one among the user defined variables.  Please note that it is recommended and good coding habit to close the IO stream connection used while read/write operation of any file. Rest of the code is self explanatory. Happy...

Read More

SQL:My first SSIS Package

I would like to share my experience about a situation, where I was given a task to export a file(say XML) from FTP location and get the data from this file populated into our database tables. Moreover, this task was required to execute everyday periodically. I asked my mentor and he introduced me to the concept of SSIS. Lets explore the experience here. Why SSIS? SSIS makes things easier. SSIS (SQL Server Integration Services) is MS SQL Server component, used for variety of data migration tasks. With respect to our scenario, we will be using two very efficient features: 1. FTP Task 2. Script task How to create a SSIS package? Basic steps included here. Create package: Launch “SQL Server Business Intelligence development Studio” which is a component of SQL Server. Then go to File -> New -> Project and you will see the following dialog box:   Select “Integration Services Project” as a template and name the Project per your convenience and press OK. You will see the below window in front of you:   In the “Toolbox” which comes under View -> Toolbox, you will find the real power(Features) of SSIS. As per our current need, we need to create a FTP Task and Script task as shown below: Here “Download file from FTP” is a FTP task and the “Script task” is Script task. The green arrow shown here is the direction of flow of control. First it will execute FTP task after that it will execute the Script task. Double click on FTP box or select editor from properties, You will find the FTP editor as shown below: Here you will find few fields that needs to be filled for proper operation. All fields are self-explanatory and can be customized per our need.for ex: “RemotePath” will accept the file location and name in the FTP. Similarly for Script task, Double click on Script box or select editor from properties,see script task editor below. Here also, there are few fields which are self-explanatory and can be customized per our needs for ex: “ScriptLanguage” to define the language,”Entry point” defines the method which begins the execution. Here we have made use of VB.Net. Moreover, here we have defined some User defined variables which are required to pass information like DB Connection string etc. The button “Design Script…” plays more important role in execution of Script task. In the Design Script window, we will be writing the code for Downloading the file from FTP and populate the Database tables with data present in the downloaded file. Moreover, we can compress/decompress files while we download from FTP. Please note that, there is no native task available...

Read More