none
Unzip->Remove unwanted character->zip->Rename->move to different location

    Question

  • Hi,

    I am using SSIS 2005 VERSION. I am looking for the below implementation using SSIS 2005 version.

    Need to achieve below requirements using SSIS 2005 version:

    1. Unzip file from the location F:\ . When I unzip I will get one folder inside the folder I will get one .txt file.

    2. Once I get the .txt file I have to read/any SSIS process to remove the first 2 letters of each row example below:

         DA1234  345   test       test1   test2

         DA345   789   test4      test5   test7

    Above is the content of text file from the each line starts with "DA" I need to remove that "DA" from each line.

    3. Once "DA" is removed rename the .txt file and zip with same name as .txt file and send it to different location say D:\.

    4. Once sending process is success delete the file from the location Original location F:\.

    Please help me to achieve the above requirement.

    Thanks...........

    Monday, October 14, 2013 10:32 AM

Answers

All replies

  • Here is a free unzip task that works in ssis 2005:
    http://taskunzip.codeplex.com/

    First task Unzip Task
    (or an Execute Process Task that executes a zipclient)

    Secord task Data Flow Task
    with the textfile as source and an other textfile as destination.
    Add a Derived Colum between them to clean the column

    Third task Zip Task
    Zip the textfile used in the destination

    Fourth task File System Task
    for deleting the file(s)

    If you don't know the name of the textfile within the zipfile, then you could add a Foreach Loop Container (file enumerator) to loop through all (1) files in the unzip folder.


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Proposed as answer by Vaishu00547 Monday, October 14, 2013 11:30 AM
    Monday, October 14, 2013 11:09 AM
    Moderator
  • I use J# library functions to do zipping unzipping of files by writing code inside Script Task

    Something like below

    http://microsoft-ssis.blogspot.in/2011/01/unzip-files-with-ssis.html

    you might need a For Each Loop to iterate through zip files from your location. Then use a variable to capture the file name and pass it to script task for unzipping. For cleansing data you can use execute sql task where you can use OPENROWSET or similar methods to remove the DA part. Once thats done you can again pass it onto script task to do zipping. Finally renaming and moving can be done using File System Task

    Please mark the post as answered if it answers your question

    Monday, October 14, 2013 11:18 AM

  • Hi Santosh,

    Add below exp in derived column transformation to remove first two characters from string.

    Exp: SUBSTRING([Column 0],LEN([Column 0]) - 2,LEN([Column 0]))

    Please mark the post as answered if it answers your question

    Regards,

    Vaishu

    Monday, October 14, 2013 11:32 AM
  • Hi..

    Thanks for your reply. Can you please provide some samples if you have? I am new to SSIS.

    Thanks.... 

    Tuesday, October 15, 2013 3:37 AM
  • Hi..

    Thanks for your reply. Can you please provide some samples if you have? I am new to SSIS.

    Thanks.... 


    On which part do you need clarification? What do you have so far?
    Do you want to use the third party unzip task or the J# version or an Execute Process Task?

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Tuesday, October 15, 2013 5:19 AM
    Moderator
  • I don't want to use any thirdyparty.  Execute Process Task is SSIS control. Please let me know how to implement my requirement using Execute Process Task. If possible please provide me a sample.

    Thanks...

    Tuesday, October 15, 2013 6:25 AM
  • I don't want to use any thirdyparty.  Execute Process Task is SSIS control. Please let me know how to implement my requirement using Execute Process Task. If possible please provide me a sample.

    Thanks...

    A little googling couldn't hurt ;-)

    http://www.youtube.com/watch?v=QK5cJqn6Pgs
    http://parasdoshi.com/2011/09/07/ssis-using-sql-server-integration-services-ssis-to-unzip-a-file/
    http://technet.microsoft.com/en-us/library/ms141166.aspx

    You first need to determine which (un)zip tool you want to use... WinRar, 7zip, WinZip, etc... basically a whole bunch of third party tools :-)


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Tuesday, October 15, 2013 6:35 AM
    Moderator