How to download a file from internet using SSIS

  • Daniel Calbimonte

    SSCarpal Tunnel

    Points: 4757

    Comments posted to this topic are about the item How to download a file from internet using SSIS

  • anishagargdiet

    Old Hand

    Points: 383

    Hi,

    I tried running the following command on power shell-------

    Start-BitsTransfer -source "http://www.textfiles.com/adventure/$filename" -destination "C:\sql\$221baker.txt"

    And getting the following error-------

    The term 'Start-BitsTransfer' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify

    that the path is correct and try again.

    At line:3 char:19

    + Start-BitsTransfer <<<< -source "http://www.textfiles.com/adventure/$filename" -destination "C:\sql\$221baker.txt"

    + CategoryInfo : ObjectNotFound: (Start-BitsTransfer:String) [], CommandNotFoundException

    + FullyQualifiedErrorId : CommandNotFoundException[/color]

    My power shell details:

    Name : Windows PowerShell ISE Host

    Version : 2.0

    InstanceId : b706f6b1-4720-48d9-9216-7f662cdba347

    UI : System.Management.Automation.Internal.Host.InternalHostUserInterface

    CurrentCulture : en-US

    CurrentUICulture : en-US

    PrivateData : Microsoft.PowerShell.Host.ISE.ISEOptions

    IsRunspacePushed : False

    Runspace : System.Management.Automation.Runspaces.LocalRunspace

    Please help.

    Regards,

    Anisha Gupta

  • David Kirzner

    SSC Enthusiast

    Points: 119

    Have been on the lookout for just such functionality !

    Can this be modified to support sites that require the user to logon

    and then navigate to a particular screen to pick up a file that has embedded in its name only the latest version of the report (Vendor generates the report 4X a day).

    Thanks much,

    David K.

  • anishagargdiet

    Old Hand

    Points: 383

    Hi David,

    Using other power shell command, we can login the other web site navigate to required page and download the file.

    Example---

    function Test-GmailLogin{

    Param(

    $account,

    $username,

    $password

    )

    $ie = New-Object -ComObject InternetExplorer.Application

    $ie.Visible = $true

    $ie.navigate('Sitename')

    while($ie.Busy){sleep -mil 5}

    if($ie.document.Url -match 'Inbox'){

    Write-Host 'Account already logged in' -ForegroundColor green -BackgroundColor white

    return $ie

    }else{

    $ie.document.getElementById("account").value=$account

    $ie.document.getElementById("username").value=$username

    $ie.document.getElementByID("password").value=$password

    $ie.document.getElementById("btnLogin").Click()

    }

    }

    $ie=Test-GmailLogin -account -username -password

    Anisha

  • anishagargdiet

    Old Hand

    Points: 383

    Hi David,

    We can use other power shell commands to login website and navigate to the required page and download the file.

    For example--

    $username = "username"

    $password = "password"

    $ie = New-Object -com InternetExplorer.Application

    $ie.visible=$false

    $ie.navigate("Url")

    while($ie.ReadyState -ne 4) {start-sleep -m 100}

    $ie.document.getElementById("username").value= "$username"

    $ie.document.getElementById("pass").value = "$password"

    $ie.document.getElementById("loginform").submit()

    start-sleep 20

    you can try for your gmail account.

  • rharderwijk

    Ten Centuries

    Points: 1119

    What you do need, apart from the correct PowerShell version, is access to the BITS service. If that has been disabled or you do otherwise not have access to that function, it will not work.

    Also, if you try to use this in a production environment, you might need to do some configuration for a proxy, as most companies will not allow a server to connect to the internet directly.

  • anishagargdiet

    Old Hand

    Points: 383

    Hi,

    Thanks for information.

    Anisha

  • Daniel Calbimonte

    SSCarpal Tunnel

    Points: 4757

    Did you tried to import the module?

    import-module bitstransfer

  • anishagargdiet

    Old Hand

    Points: 383

    Hey,,,I got Success,,Thanks a lot.:-)

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the article and extra info.

  • John Bigler

    SSC-Addicted

    Points: 409

    Although I like PowerShell too, as a BI developer, I prefer to keep as much of my code contained within SSIS as possible. So, in similar situations, I would just create a Script Task in SSIS to do the same thing (C# code sample below).

    // Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"

    Object ConnMgr = new Object();

    ConnMgr = (Object)(Dts.Connections["HTTP Connection Manager"].AcquireConnection(null) as Object);

    // Create a new HTTP client connection

    HttpClientConnection objHttpConnection = new HttpClientConnection(ConnMgr);

    // Save the file to a local path.

    string strFullFilePath = Dts.Variables["$Project::InboundDataFilesPath"].Value + @"\Datafile.txt";

    objHttpConnection.DownloadFile(strFullFilePath, true);

  • gbritton1

    SSCertifiable

    Points: 6520

    John Bigler (12/1/2016)


    Although I like PowerShell too, as a BI developer, I prefer to keep as much of my code contained within SSIS as possible. So, in similar situations, I would just create a Script Task in SSIS to do the same thing (C# code sample below).

    // Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"

    Object ConnMgr = new Object();

    ConnMgr = (Object)(Dts.Connections["HTTP Connection Manager"].AcquireConnection(null) as Object);

    // Create a new HTTP client connection

    HttpClientConnection objHttpConnection = new HttpClientConnection(ConnMgr);

    // Save the file to a local path.

    string strFullFilePath = Dts.Variables["$Project::InboundDataFilesPath"].Value + @"\Datafile.txt";

    objHttpConnection.DownloadFile(strFullFilePath, true);

    +1

    Nicer and cleaner IMHO

  • PHYData DBA

    SSCertifiable

    Points: 7541

    gbritton1 (12/1/2016)


    John Bigler (12/1/2016)


    Although I like PowerShell too, as a BI developer, I prefer to keep as much of my code contained within SSIS as possible. So, in similar situations, I would just create a Script Task in SSIS to do the same thing (C# code sample below).

    // Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"

    Object ConnMgr = new Object();

    ConnMgr = (Object)(Dts.Connections["HTTP Connection Manager"].AcquireConnection(null) as Object);

    // Create a new HTTP client connection

    HttpClientConnection objHttpConnection = new HttpClientConnection(ConnMgr);

    // Save the file to a local path.

    string strFullFilePath = Dts.Variables["$Project::InboundDataFilesPath"].Value + @"\Datafile.txt";

    objHttpConnection.DownloadFile(strFullFilePath, true);

    +1

    Nicer and cleaner IMHO

    +1000

    Why go so far out of your way to create something that needs all this extra access and another runtime? Keeping your code in only SSIS structures makes it portable and re-usable.

    Doing this it is certain there will be issues with future executions.

  • David Kirzner

    SSC Enthusiast

    Points: 119

    Thanks much for your so promptly delivered information !

    This needs to be an unattended transfer of documents posted on a website,

    wherein everyday, documents posted since the last download need to be downloaded.

    It would need to read either timestamp column values listed in a displayed grid or as part of the document name to determine which documents need to be downloaded,

    since the download last occurred.

    Is the above possible using additional Powershell scripting ?

  • Daniel Calbimonte

    SSCarpal Tunnel

    Points: 4757

    A new article about downloading files with the SSIS script task will be published this month.

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

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