Post reply

Image Urls in XML File Source

  • January 12, 2011 at 2:48 pm

    #230290

    I currently have an Xml document that I am importing into multiple tables in my database. In this xml file are urls that lead to images that I need to download to our file system. Is there a way using SSIS to actually execute the url and grab the image and download into the appropriate directory?

    Thanks in advance for any help.

  • January 14, 2011 at 1:52 pm

    #1273786

    pamozer (1/12/2011)

    I currently have an Xml document that I am importing into multiple tables in my database. In this xml file are urls that lead to images that I need to download to our file system. Is there a way using SSIS to actually execute the url and grab the image and download into the appropriate directory?

    Thanks in advance for any help.

    Yes, you can do it using script and the standard .NET WebClient class. Check this script for ideas what is the required functionality.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • January 14, 2011 at 2:35 pm

    #1273809

    Thank You for your reply. I will take a look at the script. My requirements are pretty simple I am just not familiar with writing .net. I have an xml file that I am importing into tables. One table has the Urls that direct you to an image. I want to grab that image and save it to a director on our file server. We need to manipulate the images so we can't just use the url.

  • January 18, 2011 at 12:34 pm

    #1274937

    I was able to get the iterating through the files working and I think I can use the Http connection for each url by setting the properties and using an expression. The problem is my script task.

    Imports System

    Imports System.IO

    Imports System.Text

    Imports System.Windows.Forms

    Imports Microsoft.SqlServer.Dts.Runtime

    Public Class ScriptMain

    Public Sub Main()

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

    Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing)

    ' Create a new HTTP client connection

    Dim connection As New HttpClientConnection(nativeObject)

    ' Download the file #1

    ' Save the file from the connection manager to the local path specified

    Dim filename As String = ("C:\" & CStr(Dts.Variables("@User:ImageName").Value))

    connection.DownloadFile("C:\" & CStr(Dts.Variables("@User:ImageName").Value), True)

    ' Confirm file is there

    If File.Exists(filename) Then

    MessageBox.Show(String.Format("File {0} has been downloaded.", filename))

    End If

    ' Download the file #2

    ' Read the text file straight into memory

    Dim buffer As Byte() = connection.DownloadData()

    Dim data As String = Encoding.ASCII.GetString(buffer)

    ' Display the file contents

    MessageBox.Show(data)

    Dts.TaskResult = Dts.Results.Success

    End Sub

    End Class

    This is the error I get:

    Error: Failed to lock variable "osc_offer_combos" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

    The value above is the value of the imageName Variable. Any thoughts?

  • January 18, 2011 at 4:58 pm

    #1275053

    I was actually able to fix the problem. I had to recreate the script task. And the errors went away. A bug I am guessing.

Viewing 5 posts - 1 through 4 (of 4 total)

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