SSIS 2008 - Download File Base on Last Modified Date Property

  • Hi all,

    I have a table in my developing database that has URLs. Each URL is a link to a source and each link's data set is updated monthly but on different dates. How can I use SSIS to go through each link in the table, check it's last date modified, and then download the file if today is a newly modified date? I know that I need to use the HttpWebResponse.LastModified property but I am unsure about how to put it all together. Any help is truly appreciated.

    Regards:
    Mordred
    Keep on Coding in the Free World

  • Here is an algorithm for what I want:

    For each link in tblTables_And_Links

    variable DownloadURL = link

    open httpconnection(DownloadURL)

    TodaysDate = now

    If TodaysDate > LastModified then

    execute DownloadData

    else goto next link

    next link

    Does this make sense?

    Regards:
    Mordred
    Keep on Coding in the Free World

  • Alright so I have an Execute SQL Task that connects to a Foreach Loop container. The SQL Task queries a view that shows all the links but I cannot get it to iterate through each row from the table in the database. I know that I am messing up with my properties somewhere but after hours of reading, tweaking, reading, and more tweaking, I am not much farther ahead. It's getting there though.

    Regards:
    Mordred
    Keep on Coding in the Free World

  • Hi all, please inform me as to why the following parameters for an Execute SQL Task and Foreach Loop Container are not right. This format is almost identical to a book I am reading but it is not working! The variables themselves are scoped to the package and everything else here seems correct but being new to SSIS I am not understanding the errors which are:

    Error: 0xC002410D at Foreach Loop Container: The enumerator failed to retrieve element at index "1".

    Error: 0xC001C012 at Foreach Loop Container: ForEach Variable Mapping number 1 to variable "User::sDbName" cannot be applied.

    I know that the SQL statement outputs all of the rows that I want because I built it and the SQL Task completes successfully but the Foreach Loop stops. There are only 26 rows from this table for now so I know that the enumerator should have been able to retrieve the element at index 1 but for some reason it does not. Any ideas? Please?

    Execute SQL Task

    General:

    ResultSet = Full result set

    SQL Statement:

    ConnectionType = OLE DB

    Connection = MICHAEL-PC.EconAnalysis

    SQLSourceType = Direct input

    SQLStatement = SELECT Table_Links FROM dbo.tblLinks

    BypassPrepare = True

    No Parameter Mapping

    Result Set:

    Result Name = 0

    Variable Name = flObj (of Object datatype)

    No Expressions

    Foreach Loop Container

    Collection:

    Enumerator = Foreach ADO Enumerator

    ADO object source variable = User::flObj

    Rows in first table selected

    Variable Mappings:

    Variable = User::sDbName

    Index = 1

    Regards:
    Mordred
    Keep on Coding in the Free World

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

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