locked
Need a suggestion on how to implement the following task. RRS feed

  • Question

  •  I have to implement the following task and automate it. But I have no idea how to start with it. Please help me and suggest me the best approach to do this. If SSIS is not the way to implement this then through which technology/tool I can do this ?

    Step 1 :  I need to download 3 specific files(files with current date) from bunch of files which are on FTP site on a daily basis. 

    Step 2: Once the files are downloaded they need to be converted and delivered to a specific folder on mapped network drive.

    Friday, June 24, 2011 6:19 PM

Answers

    1. Create an SSIS package, and use three FTP Tasks.  You can configure one FTP Connection Manager to use in all three, but then specify the specific file to download in each of them.
    2. Depends what you mean by "converting" them... can't help you there without more info.
    3. Use a File System Task (or three) to copy/move the resulting files to a network share UNC (try not to use a mapped drive - it won't be mapped for the service account you're going to use to automate it).
    4. Deploy the package to an SSIS package repository (could just be the file system on a server or network share).
    5. Configure a SQL Agent Job to execute the SSIS package

    Todd McDermid's Blog Talk to me now on
    • Marked as answer by guest369 Thursday, June 30, 2011 6:58 PM
    Friday, June 24, 2011 6:36 PM

All replies

    1. Create an SSIS package, and use three FTP Tasks.  You can configure one FTP Connection Manager to use in all three, but then specify the specific file to download in each of them.
    2. Depends what you mean by "converting" them... can't help you there without more info.
    3. Use a File System Task (or three) to copy/move the resulting files to a network share UNC (try not to use a mapped drive - it won't be mapped for the service account you're going to use to automate it).
    4. Deploy the package to an SSIS package repository (could just be the file system on a server or network share).
    5. Configure a SQL Agent Job to execute the SSIS package

    Todd McDermid's Blog Talk to me now on
    • Marked as answer by guest369 Thursday, June 30, 2011 6:58 PM
    Friday, June 24, 2011 6:36 PM
  • Thanks for your immediate response. I'm very new to this stuff. 

     Where I have to specify the specific file to download in each of them ?

    How to configure a FTP Connection Manager ?

     



    Friday, June 24, 2011 7:10 PM
  • you can use MASK in the FTP Task to download all files contains current date. this is a sample of mask:

    http://www.rad.pasfu.com/index.php?/archives/28-Transfer-Multiple-Files-from-or-to-FTP-remote-path-to-local-path-SSIS.html


    http://www.rad.pasfu.com
    • Proposed as answer by Kalman Toth Thursday, June 30, 2011 3:03 PM
    Friday, June 24, 2011 8:26 PM
  • Thanks for your advice. I'll try this.
    Friday, June 24, 2011 8:59 PM
  • It's pretty straightforward to configure an FTP CM and FTP Task... try it and find out.  MSDN has decent documentation on it...
    Todd McDermid's Blog Talk to me now on
    • Proposed as answer by Kalman Toth Thursday, June 30, 2011 3:03 PM
    Friday, June 24, 2011 9:53 PM