July 19, 2011 at 3:04 pm
Hello,
I have a maintenance plan in SQL Server 2000 that is not deleting files as it should. No problem as this gives me more experience working with SSIS.
However, after writing a script, I find you can't debug a script.
Now, I'm not sure if this is the best way to delete old files in SSIS.
I just have one script task on the control flow.
It is turning red and not working. I've submitted the code that I am using.
I am supply the unc path. I just need it to delete files older than 7 days.
I am working on a SQL Server 2008 R2 server in SQL Server BI studio.
Any help is greatly appreciated.
Thanks.
Tony
(The original code example came from:
http://www.sqlserverspecialists.co.uk/blog/_archives/2008/7/10/3786631.html)
Script:
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld
Dim sDebug As String
'Initilize variable
'******************
sDebug = "Y"
'Server Files
'****************
iDaysOld = CInt(Dts.Variables("User::DaysBack").Value) 'the variable DaysBack can be altered as needed.
MsgBox(iDaysOld)
oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = CStr(Dts.Variables("User::FileFolder").Value) 'the variable FileFolder can be as needed.
MsgBox(sDirectoryPath)
oFolder = oFSO.GetFolder(sDirectoryPath)
oFileCollection = oFolder.Files
'Walk through each file in this folder collection.
'*************************************************
For Each oFile In oFileCollection
MsgBox(oFile.ToString())
If oFile.DateLastModified < (DateTime.Now.AddDays(-iDaysOld)) Then
'If we are debugging, then just show a messagebox. If this is not a debug,
'delete the file.
'**************************************************************************
If sDebug = "Y" Then
MsgBox("File found that needs to be deleted: " + oFile.name, MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "File Deletion")
Else
oFile.Delete(True)
End If
End If
Next
'Clean up
'********
oFSO = Nothing
oFolder = Nothing
oFileCollection = Nothing
oFile = Nothing
'Send success
Dts.TaskResult = ScriptResults.Success
End Sub
Things will work out. Get back up, change some parameters and recode.
July 19, 2011 at 3:45 pm
can you provide error messages. also post how far the script is getting (based on your message boxes). do you have the needed references in your code?
One thing you can do is run a build, this will identify any missing references, on the file menu when you are in the script editor, under Build I believe.
also add more messge boxes to see how far the code is progressing.
Another thing you can do I think, not sure without looking but you can use a for each container and loop through the files then use an expression to check the dates. BUT not sure if the date attribute is available, you could use a script at that point (within the for each) to at least get the date, then use a file system task to delete.
But post your errors and how far the script gets and do a build, that will help.
July 19, 2011 at 5:54 pm
That sure is a lot of code to delete files older than n-days. Here is a one-line PowerShell command that does the work for you. You can execute the command from SSIS using the Execute Process task referencing the PowerShell 2.0 runtime (bundled with 2008 R2 install). Adjust constants to suit...remove the -WhatIf to have it really do the deletion:
ls -Path "E:\Backups\" -Filter "*.bak" |? {$_.LastWriteTime -lt (get-date).AddDays(-7)} | Remove-Item -WhatIf
To also look in all sub-folders:
ls -Path "E:\Backups\" -Filter "*.bak" -Recurse |? {$_.LastWriteTime -lt (get-date).AddDays(-7)} | Remove-Item -WhatIf
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 19, 2011 at 7:44 pm
It has gotten a lot easier with Powershell.
In the past I have used scripts to write FSO in ActiveScript Code (DTS) to search for certain files based on date, then rename them archive and purge.
Does Powershell have all the capability that a script task has with respect to File System Object manipulation of files?
A example would be look at each backup file within a folder with an file extension based on DateTime and performing operations on each file based on criteria such as date created, modified, file size etc?
If this capability is possible does anyone know of any links on how to do this within Powershell as opposed to performing these types of operations within a Script Task?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 19, 2011 at 7:57 pm
Hello,
Thanks for the example. Unfortunately, corporate policy won't allow me to do any powershell coding.
That is why I was trying to do this in SSIS.
Thanks,
Tony
Things will work out. Get back up, change some parameters and recode.
July 19, 2011 at 8:23 pm
WebTechie38 (7/19/2011)
Hello,Thanks for the example. Unfortunately, corporate policy won't allow me to do any powershell coding.
That is why I was trying to do this in SSIS.
Thanks,
Tony
I have done this using the Script Task and using the File System Object and you can loop through the files by using a ForEach Loop Container Object within SSIS.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 19, 2011 at 8:27 pm
WebTechie38 (7/19/2011)
However, after writing a script, I find you can't debug a script.
Now, I'm not sure if this is the best way to delete old files in SSIS.
I just have one script task on the control flow.
It is turning red and not working. I've submitted the code that I am using.
(
You can place breakpoints, etc in your code, etc and you can verify what is happening .
IMHO, to reproduce the problem that you are experiencing and get a quicker response it would be easier if you provided additional information.
I would suggest that in the future that you post SSIS question in the SSIS Forum to get a better response. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 19, 2011 at 8:40 pm
WebTechie38 (7/19/2011)
Hello,Thanks for the example. Unfortunately, corporate policy won't allow me to do any powershell coding.
That is why I was trying to do this in SSIS.
Thanks,
Tony
Booo... 😛 Do they know that PowerShell 2.0 is bundled with SQL Server 2008 R2 installations? You do have to enable script execution though..maybe its a security thing.
Here is a C# example that may only need a minor tweak or two. If you are more comfortable with VB.NET (I favor C# personally) it should be a simple port job for you:
using System.IO;
string[] files = Directory.GetFiles(dirName);
foreach (string file in files)
{
FileInfo fi = new FileInfo(file);
if (fi.LastAccessTime < DateTime.Now.AddMonths(-3))
fi.Delete();
}
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 19, 2011 at 8:50 pm
Well I did look for a SSIS forum for SQL Server 2008.
I may have missed it.
Things will work out. Get back up, change some parameters and recode.
July 19, 2011 at 8:52 pm
I tried putting breakpoints, but they didn't work.
I read somewhere that those don't work in the script editor.
The first msgbox is working to file the days.
But..
oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = CStr(Dts.Variables("User::FileFolder").Value) 'the variable FileFolder can be as needed.
MsgBox(sDirectoryPath)
oFolder = oFSO.GetFolder(sDirectoryPath)
The msgbox to show the sDirectorypath never shows before the box goes red.
Is there supposed to be a flat file connection manager for deleting files with the script task?
Thanks,
Tony
Things will work out. Get back up, change some parameters and recode.
July 19, 2011 at 8:53 pm
WebTechie38 (7/19/2011)
Well I did look for a SSIS forum for SQL Server 2008.I may have missed it.
There isn't one. There's one for 2005 and a generic one for Integration Services under Data Warehouse I think. You're fine. Most of the people that look at the two IS forums also look at General. As long as you declare your environment we can follow along.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 19, 2011 at 8:58 pm
WebTechie38 (7/19/2011)
I tried putting breakpoints, but they didn't work.I read somewhere that those don't work in the script editor.
They work...but there are restrictions.
Is there supposed to be a flat file connection manager for deleting files with the script task?
No need for a connection manager. The FSO stands on its own two feet. ...what's wrong with the code I posted?
Did you setup the variable in the ReadOnlyVariables collection on the Script Task itself? Try the code without variables...get that working. Then introduce the variables.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 19, 2011 at 9:00 pm
WebTechie38 (7/19/2011)
Well I did look for a SSIS forum for SQL Server 2008.I may have missed it.
Opps, Sorry about that...
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 19, 2011 at 9:02 pm
opc.three (7/19/2011)
Booo... 😛 Do they know that PowerShell 2.0 is bundled with SQL Server 2008 R2 installations? You do have to enable script execution though..maybe its a security thing.
Here is a C# example that may only need a minor tweak or two. If you are more comfortable with VB.NET (I favor C# personally) it should be a simple port job for you:
using System.IO;string[] files = Directory.GetFiles(dirName);
foreach (string file in files)
{
FileInfo fi = new FileInfo(file);
if (fi.LastAccessTime < DateTime.Now.AddMonths(-3))
fi.Delete();
}
Thanks for posting the code. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 20, 2011 at 6:26 pm
I am working through this. Yeah.
I tend to use Visual Basic. I haven't learned C# yet.
The code I used was:
iDaysOld = CInt(Dts.Variables("User::DaysBack").Value) 'the variable DaysBack can be altered as needed.
sDirectoryPath = CStr(Dts.Variables("User::FileFolder").Value) 'the variable FileFolder can be as needed.
'Create objects
'*************
oFSO = CreateObject("Scripting.FileSystemObject")
oFolder = oFSO.GetFolder(sDirectoryPath)
oFileCollection = oFolder.Files
For Each oFile In oFileCollection
If oFile.DateLastModified < (DateTime.Now.AddDays(-iDaysOld)) Then
'If we are debugging, then just show a messagebox. If this is not a debug,
'delete the file.
'**************************************************************************
If sDebug = "Y" Then
MsgBox("File found that needs to be deleted: " + oFile.name, MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "File Deletion")
Else
MsgBox("about to delete")
oFile.delete(True)
MsgBox("File deleted: " + oFile.name, MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "File Deletion")
Exit For
End If
End If
Next
It doesn't like oFile.Delete(true). It turns red. I tested with msgbox ofile.name to insure it was seeing the file and the right name is shown.
Tony
Here is the error output.
Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileNotFoundException: Exception from HRESULT: 0x800A0035 (CTL_E_FILENOTFOUND)
at Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)
at ST_5b701785c293406182c7bda7daa5bb75.vbproj.ScriptMain.Main()
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
Task failed: Script Task
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
Things will work out. Get back up, change some parameters and recode.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply