June 27, 2017 at 6:00 am
This is for SSIS 2012.
I have a bunch of files in a directory that all start the same way (MyFile_Date_File1, MyFile_Date_File2, etc.) that I want to delete from a directory. The problem is there are other files in the directory that can't be deleted so using Delete Directory Content is out (unless I am misunderstanding how this option is used). I really really really don't want to build a loop container around this task if I don't have to.
Is there a way to set up a variable in this task to delete all files that start with MyFile?
FYI: I've never ever gotten variables to work correctly with the File System Task. They always fail and I can never figure out why. Even expressions seem to fail. So any advice on getting them to work would be appreciated.
June 27, 2017 at 6:04 am
Brandie Tarvin - Tuesday, June 27, 2017 6:00 AMThis is for SSIS 2012.
I have a bunch of files in a directory that all start the same way (MyFile_Date_File1, MyFile_Date_File2, etc.) that I want to delete from a directory. The problem is there are other files in the directory that can't be deleted so using Delete Directory Content is out (unless I am misunderstanding how this option is used). I really really really don't want to build a loop container around this task if I don't have to.
Is there a way to set up a variable in this task to delete all files that start with MyFile?
FYI: I've never ever gotten variables to work correctly with the File System Task. They always fail and I can never figure out why. Even expressions seem to fail. So any advice on getting them to work would be appreciated.
Are you happy to add a script task & write a few lines of C# to do this?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 27, 2017 at 7:54 am
I'm looking for an easier way to get out of this. Something that doesn't actually require a lot of code or multiple tasks / work arounds.
Bleargh. Why does Microsoft have to make things complicated?
June 27, 2017 at 9:07 am
Brandie Tarvin - Tuesday, June 27, 2017 7:54 AMI'm looking for an easier way to get out of this. Something that doesn't actually require a lot of code or multiple tasks / work arounds.Bleargh. Why does Microsoft have to make things complicated?
Do you consider 5 lines of code 'a lot'?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 27, 2017 at 9:26 am
if you want to avoid a script task,i would think you could use foreach loop pointing to the directory, with a filter like "MyFile_*.txt" for the pattern identify the records, and then you can just add a filesystem task to delete a file based on the parameter(ie User::CurrentFileName)
Lowell
June 27, 2017 at 9:31 am
Lowell - Tuesday, June 27, 2017 9:26 AMLowell
Is that Big Lowell or Little Lowell? 😀
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 27, 2017 at 11:33 am
Phil Parkin - Tuesday, June 27, 2017 9:07 AMBrandie Tarvin - Tuesday, June 27, 2017 7:54 AMI'm looking for an easier way to get out of this. Something that doesn't actually require a lot of code or multiple tasks / work arounds.Bleargh. Why does Microsoft have to make things complicated?
Do you consider 5 lines of code 'a lot'?
OK, 7 lines of code. But easily reduced to 5 by hardcoding the sourceFolder and deletePattern variables straight into Directory.GetFiles().
string sourceFolder = @"c:\temp";
string deletePattern = "myFile_*.txt";
string[] files = Directory.GetFiles(sourceFolder, deletePattern);
foreach (string f in files)
{
File.Delete(f);
}
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 29, 2017 at 11:40 am
Lowell, I was trying to avoid the Loop. I just wanted a single task. But it seems like either I have to do a script task with several lines of code (instead of a single connection and a wild card) or a Loop. I went with the Loop since I already new it but I was really really hoping I could make just the File System Task work without other stuff.
June 29, 2017 at 1:57 pm
Could do it outside SSIS as well (if that is an option).
If it is required to do it from SSIS, I think either looping, or if you have xp_cmdshell enabled, you could use a TSQL task to do it.
But if you can do it outside of SSIS, you could look at a bat file or powershell. If you did powershell, you should be able to do it from a SQL job.
I know you said it was for SSIS 2012, but just wanted to throw some other options out there as well that still fit inside the SQL realm.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 29, 2017 at 3:47 pm
I think everyone on this thread knows that I don't know SSIS. That said, can you fire a DOS command? If so, this ought to do it.
del MyFile_*.txt
If not, just say so and I'll shut up about SSIS. Who knows, I might actually learn something about it. 😉
June 30, 2017 at 5:15 am
Ed Wagner - Thursday, June 29, 2017 3:47 PMI think everyone on this thread knows that I don't know SSIS. That said, can you fire a DOS command? If so, this ought to do it.
del MyFile_*.txt
If not, just say so and I'll shut up about SSIS. Who knows, I might actually learn something about it. 😉
DOS command works if xp_cmdshell is enabled (It would be an EXECUTE SQL Task). Corporate is now trying to bring down the hammer on using this (security vulnerabilities, etc.) even though my department only uses it for DBA maintenance tasks and the occasional SSIS package. So, yeah, I thought about it, but I need to find out what's going on with our current exception process on this particular issue before I use it.
EDIT: I was trying to figure out if I could use a DOS like expression in the File System Task, but I couldn't figure it out. Hence the reason I started this thread. I was hoping someone would be able to give me a clue on that.
June 30, 2017 at 5:43 am
Brandie Tarvin - Friday, June 30, 2017 5:15 AMEd Wagner - Thursday, June 29, 2017 3:47 PMI think everyone on this thread knows that I don't know SSIS. That said, can you fire a DOS command? If so, this ought to do it.
del MyFile_*.txt
If not, just say so and I'll shut up about SSIS. Who knows, I might actually learn something about it. 😉DOS command works if xp_cmdshell is enabled (It would be an EXECUTE SQL Task). Corporate is now trying to bring down the hammer on using this (security vulnerabilities, etc.) even though my department only uses it for DBA maintenance tasks and the occasional SSIS package. So, yeah, I thought about it, but I need to find out what's going on with our current exception process on this particular issue before I use it.
EDIT: I was trying to figure out if I could use a DOS like expression in the File System Task, but I couldn't figure it out. Hence the reason I started this thread. I was hoping someone would be able to give me a clue on that.
A more direct method for executing DOS commands would be to use the Execute Process task to run a batch file. No need to make a call to the SQL Server engine & therefore no need for xp_cmdshell to be enabled.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply