I am working on a SSIS package that extracts some data from DB to a file then uploads it to a FTP server. I need to execute several FTP commands but seems like the SSIS FTP task only allows me to specify source & destination locations.
Does any one know how to make SSIS execute a FTP script without having to create a Script Task and write VB.NET code?
Unfortunately no, I think you might need to use a script task. You should be able to find a .Net FTP library to re-use (adding it as a reference), and you can store user / pass / connection information in variables and access it within the script for maximum portability / maintainability.
There's a few routes you could take...
Assuming you have an FTP client that can be run from the command line, you can use the Execute Process task to run the FTP client.
Alternatively, you could create a shell script (like a batch script) to send commands to the FTP client, and use the Execute Process task to execute the shell script. This will be necessary if you need to send a series of commands in order to the client, and can't do everything via a single set of input parameters: i.e. a command-line FTP client that accepts a command script as an input parameter (There probably are some more robust FTP clients out there that do this).
Last, as Dane mentioned, your alternative is to use the Script Task. This allows you to work with managed code (VB.NET) to perform more complex operations. You could "roll your own" assembly to create the FTP connection, run commands, etc., or use a third-party assembly to avoid writing an FTP client yourself, and just write some VB.NET to make use of it.
If you're looking on some of the basics on creating an FTP client in .NET, this article will get you started: http://support.microsoft.com/kb/812404 Though honestly, I'd look for a third party library that does what you need.