Post reply

How to zip a text file in a DTS Task

  • February 6, 2007 at 9:50 am

    #176698

    Here is what I am trying todo.

    1. Run a query that writes the Results to a File Called AUX_Export_.Txt (I created a DTS to run on Schedule)

    2. I then created a ActiveX Script to Rename this file to :AUX_Export_020507.txt                                    You can see I am adding the current Date's Month with a two digits Month for example 1 for Jan. should be 01 same thing for the Day as two digits and year as two digits.

    3. Now I need to Zip this text file and move it out to a FTP directory?????????????????????HELP

    I have 7-zip which I should be able to call from the command line with: "C:/Program Files/7-Zip/7z.exe" a -tzip "R:\Avectra\Queries\Mike Queries\Reports\Commerce Register\ReadyForZip\*.txt"

    Can someone tell me what kind of task I need to call or make to make a commend line call? Then I think all I need is a File Transfer Task to Move the zip file to a FTP directory..

    Question can I create a global Variable That holds the file name that I Rename the text file to in Step 2? That way I know I am calling the Zip file command with the correct file name instead of *.txt?zipping up.

    Please give me details so I don't have to struggle. 

    I have been working on this for while I know someone has done this before. Export some data to a text file rename file on the fly then zip the file with same file name then move the zip file.

    Thanks

    Michael Webb

  • February 6, 2007 at 3:25 pm

    #688158

    HELLO CAN ANYONE HELP!

  • February 7, 2007 at 5:47 am

    #688261

    I came across a similar problem i had to achieve a few months back.

    What I did was to setup an Execute Process Task to take the full command line parameters.  When ran, this would unzip the file to a specified output folder.

    To do this, I stored the full command line parameters in Global Variable(s).  These were then set against the Execute Process Task by using a Dynamic Process Task.  When the DTS is run at this point, you should be able to unzip the file to a specified location.

    You could then add an FTP task after this to send it.

    Hope this helps...

  • February 7, 2007 at 8:39 am

    #688303

    Can you give me more details on how this is done?

  • February 9, 2007 at 9:49 am

    #688896

    Michael,

    All you need to do is basically store your command in a global variable.  You can create a global variable by right-clicking the background DTS designer of your package.  From the pop-up, select Package Properties.  This will display a tabbed dialog.  The second tab has the caption of Global Variables.  Click on it to display them.

    In the Name column, enter a meaningful name for you zipped filename and path.  Give it a string type, and enter the name of it.  It may be worth creating 2 - one for the filename and the other for the path, lets say ZippedFileName and ZippedFilePath.

    What you want to do for this is to insert 2 tasks:  1 Execute Process Task that will perform the zip/unzip of your file and the other an ActiveX Script Task that will set the Execute Process Task’s properties.

    The Execute Process Task needs to know which file it needs to zip/unzip.  This should execute a command line application such as pkzip or in your case C:/Program Files/7-Zip/7z.exe" a -tzip "R:\Avectra\Queries\Mike Queries\Reports\Commerce Register\ReadyForZip\*.txt”.  This will be set by the ActiveX Script Task.

    The ActiveX Script Task will set the properties of the Execute Process Task.  You could use a dynamic properties task, but you will need to do some work programmatically to work out the filename and the path.  The following ActiveX VB Script will set the ProcessCommandLine property of the Execute Process Task:

            Dim oPkg

            Dim oCPTask

            Set oPkg = DTSGlobalVariables.Parent

            Set oCPTask = oPkg.Tasks("DTSTask_DTSCreateProcessTask_1").CustomTask

            oCPTask.ProcessCommandLine = DTSGlobalVariables("ZippedFilePath").Value & "\" & DTSGlobalVariables("ZippedFileName").Value

            Main = DTSTaskExecResult_Success

     Then what you need to do next is to set the parameters on you need on the FTP Process.

    Hope this helps,

     

    Andez

     

     

     

     

  • February 9, 2007 at 9:54 am

    #688898

    Dude thank you so much.. Sometime I can be kind of slow!!!

  • February 12, 2007 at 10:35 am

    #689257

      We all can be slow at times...don't worry about it.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply