Post reply

Fetching file from FTP and running DTS

  • September 1, 2006 at 12:20 am

    #76574

    hi,

    I have a task that is:

    I have to fetch a file from FTP location.The file is an excel file.

    After fetching the file I have to run DTS and transfer the data from excel to sql server 2005 database.

    There are different location and file names may be different.

     

    How can these be done. I am using ASP.NET 2.0

  • September 1, 2006 at 11:54 am

    #658109

    You can do this 2 ways:

    Try using "Dyanmic Propert Task" to dynamically change the connection attributes.

    DTS Load from Specific Loaction

    1.Create ActiveX Script or TSQL (using xp_cmdshell) to Copy the Excel from "Source" to a Specific loaction C:\MyExcel.xls

     

    2.Create a "Transform Data task" to load the data from C:\MyExcel.xls to SQL table

    3.Create ActiveX Script or TSQL (using xp_cmdshell) to delete the Excel from C:\MyExcel.xls

    To make it fancy I would store the Excel Name(Actual) and Loaction in a SQL Parameter Table that was if the location of the source or name of the excel changes you don't have to modify DTS but change the value in the parameter table.

    Hope this helps.

    Thanks

    Sreejith

  • September 2, 2006 at 8:30 am

    #658172

    Hi Sreejith,

    Sounds Good I will try this.

    But I would have such 20-30 ftp location and  excel files from different locations would be fetched.May be at specified time.I would create a job to do so.

    Would it be fesible??

  • September 3, 2006 at 3:16 pm

    #658232

    To use multiple files I would take the "Copy Excel" out of DTS and into stored procedure as xp_cmdshell. In the SP the process will loop through and copy the files from various location to a predefined loaction. Depending upon your actual requirement, the DTS can be kicked off as xp_cmdshell either after copying all the files or after copying 1 file at a time.

    This sp can be aclled as a SQL Job.Hope this helps.

    Thanks

    Sreejith

  • September 4, 2006 at 12:52 am

    #658260

    Hi Sreejith,

    Can you please explain me this.Or give the links from where I can find the turotrials for this.As I am new to SSIS and DTS.

    Thanks

     

  • September 4, 2006 at 5:58 am

    #658286

    hi Sreejith,

    I am not geeting any thing like "Dyanmic Propert Task". From where I can add that.

  • September 20, 2006 at 12:12 am

    #661418

    hi,

    I have completed the task. Thanks a lot Sreejith.

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

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