December 8, 2008 at 5:40 pm
Hi all.
Every night, we copy a fresh copy of an Access DB (.mdb file) over to each of our users (it keeps the Access client file from corrupting over time :P).
This worked very well from a simple batch file before we had 24/7 service in our office, meaning that users are now sometimes logged onto the .mdb in the middle of the night when we try to copy a fresh one. Obivously if you copy while in use, it corrupts the .mdb.
Our solution is to move this to an SSIS package, as it is easy enough to loop through the user folders and copy them a new copy. However, if they are currently logged into the .mdb, we want to find out prior to performing the copy, and we can then flag this user as not having been given a fresh copy, and not copy the file over, thus not corrupting the one they're currently in.
If a user is currently using an .mdb, then an .ldb file appears in the same directory for the term of their open session.
So, prior to doing the copy of the .mdb with SSIS, I want to check for the existence of the .ldb file of the same name in the same directory.
Does anyone know if there is a way to check for file existence from within SSIS? Given that it's easy enough to do moves and copies, I thought there might be, but I can't find anything specific to this.
December 8, 2008 at 6:41 pm
IMO, the best way is to use a script task which will allow you to check for the file, if it exists fail and do one thing in your flow, if it doesn't return success and process the .mdb.
You can also look into the file properties task created by the guys at PragmaticWorks. It's free.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 8, 2008 at 7:50 pm
Sorry, what do you mean by a "script task" exactly?
December 8, 2008 at 9:10 pm
sharon.bender (12/8/2008)
Sorry, what do you mean by a "script task" exactly?
A Script Task is one one of the Control Flow items in the toolbox in BIDS. It allows you to write .NET scripts (VB only in 2005). With this you can use much of the .NET Framework including FIle access. You can check for file existence like this:
File.Exists("C:\Temp\test.txt")
YOu can use a variable to hold the boolean and then use a Expression and Constraint as your precedence constraint between tasks.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 8, 2008 at 9:20 pm
I'm assuming if it's .Net, that we wouldn't be able to kick it off periodically, like once/hour, from a job.
The reason I want to do this from the SQL Server is so that we can create a job to poll for it at regular inervals, rather than something that we have to run manually.
SSIS is powerful and perfect for this in every way, except that I'm unsure how to check for existence of a file through any of its standard commands. As I can kick off a command line, I could probably do it in a dos sort of way, which is probably what I'll end up doing. If I can crack how to do that in SSIS, then this will be a much better solution for us.
December 8, 2008 at 9:27 pm
You can do this in SSIS. That is what I am trying to tell you. You use the Script Task (a component of SSIS) to check for existence. Attached is a package with a script task that checks for file existence and if the file DOES NOT exist continue to a data flow. Very simple. Unless you have northwind you will need to change the database connection.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 8, 2008 at 9:33 pm
Thanks a million Jack. I'll have a look.
A colleague and I were just talking about this and he showed me a script where he's returning records from an sp_cmdshell.exe call from within an SP. When the file isn't found, one of the returned rows is "File Not Found" so I can always take this approach and loop through the recordset returned for the existence of File Not Found.
I suspect the example you gave me will do similar, but I'll have a look. Always more than one way to skin a cat.
At least I know it can be done. Now just to find the best way.
December 9, 2008 at 9:15 am
Hi Sharon,
I don't know if you are having issues with this still or not but if so it may be worth checking out the file watcher task. It is available free from Konesans install and add it to the toolbox and drop it on your package - it is designed for excatly what you are trying to do.
Chris
December 9, 2008 at 12:43 pm
Hi Chris.
Thanks for the tip.
Yes, I haven't started writing the proc to do this yet, so there's still time to throw new ideas at it, and this one sounds good. I'll look them up on the web and see if I can get the tool you recommend.
December 9, 2008 at 12:48 pm
Hi Chris.
Actually, the FileWatcher looks good, but probably not for what I'm trying to do here.
The .ldb file I referred to has to be ABSENT from the directory in order to prompt a copy, and this searches for a file to be loaded and complete.
I have about 100 users to check files on, and it's a very small file which will either be present or absent at any given time, and when absent, then I need to perform an action.
So I don't think I can probably use this in this particular instance, but I'll toy with it to make sure!
We've found a way to do it from a command line call, and this will suffice if I can't find a better way.
December 10, 2008 at 2:51 am
HI Sharon
it should work if you:
configure the filewatcher to look for the *.ldb file (dir provided by a variable)
set the timeout to be 1 (minute)
timeout warning as false (this will effectivley raise an erorr but also allow you to handle it)
from the filewatcher you will branch the ssis in 2 directions. If the file is found and not found. For the file found branch connect the filewatcher to the approppriate tasks with a success constraint arrow (green). For the file not found branch connect the filewatcher task to the appropriate task via a failure constraint arrow (red - drag a green arrow, double click and change it to failure)
put all tasks in a for each loop and loop over all the dirs you need carrying out these actions.
Chris
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply