Check if file exist with todays date as file name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Check if file exist with todays date as file name

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-09-09 : 16:44:13

Hi,

I am done with most part of my SSIS package.I need advice & suggestions on this little part.

Every Monday between 9am-12pm,we receive 6 zip files.The zip file's name contains todays date as mmddyyyy format .Example of file name is [ A09082008.zip],[B09082008],[C09082008],[D09082008],[E09082008] & [F09082008].

I need to check and see if there are total of 6 zip files as named above with the current date.If yes,it needs to go ahead and run the SSIS package [this is already in place].If no file exist by 1pm,a mail should be sent out.

Any ideas on how I can do this.

Thank You.

franktellez
Starting Member

3 Posts

Posted - 2008-09-09 : 17:46:11

DECLARE @now DATETIME
SET @now = GETDATE()
ex:
PRINT dbo.FormatDateTime(@now, 'DD/MM/YYYY')

instead of printing save the value to variable
remove "/" using strtran()

then compare filename to variable
if true
execute ssis
if not use sp_sendmail to notify
I would made sure the time is greater then 1:00 pm for example

to compare filename
ex:

if variable = substr(filename,2,8)
then 'execute ssis
else
exec sp_notify

At least I give you the idea.

****************************************

create this function:

CREATE FUNCTION dbo.FormatDateTime
(
@dt DATETIME,
@format VARCHAR(16)
)
RETURNS VARCHAR(64)
AS
BEGIN
DECLARE @dtVC VARCHAR(64)
SELECT @dtVC = CASE @format

WHEN 'LONGDATE' THEN

DATENAME(dw, @dt)
+ ',' + SPACE(1) + DATENAME(m, @dt)
+ SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2))
+ ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))

WHEN 'LONGDATEANDTIME' THEN

DATENAME(dw, @dt)
+ ',' + SPACE(1) + DATENAME(m, @dt)
+ SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2))
+ ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))
+ SPACE(1) + RIGHT(CONVERT(CHAR(20),
@dt - CONVERT(DATETIME, CONVERT(CHAR(8),
@dt, 112)), 22), 11)

WHEN 'SHORTDATE' THEN

LEFT(CONVERT(CHAR(19), @dt, 0), 11)

WHEN 'SHORTDATEANDTIME' THEN

REPLACE(REPLACE(CONVERT(CHAR(19), @dt, 0),
'AM', ' AM'), 'PM', ' PM')

WHEN 'UNIXTIMESTAMP' THEN

CAST(DATEDIFF(SECOND, '19700101', @dt)
AS VARCHAR(64))

WHEN 'YYYYMMDD' THEN

CONVERT(CHAR(8), @dt, 112)

WHEN 'YYYY-MM-DD' THEN

CONVERT(CHAR(10), @dt, 23)

WHEN 'YYMMDD' THEN

CONVERT(VARCHAR(8), @dt, 12)

WHEN 'YY-MM-DD' THEN

STUFF(STUFF(CONVERT(VARCHAR(8), @dt, 12),
5, 0, '-'), 3, 0, '-')

WHEN 'MMDDYY' THEN

REPLACE(CONVERT(CHAR(8), @dt, 10), '-', SPACE(0))

WHEN 'MM-DD-YY' THEN

CONVERT(CHAR(8), @dt, 10)

WHEN 'MM/DD/YY' THEN

CONVERT(CHAR(8), @dt, 1)

WHEN 'MM/DD/YYYY' THEN

CONVERT(CHAR(10), @dt, 101)

WHEN 'DDMMYY' THEN

REPLACE(CONVERT(CHAR(8), @dt, 3), '/', SPACE(0))

WHEN 'DD-MM-YY' THEN

REPLACE(CONVERT(CHAR(8), @dt, 3), '/', '-')

WHEN 'DD/MM/YY' THEN

CONVERT(CHAR(8), @dt, 3)

WHEN 'DD/MM/YYYY' THEN

CONVERT(CHAR(10), @dt, 103)

WHEN 'HH:MM:SS 24' THEN

CONVERT(CHAR(8), @dt, 8)

WHEN 'HH:MM 24' THEN

LEFT(CONVERT(VARCHAR(8), @dt, 8), 5)

WHEN 'HH:MM:SS 12' THEN

LTRIM(RIGHT(CONVERT(VARCHAR(20), @dt, 22), 11))

WHEN 'HH:MM 12' THEN

LTRIM(SUBSTRING(CONVERT(
VARCHAR(20), @dt, 22), 10, 5)
+ RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3))

ELSE

'Invalid format specified'

END
RETURN @dtVC
END
GO

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-09-10 : 09:27:49

Frank,

Thanks for your idea.Im a little lost,appreciate if you could provide some explanation.In my situation,only if all 6 files exist with todaysdate as file name,then the rest of the SSIS pacakge should execute.

1)What in SSIS can I use to write this code.Script Task or Script Component?
2)Upon arrival of all 6 files with current dates,it should execute the rest of the pacakge.Else it should send a mail out.Stating the files nvr arrived.

Im totally lost...hopefully you can help me understand the steps i need to take.Thank You!

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-09-10 : 10:29:11

I tried the following.I created a Script Task with

2 read variables
FileLocation = "C:\Test\"
FileExtension = "*.zip"
1 write variable
FileExist =False

In Script Task
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim fileLoc As String
Dim fileExt As String
Dim fileExist As Boolean = False
Dim dt As String
Dim a As Integer = 0
Dim b As Integer = 0
Dim c As Integer = 0
Dim d As Integer = 0
Dim e As Integer = 0
Dim f As Integer = 0
dt= Now.Date.ToString("MMddyyyy")
If Dts.Variables.Contains("User::FileLocation") = True AndAlso _
Dts.Variables.Contains("User::FileExtension") = True Then

fileLoc = CStr(Dts.Variables("User::FileLocation").Value)
fileExt = CStr(Dts.Variables.Item("User::FileExtension").Value)

Dim dirInfo As New DirectoryInfo(fileLoc)
Dim fileInfo As fileInfo()
fileInfo = dirInfo.GetFiles(fileExt)
For Each file As FileInfo In fileInfo
If file.Name.Contains(dt) Then
If file.Name.Contains("A") Then
a = +1
ElseIf file.Name.Contains("B") Then
b = +1
ElseIf file.Name.Contains("C") Then
c = +1
ElseIf file.Name.Contains("D") Then
d = +1
ElseIf file.Name.Contains("E") Then
e = +1
ElseIf file.Name.Contains("F") Then
f = +1
End If
End If
Next
End If

If a = 1 And b = 1 And c = 1 And d = 1 And e = 1 And f = 1 Then
fileExist = True
Dts.TaskResult = Dts.Results.Success
Else
fileExist = False
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
End Class

To test
I added a Send mail task to send a mail if fileExist=True ,with subject "SUCCESS" and
I added a Send mail task to send a mail if fileExist=False ,with subject "FAILURE"

I tetsed this piece of code,by placing 6 zip files with currentdate 09102008 to the C:\Test folder -Worked with Success .I received a SUCCESS mail .
Tested by removing 1 file and changing file date to previous day.I received a FAILURE mail.

My code might need some improvement.Im rather new to coding and am open to suggestions.

How do I schedule this script task as a job that would run between 9am-12pm and upon arrival of all 6 files execute another SSIS package. ELSE
if no files are received by latest 12pm,send a mail out to me .

I have to get this done quickly,Do help me out.Thank You.

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-09-10 : 10:48:50

How do I schedule this script task as a job that would run between 9am-12pm -
> Need to schedule every hour (or similar) else you need an external file watcher mechanism which could get complicated

arrival of all 6 files execute another SSIS package -
> From Script Task, connect Execute Package Task and set constraint to '@[fileExist] == true'

ELSE if no files are received by latest 12pm,send a mail out to me -
> connect to a Mail Task and set constraint to '@[fileExist] == false'

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-09-10 : 11:07:25

Hi Thanks for your tip,

Do let me know if I got this correct.

Create a sql job.Schedule job to run recurring every 1/2 hour between 9am-12pm.
Job Details
Step 1
Create a Script Task as previously posted.Connect to Execute Package Task [this would refer to the next SSIS package to execute,if all files are received with success].Constraints are to be set as @fileExist="True".Create a send mail task and connect it to Script Task.Constraints to be set is @fileExist="False".
On Sucess
-Quit job reporting SUCCESS
On Failure
-Quit job reporting FAILURE.

My doubt is,example sql job executes and files are not received at 9am.Upon script task execution,it would send a Failure email as files are not there.
At 9.30am sql job run again, this time files are there.It goes ahead and runs the next SSIS pacakge.

Will I be receiving a failure email upon each 30 min that the files are not available between 9-12.

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-09-10 : 12:06:14

Sorry add this to your Script Task somewhere to check if it's noon yet:


        If DateTime.Now => DateTime.Parse("12:00:00") Then
            Dts.Variables("itsNoon").Value = True
        End If

New variable called "itsNoon" type Boolean, default False

On contraints, '@[fileExist] == false && @[itsNoon] == true' to trigger email

franktellez
Starting Member

3 Posts

Posted - 2008-09-10 : 12:19:44

To notify configure send mail feature in sql server
create an additional task

use a condition statement:

if time > 12:00

execute task (send mail)

Sending an e-mail message

This example sends an e-mail message to Dan Wilson using the e-mail address danw@Adventure-Works.com. The message has the subject Automated Success Message. The body of the message contains the sentence 'The stored procedure finished successfully'.

Copy Code:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AdventureWorks Administrator',
@recipients = 'danw@Adventure-Works.com',
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Success Message' ;

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-09-10 : 12:50:40

Thank You.I have added your suggestion to my script task.Im currently putting in place my SSIS package together and would be testing it.Here is what I have in place so far.

1 - Script Task to check 6 zip files [as posted in forum]
On Success of 1-
2- Script Task to unzip and perform other file manipulation [Copy /move /delete and etc....]
3- Execute Package Task 1
4- Execute Package Task 2
5- Execute Package Task 3
6- Execute Package Task 4
7- Execute Package Task 5
8- Execute Package Task 6
9- Script Task to zip up files and perform other file manipulatio.

On Failure of 1- & Time is 12 pm
Send Mail Task

I shall be updating you soon,when I test it out.

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-09-10 : 17:14:08

Oh, if you can't wait on noon time, you can always manipulate this earlier value so it fires sooner; just for time sake.

DateTime.Parse("12:00:00") to DateTime.Parse("09:00:00") or something less than current time

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-09-11 : 09:51:30

I added a few extra var and a sql task to WAITFOR DELAY '00:00:30.This sql task is connected to the script task.Here sre the changes I made to my intial code.

If fileCount=6 Then
Dts.Variables("User::FileExist").Value = True
Dts.Variables("User::DoFlag").Value = False
Dts.Variables("User::ImportFiles").Value = True
Dts.TaskResult = Dts.Results.Success

'Files not there and time is more than 12pm
ElseIf fileCount<>6 And DateTime.Now >= DateTime.Parse ("12:00:00") Then
Dts.Variables("User::FileExist").Value = False
Dts.Variables("User::DoFlag").Value = False
Dts.Variables("User::ImportFiles").Value = False
Dts.TaskResult = Dts.Results.Failure

'Files not there and time is still not 12pm
ElseIf fileCount<>6 And DateTime.Now <= DateTime.Parse("12:00:00") Then
Dts.Variables("User::FileExist").Value = False
Dts.Variables("User::DoFlag").Value = True
Dts.Variables("User::ImportFiles").Value = False
Dts.TaskResult = Dts.Results.Failure

Upon executing the script above and moving to the next step which is another Script Task.This other script task would manipulate incoming files [unzipping/moving /deleting].

This script task works fine when tested outside SSIS pacakge.When I included the same code in script task and executed it,I ge a "Script fail to load" error.

I have tried changing the PrecompileScriptIntoBinaryCode to both True & False.There are no breakpoints in my code.
How Can I solve this ?

Need Help !

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-09-11 : 10:21:50

Take out Dts.TaskResult = Dts.Results.Failure lines and just move Dts.TaskResult = Dts.Results.Success
at the very end. All you're doing on this script is storing values which you should place in the contraint expressions. Let the logic from contraints expression decide which task(s) to execute.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-09-11 : 12:34:48

I tested my code and it works good.

Once the script task that checks for file returns a success.I have connected it to another script task that would unzip,move,copy,delete,and rename the files.Basically manipulate the files.

Problem is I have tested the code in this script task outside SSIS package and it works well.When I include it into script task in SSIS package.I get a "script fail to load" error.

I have no breakpoints in this code.My system in X86 and the PrecompileScriptIntoBinaryCode has been set to Both True & False.

No success.I cannot proceed if I cant get this part to work.Help me here ......

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-09-11 : 12:48:38

"Problem is I have tested the code in this script task outside SSIS package..."

I'm not sure what you mean by tested outside SSIS package?

Error maybe related to this article?
http://support.microsoft.com/kb/931846

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-09-11 : 13:10:57

Thank You for the site,Im gonna try the solution there.

What I mean by tried the code outside SSIS pacakge is,this code is written by a .Net developer in Visual Studio -Visual Basic.He has tested the code and works completely fine with test files.

When I include the same script into a Script Task.I get the error.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-09-11 : 13:57:14

My bad, The Public Class Script Main was missing from the code,It had been replaced by a Module instead.

I changed the Module to a Public Class Script Main and it worked.

franktellez
Starting Member

3 Posts

Posted - 2008-09-12 : 16:29:05

Cool,

Would you mind posting the final code for us to see?

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-09-25 : 13:45:14

Here is the function that does the checking for the files.

Shared Sub FileMatcher(ByVal Location As String, ByVal Extension As String, ByVal fileName() As String)

Dim i As Integer = 0
Dim fileCount As Integer = 0
Dim fileCount1 As Integer = 0

Try
Dim dirInfo As New DirectoryInfo(Location)
Dim fileInfo As FileInfo()
fileInfo = dirInfo.GetFiles(Extension)

For i = 0 To 5 Step 1
For Each file As FileInfo In fileInfo
If fileName(i) = file.Name Then
fileCount1 = fileCount1 + 1
Exit For
End If
Next
Next

'All 6 .zip files with today's date exist
If fileCount1 = 6 Then
Dts.Variables("User::FileExist").Value = True
Dts.Variables("User::DoFlag").Value = False
Dts.Variables("User::ImportFiles").Value = True
Dts.TaskResult = Dts.Results.Success
'Files not there and time is more than 12pm
ElseIf DateTime.Now >= DateTime.Parse("12:00:00") Then
Dts.Variables("User::FileExist").Value = False
Dts.Variables("User::DoFlag").Value = False
Dts.Variables("User::ImportFiles").Value = False
Dts.TaskResult = Dts.Results.Failure
'Files not there and time is still not 12pm
ElseIf DateTime.Now <= DateTime.Parse("12:00:00") Then
Dts.Variables("User::FileExist").Value = False
Dts.Variables("User::DoFlag").Value = True
Dts.Variables("User::ImportFiles").Value = False
Dts.TaskResult = Dts.Results.Success
End If

Catch ex As Exception
Console.WriteLine(ex.Message)

End Try
End Sub
End Class

You will need to create the variables as above.
the parameter being passed to the function is
Location - The location to watch for incoming files
Extension - '*.zip'
fileName() -Name of files to be watched for

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources