June 28, 2018 at 7:17 am
I'm developing an SSIS task that I can use to import to a table SQL Audit files. I've got the task working the way I want it to, but there's one niggling little detail I'd like to resolve, if possible.
I have a ForEach Loop, which has a File System task, which moves the audit files from the "active" audit location to an import location. The move works, and, as I expected, it fails to move the current active audit file. But, this causes it to report an error (again, expected,) which I'd like to try to not report as an error.
If it's not possible (I won't be surprised if it's not) I can live with it reporting as an error.
But I'd really rather not.
Possibly I could get the current name of the active Audit file, and exclude it from the move?
June 28, 2018 at 8:17 am
I did this exact thing not so long ago and I did a script task to check if the file is locked before processing. I used an example I found online it only took a few lines of code. I think I used this example http://agilebi.com/jwelch/2008/05/13/see-if-a-file-is-locked/
June 28, 2018 at 8:20 am
jasona.work - Thursday, June 28, 2018 7:17 AMI'm developing an SSIS task that I can use to import to a table SQL Audit files. I've got the task working the way I want it to, but there's one niggling little detail I'd like to resolve, if possible.
I have a ForEach Loop, which has a File System task, which moves the audit files from the "active" audit location to an import location. The move works, and, as I expected, it fails to move the current active audit file. But, this causes it to report an error (again, expected,) which I'd like to try to not report as an error.If it's not possible (I won't be surprised if it's not) I can live with it reporting as an error.
But I'd really rather not.Possibly I could get the current name of the active Audit file, and exclude it from the move?
You would have to be able to in some way identify which of the files is the active one. Is there any consistent and repeatable information that would be knowable in advance, such as the filename having the current date as part of the filename in a well-known format? Or anything else that you could acquire easily and NOT cause an error?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 28, 2018 at 8:48 am
sgmunson - Thursday, June 28, 2018 8:20 AMjasona.work - Thursday, June 28, 2018 7:17 AMI'm developing an SSIS task that I can use to import to a table SQL Audit files. I've got the task working the way I want it to, but there's one niggling little detail I'd like to resolve, if possible.
I have a ForEach Loop, which has a File System task, which moves the audit files from the "active" audit location to an import location. The move works, and, as I expected, it fails to move the current active audit file. But, this causes it to report an error (again, expected,) which I'd like to try to not report as an error.If it's not possible (I won't be surprised if it's not) I can live with it reporting as an error.
But I'd really rather not.Possibly I could get the current name of the active Audit file, and exclude it from the move?
You would have to be able to in some way identify which of the files is the active one. Is there any consistent and repeatable information that would be knowable in advance, such as the filename having the current date as part of the filename in a well-known format? Or anything else that you could acquire easily and NOT cause an error?
Actually, I can query sys.dm_server_audit_status to get the current audit file. The trick is somehow getting the File System task to "skip" this file. Right now the For Each Loop goes through every file in the source folder and passes them to the File System task to move them.
Hmm. I wonder if I can grab the current filename to a variable, check it in an Expression Task, and if it doesn't match, run the file system task...
Time to keep messing with this, I think this might work.
June 28, 2018 at 8:51 am
jasona.work - Thursday, June 28, 2018 8:48 AMsgmunson - Thursday, June 28, 2018 8:20 AMjasona.work - Thursday, June 28, 2018 7:17 AMI'm developing an SSIS task that I can use to import to a table SQL Audit files. I've got the task working the way I want it to, but there's one niggling little detail I'd like to resolve, if possible.
I have a ForEach Loop, which has a File System task, which moves the audit files from the "active" audit location to an import location. The move works, and, as I expected, it fails to move the current active audit file. But, this causes it to report an error (again, expected,) which I'd like to try to not report as an error.If it's not possible (I won't be surprised if it's not) I can live with it reporting as an error.
But I'd really rather not.Possibly I could get the current name of the active Audit file, and exclude it from the move?
You would have to be able to in some way identify which of the files is the active one. Is there any consistent and repeatable information that would be knowable in advance, such as the filename having the current date as part of the filename in a well-known format? Or anything else that you could acquire easily and NOT cause an error?
Actually, I can query sys.dm_server_audit_status to get the current audit file. The trick is somehow getting the File System task to "skip" this file. Right now the For Each Loop goes through every file in the source folder and passes them to the File System task to move them.
Hmm. I wonder if I can grab the current filename to a variable, check it in an Expression Task, and if it doesn't match, run the file system task...
Time to keep messing with this, I think this might work.
And that did the trick!
Thanks for giving me a nudge in the right direction!
June 28, 2018 at 10:44 am
jasona.work - Thursday, June 28, 2018 8:51 AMjasona.work - Thursday, June 28, 2018 8:48 AMsgmunson - Thursday, June 28, 2018 8:20 AMjasona.work - Thursday, June 28, 2018 7:17 AMI'm developing an SSIS task that I can use to import to a table SQL Audit files. I've got the task working the way I want it to, but there's one niggling little detail I'd like to resolve, if possible.
I have a ForEach Loop, which has a File System task, which moves the audit files from the "active" audit location to an import location. The move works, and, as I expected, it fails to move the current active audit file. But, this causes it to report an error (again, expected,) which I'd like to try to not report as an error.If it's not possible (I won't be surprised if it's not) I can live with it reporting as an error.
But I'd really rather not.Possibly I could get the current name of the active Audit file, and exclude it from the move?
You would have to be able to in some way identify which of the files is the active one. Is there any consistent and repeatable information that would be knowable in advance, such as the filename having the current date as part of the filename in a well-known format? Or anything else that you could acquire easily and NOT cause an error?
Actually, I can query sys.dm_server_audit_status to get the current audit file. The trick is somehow getting the File System task to "skip" this file. Right now the For Each Loop goes through every file in the source folder and passes them to the File System task to move them.
Hmm. I wonder if I can grab the current filename to a variable, check it in an Expression Task, and if it doesn't match, run the file system task...
Time to keep messing with this, I think this might work.And that did the trick!
Thanks for giving me a nudge in the right direction!
Glad I could help. Sometimes all you need is the nudge... other times it's just another pair of eyes 'cause one is too deep in it to see outside the box.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply