Download file from web using SSIS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Download file from web using SSIS

Author

Topic

tpavan7329
Starting Member

18 Posts

Posted - 2011-05-11 : 14:08:29

Hi,
Client gets a file everyday from the website, i download file manually into a location and then i trigger the ssis package job manually. As client wants to automate the complete process, iam trying to see if there is a way to download the file , unzip it and keep that on a location.

Thanks,
Pavan

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-11 : 14:25:47

SSIS has a FTP connection manager and HTTP connection manager described here:
http://msdn.microsoft.com/en-us/library/ms141015.aspx
http://msdn.microsoft.com/en-us/library/ms137602.aspx

However, once when I tried to use it, I ran into some difficulties - details of which I don't recall. I am convinced that it was my shortcomings rather than anything inherently wrong with the connection managers. So you might want to give that a try.

Partly because I ran into difficulties, and partly because I was more at home with C# code, I wrote my own code to download the files I wanted. System.Net namespace has several classes that lets you connect to remote sites via ftp or http and upload/download files. If you decide to go that route, look for FtpWebRequest and HttpWebRequest on this page: http://msdn.microsoft.com/en-us/library/system.net.aspx.

latch
Yak Posting Veteran

62 Posts

Posted - 2011-05-11 : 17:38:35

I think using a script task with vb script or C# script you can able to download a file then using execute process task you can unzip it.

check these:
http://www.proteanit.com/b/2008/05/14/ssis-download-a-file-from-a-website/

tpavan7329
Starting Member

18 Posts

Posted - 2011-05-22 : 14:55:23

I have created a variable with Expression as my URL and i have used below code in the Script Task to download the file from web using ssis

Imports System
Imports System.IO
Imports System.Text
Imports System.Windows.Forms
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Web
Imports System.Net
Imports Microsoft.VisualBasic

Public Class ScriptMain

Public Sub Main()

Dim WebConnection As New WebClient()
Dim Cre As New Net.NetworkCredential("username", "password")
Try

With WebConnection
.BaseAddress = Dts.Variables("variablename").Value.ToString()
.Credentials = Cre

End With
Catch ex As Exception
Dts.Events.FireError(0, "Problem connecting to website: ", ex.Message, "", 0)
End Try
Try
With WebConnection
.DownloadFile(.BaseAddress, "D:\DropBox\same.zip")

End With
Catch ex As Exception
Dts.Events.FireError(0, "Problem downloading file: ", ex.Message, "", 0)

End Try

Dts.TaskResult = Dts.Results.Success
End Sub

End Class

T Pavan Kumar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources