dave-dj (10/25/2012)
excellent and comprehensive article.I only have a couple of questions..
first - if you where required to process multiple files per day, would you adapt this process, or would you use a process similiar to the others whereby the package is run every 5 minutes (and subsequently loaded and unloaded from memory)?
If it were part of the requirement to process multiple files per day then you have some options. In keeping with the idea that we want our SSIS package to run continuosly, and not have to stop and start it every so often, I would look into putting the entire set of tasks into a For Loop with a condition of 'where 1=1'. This would effectively bring us back to the 'watcher' after processing a file. If there was a possibility for a file to arrive during the processing of a previous file, then you could consider setting WatcherInputFindExistingFiles to True to ensure that file were processed.
secondly - I've not really covered c# before but it would appear to be the prefered language SQL2005 onwards. So could you recommend any useful resource where I could start to bring my skill in this area up to date
SSIS 2005 only supports VB.net. Microsoft heard the calls from their customers though and added support for C# to SSIS 2008, which I was very happy about since my background was in C# and converting language skills back and forth was a nuisance.
Here is a great set of resources I wish were around when I was getting started that will help you get started with C# as a programming language, using Visual Studio. Working with C# as a stand-alone language in Visual Studio is slightly different than working with it as a scripting language in SSIS, however the generic C# language skills learned from these lessons will immediately translate to working with C# in SSIS.
Channel 9 > C# Fundamentals: Development for Absolute Beginners
Thanks for reading, and posting!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato