ssis package download zip file per date

Posted on 2012-09-11
Medium Priority
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\file20120911.zip 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 -- file20120911.zip
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
  • 3

Author Comment

ID: 38387841
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

ID: 38387875
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\20120911.zip.

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/20120911.zip.

Author Comment

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

Author Comment

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

Accepted Solution

Alpesh Patel earned 2000 total points
ID: 38416930
You can get path as variable and create path + datestamp as file name in expression.

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     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 shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

840 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