ssis package download zip file per date

Posted on 2012-09-11
Last Modified: 2012-10-08
have an ftp site with multiple zip files that are setup per date such as:

I'm writing an ssis package to load a csv file that is in the zip files. what i'm having trouble with is downloading the zip file that is today's date as this package will run daily.
so i did the following
1. variable: zipFileName with a value of where i want to download it to: \\servername\share
2. placed a script task to generated the zip file name; readwritevariables: user:zipFileName
script code:
public void Main()
            Dts.TaskResult = (int)ScriptResults.Success;
            string mainVar = Dts.Variables["zipFileName"].Value.ToString(); //\\server\share\ set to given date year month day
            DateTime time = DateTime.Now;
            string format = "20120910"; //"yyyyMMdd";
            mainVar = "file" + format + ".zip";
          Dts.Variables["zipFileName"] = mainVar; //doesn't like this
will that set the variable name to my desired file name?
3. success then goes to the ftp task to download the respected new variable file name: say todays date it would be --
4. ftp task - under the file transfer i set the following:
  local parameters
   islocalpathvariable true
   localvariable user::localpath
   overwritefileatdest true
   operation receive files
   istransferascii false
  remote parameters
   isremotepathvariable true
   remotevariable  user:zipFileName

the ftp task gives an error: variable zipFileName doesnt start with /

how do i get this to generate a file name by ymd format and set it up with the ftp task?
Question by:fwstealer

    Author Comment

    i got the file name right by changing to:

    public void Main()
                Dts.TaskResult = (int)ScriptResults.Success;

                string mainVar = Dts.Variables["zipFileName"].Value.ToString();
                DateTime dt = DateTime.Now;
                string format = dt.ToString("yyyyMMdd");
                mainVar = "file" + format + ".zip";
                //Dts.Variables["zipFileName"] = mainVar;

    one small part working...
    LVL 16

    Expert Comment

    1. The easy way might be to ensure that only that file gets put into a specific folder, and then copy out and then delete the file with the FTP task.
    2. You want a file with a name like <StringConstant1>20120911<StringConstant2>.
    --- The two constant parts can be kept set up in a pair of variables.
    --- You can easily set a variable to be today's date, updated whenever the SSIS runs
    --- Concatenate these three to give you the file spec - \\servername\share\

    Oh, and if you're referring to the file at the other end of the FTP connection, use UNIX file names - forward slashes, so /share/

    Author Comment

    how do i get the ftp task editor to read the value of the variable: zipFileName?

    Author Comment

    i got it by adding another variable and starting the value with / then setting the ftp task to /zipfilename variable.
    LVL 21

    Accepted Solution

    You can get path as variable and create path + datestamp as file name in expression.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

         When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
    This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now