Retry n times if the source file is not available in the specified location

I have a flat source file(.csv) which I am importing to SQL server. Now, if the source file is not available at the specified location, then the SSIS package should retry to execute n times( say 3 times) after certain time interval. The number of retries and the time interval should be configurable.

I have used Flat File connection manager for the source and OLEDB connection manager for the destination.

I am quite new to SSIS. Any help would be highly appreciated.

You can setup retries in SQL Server Agent which can run your SSIS job.. SSIS doesn't have that kind of logic built in.

With that said, this could be scripted in a Script Task though, using the File.Exists member.

Retrieving and storing file timestamp

I have a package that processes a flat file, which is generated by a separate system each weekday around 3:30AM. I would like my package to store the timestamp of the input file and compare it to the timestamp of the next day's file. If they are the same, then the package can exit without reprocessing a particular file again.

So, my first question is whether a package variable can be updated so that its value will persist from one run to the next?

Second, what is the syntax to retrieve the file's timestamp, either directly from the file connection manager or within a script task?

Thanks,

Phil

Philsky wrote:

I have a package that processes a flat file, which is generated by a separate system each weekday around 3:30AM. I would like my package to store the timestamp of the input file and compare it to the timestamp of the next day's file. If they are the same, then the package can exit without reprocessing a particular file again.

So, my first question is whether a package variable can be updated so that its value will persist from one run to the next?

No! That'd be nice wouldn't it. I've requested similar functionality in the past although not formally via the Product Feedback Center. Perhaps you could submit it?

Philsky wrote:

Second, what is the syntax to retrieve the file's timestamp, either directly from the file connection manager or within a script task?

Thanks,

Phil

There's some code here that shows how you can access this information: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

Check lines 7 & 8 of the first code block.

-Jamie