none
how can Unzip file text file using SSIS

    Pregunta

  •  

    Hi,

    I am pulling text files in gzip format from UNIX system. I want to unzip these files and then import data from these files into database using SSIS.

    domingo, 01 de octubre de 2006 9:48

Respuestas

Todas las respuestas

  • Run your favorite unzip utility (there are plenty of gzip-compatible archivers out there, including gzip itself) using Execute Process task.
    domingo, 01 de octubre de 2006 21:01
  • In our environment, we are not allowed to load 3rd party software to unzip a file.  However, we did create a VB Script file that accepts an argument for filename and a second argument for destination directory.  We use CopyHere to accomplish the task letting WScript handle execution.  This involves keeping a vbs file on the server.

     

    However, we would ideally like to use the CopyHere (or some similar command) from within SSIS.

     

    Any suggestions?

    Thanks

     

    miércoles, 16 de enero de 2008 14:45
  • Using a script task, you should be able to execute any VB.Net command you wish.
    miércoles, 16 de enero de 2008 15:36
  • After figuring out the correct search criteria to type into Google, I found the website:

    http://www.vbprofs.com/Source_Codes/.NET/Zip%10Unzip_files_from_your_VB.NET_application/

     

    In it, Thomas Kaloyani describes Zipping and Unzipping using ONLY resources accessible to SSIS.  He includes code for zipping and unzipping.  I had previously done this using VBScript in DOS using WScript, but this version seems to be many times faster at unzipping.

     

    The one thing not shown in the code is to make reference to the Visual J# library so you can use the Imports java.util.zip so ADD REFERENCE: vjslib.dll -- part of the MS .NET framework.

     

    I want to make sure credit is given to the correct author: Thomas Kaloyani.  In it, he gives credit to Valeri on the code project website: http://www.codeproject.com/KB/cs/vmeasyzipunzip.aspx for pointing him in the right direction.

     

    All I did was duplicate what we needed from Thomas' version to work for our case.  The source file and destination directory should be changed to be variable.  Here is the code for Unzipping...

     

    Code Block

    ' Microsoft SQL Server Integration Services Script Task

    ' Write scripts using Microsoft Visual Basic

    ' The ScriptMain class is the entry point of the Script Task.

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

    Imports java.util.zip

     

    Public Class ScriptMain

    ' The execution engine calls this method when the task executes.

    ' To access the object model, use the Dts object. Connections, variables, events,

    ' and logging features are available as static members of the Dts class.

     

    Public Sub Main()

    Try

    Dim strSourceFile As String

    Dim strDestinationDirectory As String

    strSourceFile = "C:\testzip\filename.zip"

    strDestinationDirectory = "C:\testzip\Unzipped\"

     

    Dim oFileInputStream As New java.io.FileInputStream(strSourceFile)

    Dim oZipInputStream As New java.util.zip.ZipInputStream(oFileInputStream)

    Dim bTrue As Boolean = True

    Dim sbBuf(1024) As SByte

    While 1 = 1

    Dim oZipEntry As ZipEntry = oZipInputStream.getNextEntry()

    If oZipEntry Is Nothing Then Exit While

    If oZipEntry.isDirectory Then

    If Not My.Computer.FileSystem.DirectoryExists(strDestinationDirectory & oZipEntry.getName) Then

    My.Computer.FileSystem.CreateDirectory(strDestinationDirectory & oZipEntry.getName)

    End If

    Else

    Dim oFileOutputStream As New java.io.FileOutputStream(strDestinationDirectory.Replace("\", "/") & oZipEntry.getName())

    While 1 = 1

    Dim iLen As Integer = oZipInputStream.read(sbBuf)

    If iLen < 0 Then Exit While

    oFileOutputStream.write(sbBuf, 0, iLen)

    End While

    oFileOutputStream.close()

    End If

    End While

    oZipInputStream.close()

    oFileInputStream.close()

     

    Catch ex As Exception

    Throw New Exception(ex.Message)

    End Try

     

    End Sub

     

    End Class

     

     

    miércoles, 16 de enero de 2008 18:59
  • I tried to code above, including adding the reference to vjslib.dll into the script task.  When I try to run, I get the following error:

     

    Could not load file or assembly 'vjslib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.

     

    Is there something I missed doing?

     

    Frank

    jueves, 24 de enero de 2008 20:39
  • The error sounds like the code is correct because when it tries to do the IMPORTS java.util.zip, it cannot find it.  I don't know what the Culture or PublicKeyToken has to do with anything -- something in the internals or security I'd guess.

     

    We are currently using VS 2005 with Framework 2.0.  VS 2005 with framework 3 or 3.5 should work as well.

    Here's how I would troubleshoot the problem:

    Go into VS 2005 and open your Intergration Services project.

    • Open your script object.
    • Once the script development tool opens, find the PROJECT EXPLORER pane.  You may have to click View --> Project Explorer (CTRL-ALT-L) to get it to show up.
    • Expand the tree to display all your references -- it should list vjslib.  (I also have Microsoft.SqlServer.ManagedDTS, Microsoft.SqlServer.ScriptTask, Microsoft.Vsa, System, System.Data, and System.Windows.Forms -- these loaded automatically.)
    • If it does not appear, try making a reference to it again.  Sometime the programming environment is flaky.
    • If it's reference is listed, then...
      • Delete the reference
      • Save the file
      • Try to run again.  See if it is the same error.
        • The same error means the reference is somehow bad
          • Add the reference back in
          • Save the file
          • Test again.
            • If it fails, create a new project and try the same thing in a fresh project.
              • If a fresh project (pretty much a duplicate of the first) still  acts as though there is something wrong with the referenced dll, then try re-installing the framework (or try a fresh copy of the DLL from another developer PC).

    That's how I'd troubleshhot the problem.  If you have another developer PC to test on, you might even try that route first.

     

    Good Luck -- I'd like to know what you find.

    viernes, 25 de enero de 2008 12:31
  • There is a third party control you can use

    http://www.cozyroc.com/

     

    viernes, 25 de enero de 2008 14:53
  • I had to opt for the jave code because our client does not have any third party software approved for use on the server to perform the 'unzip' task.  They have Winzip approved for client machines, but the SSIS package works on the server.

     

    With that said, I am a proponent to buying third party software if it is an advantage in cost and quality.  I don't want to re-invent the wheel when I can buy one at the local tire store.  For instance, we are using Telerik controls for our ASP .NET screens.

    viernes, 25 de enero de 2008 15:09
  •  

    install  Visual J# 2.0 redistributable package to correct the error.
    miércoles, 05 de marzo de 2008 6:28
  • Just had the same error, and it had nothing to do with J#.

    Folder structure within the zip must exist in order for the extraction to take place.
    so if your file in the zip is: \private\BDA\BDA.xls
    then wherever you extract it to must have the subdirectories: \private\BDA\
    martes, 31 de marzo de 2009 13:47
  • That's way more complex than it needs to be.

    The only thing you might need to use the script task for is to set variables you can't otherwise do in the package itself. Execute Process task is the perfect tool for the job. We use it here to zip, encrypt extracts before shipping them off. The only gotcha is that if you've got a lot of switches after the exe, test them in a command prompt before you run your package. It will save you a lot of time.
    martes, 31 de marzo de 2009 21:44
  • I tried to code above, including adding the reference to vjslib.dll into the script task.  When I try to run, I get the following error:

     

    Could not load file or assembly 'vjslib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.

     

    Is there something I missed doing?

     

    Frank


    I got the same kind of error the first time... had installed a 32 bit version of Visual J# Redistributable Packages on a 64 bit machine. You can find the correct version here. And a C# version of that script here.

     

     

    sábado, 08 de enero de 2011 19:42
  • Guys, I am not good in VB and still learning.

     

    Any one with a codes to :

    1.  Script to unzip/unrar document

    2.  Script to load/append excel/CSV/text file/s into a table

    3.  Script to go search within a folder if there is any new file (excel/CSV/text) added.

    Am struggling and the library for vjslib, where can i get it? Only found version 1.0.0.0.0

    viernes, 06 de mayo de 2011 11:07
  • This blog solves your problem - http://duncansutcliffe.wordpress.com/2010/04/14/unzipping-and-zipping-files-in-ssis/ 

    Please check.


    "It is not enough to aim, you must hit."
    martes, 09 de agosto de 2011 5:37