December 15, 2008 at 3:37 pm
Hi all. If yo've read my previous post, you'll know that I'm in a situation where I need to check for the existence of an .ldb file and if that file doesn't exist, then copy an .mdb file from a static source location to a dynamic destination. I am using SSIS to do this as the file task functionality is pretty darn good.
We are basically checking to see if users have an Access app open which is evident when the .ldb file exists in their directory. If they don't have it open, we give them a fresh copy.
Anyway, I have found two ways to check for the .ldb existence from within SSIS: 1) through a command line in a Stored Procedure. 2) Using a .Net script task.
I am waiting on our Network guys to install a hotfix to allow the .net script task (#2 above) to execute properly (thanks MS) on one of our servers which requires a subsequent reboot, so for the moment I'm going with the SP check (#1 above) which is a bit more complex.
The SP checks for file existence and returns a value of 0 if the .ldb file is found and some other integer, usually 1, if it is not found. I realize this seems a bit backwards, but I have reasons for that, so just go with it.
Anyway, I can pass the return value to a variable in SSIS, I think, but I'm then not sure how to check the value and either flow on to the copy function if the .ldb file isn't present, or loop around to the next user without doing this if it is present.
Here's a summary of what I've got planned:
1) Call up path of user directories to check for the .ldb
2) Jump into ForEach Loop so we can check each path returned in above
3) Return value from SP indicating whether or not the .ldb was found
4) Make decision and flow to the file copy task where .ldb isn't found, or skip the task, looping around to check the next user where the .ldb is found.
It's #4 I'm having a problem with. Any help on how to do that is appreciated. I know it's possible, I'm just not quite sure how to configure things to get this happening.
December 18, 2008 at 7:23 am
hi,
The solution is in the constraint.
After that you assign a value to the variable you need
to check it.
edit the first constraint and change the following parameters :
1) Evaluation operation to : expression.
2) Expression : @[User::FileCount] > 0.
if 2 is evaluate to true then continue the flow.
edit the second constraint and change the following parameters :
1) Evaluation operation to : expression.
2) Expression : @[User::FileCount] == 0.
if 2 is evaluate to false then skip task or send an error or what ever.
I hope that this will help you.
December 18, 2008 at 2:50 pm
Thanks Schmuel! I figured it would work something like that. Wasn't quite sure how to reference the variable, or whether I had to pass that back to a variable in the variable window, or could just access it as a return value.
I've just found out that I can't use the SQL Script I wanted to in order to do the comparison, so I'm going with the .Net Script file check instead. This will either return a value of success or failure. Do you know the syntax for referring to the success/failure variable in a .Net Script task from the contstraints that will follow it?
I hope that makes sense!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply