Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS 2008 - Download File Base on Last Modified Date Property


SSIS 2008 - Download File Base on Last Modified Date Property

Author
Message
Mordred
Mordred
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 495
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
Mordred
Mordred
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 495
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
Mordred
Mordred
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 495
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
Mordred
Mordred
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 495
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search