April 12, 2016 at 1:08 pm
Hello,
I am trying to write an expression in SSIS to see if the file is older than 7 days then delete it.
The problem is my file names are not consistent here. Like abc_vg-20161204.txt or bhagvskjhads_ghasgh_sahsa-20161104.txt
So whats consistent is every file name has "-" and after that only the datetimestamp.
I am taking baby steps to write this.
DECLARE @Filename_to_be_deleted varchar(100)
DECLARE @Filemaxage int
SET @Filename_to_be_deleted = 'abc_vg-20161204.txt'
SET @Filemaxage = 7
SELECT CHARINDEX('-',@Filename_to_be_deleted)
SELECT SUBSTRING(CHARINDEX('-',@Filename_to_be_deleted),6,2)
SELECT DATEDIFF('dd',(DT_Date)(SUBSTRING(@Filename_to_be_deleted,6,2) + "-" + SUBSTRING(@Filename_to_be_deleted,8,2) + "-" + SUBSTRING(@Filename_to_be_deleted,10,4)),GETDATE()) > @Filemaxage
Now with charindex I can find the position of "-" but then how to retrieve the datepart from file name and compare?
Please let me know thanks.
April 12, 2016 at 1:17 pm
Quick thought, look at Ola Hallengren's maintenance scripts, he has a very good clean up process there
😎
April 12, 2016 at 1:23 pm
Would it work with the real creation time of the file by using a script?
This code is untested, but it could give you an idea.
Option Strict On
Imports System
Imports System.IO
Imports System.Data
Imports Microsoft.SqlServer.Dts.Runtime
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Dim input_file As New FileInfo(Dts.Variables("FilePath").Value.ToString)
Try
If input_file.CreationTime < System.DateTime.Now.AddDays(-7) Then
input_file.Delete()
Catch ex As Exception
End If
End Try
End Sub
End Class
P.S. My VB coding is horrible.
April 12, 2016 at 1:25 pm
Something like this may help:
DECLARE @Filename_to_be_deleted VARCHAR(100);
DECLARE @FileDate DATE;
SET @Filename_to_be_deleted = 'abc_vg-20161204.txt';
SET @FileDate = SUBSTRING(@Filename_to_be_deleted,
PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',@Filename_to_be_deleted),8);
SELECT @FileDate;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 12, 2016 at 1:27 pm
if you look at it the other way, are the last eight characters of the file name always a correctly formatted datestring?
so it-doesn-t-matter-about-dashes04122016.txt? can get me the right values with an expresion like this?
/*--Results
(No column name) val
20160412 it-doesn-t-matter-about-dashes20160412.txt
20161204 abc_vg-20161204.txt
20161104 bhagvskjhads_ghasgh_sahsa-20161104.txt
*/
;WITH MyCTE([val])
AS
(
SELECT 'it-doesn-t-matter-about-dashes20160412.txt' UNION ALL
SELECT 'abc_vg-20161204.txt' UNION ALL
SELECT 'bhagvskjhads_ghasgh_sahsa-20161104.txt '
)
SELECT REVERSE(SUBSTRING(REVERSE(val),CHARINDEX('.',REVERSE(val))+1,8)),val
FROM MyCTE;
Lowell
April 12, 2016 at 1:52 pm
Thanks everyone. I am only using T-SQL here and when I try this code inside my SSIS it still gives me error saying Error parsing the code.The single quotation mark at line number "1", character number "84", was not expected.
DATEDIFF("dd",(DT_DATE) (RIGHT((SUBSTRING(@[User::Filename_to_be_deleted],PATINDEX(''%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'',@[User::Filename_to_be_deleted]),8)), 4) + "-" + SUBSTRING((SUBSTRING(@[User::Filename_to_be_deleted],PATINDEX(''%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'',@[User::Filename_to_be_deleted]),8)), 1,2)
+ "-" + RIGHT(SUBSTRING((SUBSTRING(@[User::Filename_to_be_deleted],PATINDEX(''%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'',@[User::Filename_to_be_deleted]),8)),1,4), 2))
April 12, 2016 at 2:37 pm
dallas13 (4/12/2016)
Thanks everyone. I am only using T-SQL here and when I try this code inside my SSIS it still gives me error saying Error parsing the code.The single quotation mark at line number "1", character number "84", was not expected.
DATEDIFF("dd",(DT_DATE) (RIGHT((SUBSTRING(@[User::Filename_to_be_deleted],PATINDEX(''%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'',@[User::Filename_to_be_deleted]),8)), 4) + "-" + SUBSTRING((SUBSTRING(@[User::Filename_to_be_deleted],PATINDEX(''%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'',@[User::Filename_to_be_deleted]),8)), 1,2)+ "-" + RIGHT(SUBSTRING((SUBSTRING(@[User::Filename_to_be_deleted],PATINDEX(''%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'',@[User::Filename_to_be_deleted]),8)),1,4), 2))
Apologies, I gave you T-SQL syntax, not SSIS syntax. The following is truly ugly, but it does get you a date, in the end, using SSIS expressions.
If you create a string variable in SSIS (let's call it Test) and enter the following expression:
(left(token(@[User::Filename_to_be_deleted],"-",tokencount(@[User::Filename_to_be_deleted],"-")),8))
You should find that it will contain your date, as a string, in YYYYMMDD format.
Create another string variable, Test2, with the following expression:
left( @[User::Test],4) + "-" + SUBSTRING( @[User::Test] , 5, 2 ) + "-" + right( @[User::Test],2)
Create a DateTime variable, Test3, with the following expression:
(DT_DATE) @[User::Test2]
Now you can use Test3 in DateTime calculations.
I'd do this in a script task to avoid the horrid expressions.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 12, 2016 at 3:27 pm
Thanks.
I did similar thing using Findstring as patindex and charindex is not working in sql 2014
SUBSTRING(@[User::Filename_to_be_deleted], FINDSTRING(@[User::Filename_to_be_deleted], "-", 1)+1, 8)
April 13, 2016 at 6:08 am
What's wrong on using the script task code I gave you?
In a single task you'd be validating and deleting. If you need to get the date by the name, that's possible too.
April 13, 2016 at 6:33 am
Sure I dont but not a c# or VB developer so hard to troubleshoot if something goes wrong ther. but thanks all.
April 13, 2016 at 6:45 am
dallas13 (4/12/2016)
Thanks.I did similar thing using Findstring as patindex and charindex is not working in sql 2014
SUBSTRING(@[User::Filename_to_be_deleted], FINDSTRING(@[User::Filename_to_be_deleted], "-", 1)+1, 8)
PATINDEX and CHARINDEX both work just fine in T-SQL 2014. But they are not part of the SSIS expression language, which is distinct from T-SQL.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 13, 2016 at 6:53 am
dallas13 (4/13/2016)
Sure I dont but not a c# or VB developer so hard to troubleshoot if something goes wrong ther. but thanks all.
I'm not a C# or VB developer either, but the script is quite simple. You just need to focus on the Public Sub Main part and the Imports section. The rest is generated automatically.
If something goes wrong, you can come back to this forum or use Google/Bing. And you'll start to build a new skill.
April 13, 2016 at 7:02 am
<redacted>
April 13, 2016 at 7:56 am
Manic Star (4/13/2016)
The easiest way to do this would be to point a For-each container at the directory containing the files, set the mask to either be everything, or filter it if you want too.Then pass each file name off to a script task that could access the file system object libraries. The script could test the age of the file pretty, and delete it if its old enough.
You would have to learn how to pass parameters to an SSIS vb script task, but there are plenty of examples online to help with that.
This example code snip tests the age of a file using the file's last modified data the FSO libraries. You could modify it fairly easily to look for a file > 60 days old.
Public Sub Main()
Dim fs As Object
Dim f, s As Object
On Error Resume Next
fs = CreateObject("Scripting.FileSystemObject")
f = fs.GetFile("\\file_share1\data\logfiles\Thirdpartyoutput.txt")
s = f.OpenAsTextStream(1).Read(100)
If Err.Number <> 0 Then
Dts.TaskResult = ScriptResults.Failure
Exit Sub
Else
If FormatDateTime(f.DateLastModified, vbShortDate) <> FormatDateTime(Now(), vbShortDate) Then
Dts.TaskResult = ScriptResults.Failure
Exit Sub
End If
End If
Dts.TaskResult = ScriptResults.Success
End Sub
Then pass the PASS results to a standard File System Task to delete the file.
This looks like you have just cut & pasted some code which is semi-relevant to what the OP has asked for. However, as the OP has already stated that they are not familiar with coding, I think you risk confusing them.
If you're going to use a script task, there really is no need to use a foreach loop as well. Just do your looping inside the script task. But ... the OP said nothing about looping anyway, so that may not be a requirement.
Using a file's DateModified attribute is different from what the OP has asked for. The OP has no need to read the file contents either.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 13, 2016 at 8:50 am
Eirikur Eiriksson (4/12/2016)
Quick thought, look at Ola Hallengren's maintenance scripts, he has a very good clean up process there😎
Isn't that just for backup files?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply