Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS 2008 - Download File Base on Last Modified Date Property Expand / Collapse
Author
Message
Posted Monday, July 30, 2012 9:55 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 08, 2013 9:25 AM
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
Post #1337358
Posted Monday, July 30, 2012 12:14 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 08, 2013 9:25 AM
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
Post #1337459
Posted Monday, July 30, 2012 10:23 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 08, 2013 9:25 AM
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
Post #1337655
Posted Tuesday, July 31, 2012 10:14 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 08, 2013 9:25 AM
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
Post #1338292
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse