Post reply

How to download a file from internet using SSIS

  • November 30, 2016 at 9:54 pm

    #315385

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

  • November 30, 2016 at 11:09 pm

    #1915325

    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

  • December 1, 2016 at 12:10 am

    #1915326

    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.

  • December 1, 2016 at 12:17 am

    #1915327

    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

  • December 1, 2016 at 12:31 am

    #1915328

    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.

  • December 1, 2016 at 2:38 am

    #1915338

    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.

  • December 1, 2016 at 3:42 am

    #1915346

    Hi,

    Thanks for information.

    Anisha

  • December 1, 2016 at 3:43 am

    #1915347

    Did you tried to import the module?

    import-module bitstransfer

  • December 1, 2016 at 3:55 am

    #1915349

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

  • December 1, 2016 at 6:39 am

    #1915385

    Thanks for the article and extra info.

  • December 1, 2016 at 6:50 am

    #1915393

    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);

  • December 1, 2016 at 7:37 am

    #1915411

    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

  • December 1, 2016 at 8:00 am

    #1915426

    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.

  • December 1, 2016 at 10:05 am

    #1915491

    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 ?

  • December 1, 2016 at 10:28 am

    #1915512

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

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

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