Post reply

Fetch file from http (web) server using SSIS?

  • November 24, 2005 at 1:39 am

    #166071

    Is it possible to use SSIS to get a file from a web server URL?

    Note this is just a normal web server that is serving up some files (not a web service). We have a couple of cases where clients and suppliers are providing information this way because its very easy for them to set it up.

  • November 29, 2005 at 2:42 am

    #606351

    Just happened to do this as a sample for my book... Jamie told me about this post.

    Here you go:

    Public Sub Main()

       Dim httpConnection As Microsoft.SqlServer.Dts.Runtime.HttpClientConnection

       Dim temp As Object

       temp = Dts.Connections(

    "JPGToDownload").AcquireConnection(Nothing)

       httpConnection =

    New HttpClientConnection(temp)

       httpConnection.DownloadFile(Dts.Variables(

    "DownloadFile").Value.ToString(), True)

       Dts.TaskResult = Dts.Results.Success

    End Sub

    The trick is that the HTTP Connection Manager returns a native object and you have to create a managed wrapper object around it to make it available to the script task. Once you get past that, it's pretty simple. This code looks for the connection and the file to download from variables to make the package a little more flexible.

    HTH,

    K

  • November 29, 2005 at 11:16 am

    #606496

    and when will we be getting the book?

    Cheers,

    david russell

  • November 29, 2005 at 9:18 pm

    #606613

    March, hopefully...

    K

  • December 1, 2005 at 10:43 am

    #607133

    Will there be a "beta" copy like Farmer's book on Scripting?  Funny, it was only $14.95 for the book with a retail of 24.99... and the two day shipping was almost $46 additional... which they ultimately refunded me because it didn't show up on schedule... and now, for over a month, they haven't debited my card for any of it - although I got the refund.

    I LOVE getting books like that

    Cheers,

    david russell

  • December 1, 2005 at 2:03 pm

    #607195

    Well, can't promise any such refunds, sorry.

     

    It's a different animal than Donald's book. It looks like it will be hardbound and around 1600 pages. There won't be a beta copy, no.

     

    K

  • December 2, 2005 at 5:45 am

    #607308

    Thanks Kirk that looks spot on. Im going to try it this weekend.

    Ill be looking for the book too.

  • December 2, 2005 at 10:46 am

    #607442

    Apparently, Ash blogged about this a while back with a different method for downloading from HTTP. He posted that here.

    http://sqljunkies.com/WebLog/ashvinis/archive/2005/05/25/15653.aspx

     

    K

  • December 6, 2005 at 5:01 am

    #607901

    I tried this (Kirks original post) and it works.

    I had not used the scripting task before but managed to get it to work quite quickly. With a bit of effort I managed progress logging and debugging. This is definitely a winner and beats DTS. I always avoided DTS in favour of T-SQL or scripting host if possible.

    SSIS is living up to its claim to be a great ETL tool.

Viewing 9 posts - 1 through 9 (of 9 total)

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