September 9, 2008 at 2:40 pm
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.
September 9, 2008 at 5:34 pm
Some of the options you have are:
1. Use a sql server job
2. Use a Windows scheduled task
3. Use a Windows Service
I have no experience with the first option. The second option is easy if you are a programmer (.NET for example). Option 3 is also fairly straight forward for a programmer - you could have an instance of a File System Watcher to take an action when the files arrive.
Personally I'd recommend against option 3 as you know the times where the files are expected to be there.
September 9, 2008 at 7:48 pm
Hi Paul,
Im interested in the 2nd option,any ideas on how I can get started with this.thanks
September 9, 2008 at 8:08 pm
Just need to create an executable (in any language) and create a windows scheduled task to invoke it at the time(s) you want.
The code simply needs to check that the files you expect are in the location that they will be dropped into. It then can either send an email or execute the ssis package depending on your logic.
Executing the package can be done either via the command line tool or via the SSIS object model. Note that whatever executes the ssis package needs to be on a box with sql server standard (or above) installed.
Here is a link to an article explaining how you can call ssis package from .NET code:
http://msdn.microsoft.com/en-us/library/aa337077.aspx
I suggest you consider sql agent jobs for doing this also. Sending emails and executing ssis packages are do-able from them also. Not sure about the file checking business though.
September 10, 2008 at 7:21 am
Im thinking of doing the following,writing a .net code to check for file exist and add it to script component.Upon success execute the rest of the SSIS package.
Do you think this will work ?
September 10, 2008 at 8:27 am
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.
September 10, 2008 at 5:33 pm
Sorry I have no experience with setting up jobs in sql server.
But I don't see why it has to be an ssis package at all. This just doesn't seem correct.
September 11, 2008 at 12:39 am
think this may help you for scheduling an ssis package
September 11, 2008 at 12:40 am
http://www.mssqltips.com/tip.asp?tip=1180
this link may help you
September 11, 2008 at 7:04 am
I manage to get this done with some help here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110326
To my current code already in place,I have added
'Files are there
If a= 1 And b = 1 And c = 1 And d = 1 And e = 1 And f = 1 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 a <> 1 And b <> 1 And c <> 1 And d <> 1 And e <> 1 And f <> 1 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 a <> 1 And b <> 1 And c <> 1 And d <> 1 And e <> 1 And f <> 1 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
End If
September 11, 2008 at 7:15 am
Upon executing the script above and moving to the next step which is another Script Task.This 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 ?
Anyone help 🙁
September 11, 2008 at 8:27 am
Perhaps this will be of assistance 🙂 I wrote this a while back to check for the existence of a file in a certain directory. It populates a boolean variable varFileExists. I can then use a expression (logical OR) in the constraint to determine wether or not to proceed w/ the the rest of the package.
Variables:
varFileDirectory,varFileName,varFileExists
Expression:
@[User::varFileExists]==True
-or-
@[User::varFileExists]==False
Script Task:
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
'Author: Tommy Bollhofer (tbollhofer2@gmail.com)
'Last Modified: 09/2007
'Purpose: Determines if the file exists and sets the varFileExists boolean value.
Public Sub Main()
Dim fileLoc, fileName As String
If Dts.Variables.Contains("User::varFileName") = True Then
fileName = Dts.Variables("varFileDirectory").Value.ToString & "\" & CStr(Dts.Variables.Item("User::varFileName").Value).Replace(".gz", "")
'Uncomment for Debug
'System.Windows.Forms.MessageBox.Show(fileName)
If File.Exists(fileName) Then
Dts.Variables.Item("User::varFileExists").Value = True
'Uncomment for Debug
'System.Windows.Forms.MessageBox.Show("File Exists!")
Else
Dts.Variables.Item("User::varFileExists").Value = False
'Uncomment for Debug
'System.Windows.Forms.MessageBox.Show("File Does Not Exist!")
End If
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
End Class
Tommy
Follow @sqlscribeSeptember 11, 2008 at 9:24 am
Thanks Tommy,Im gonna keep your code for refrences.
My problem is
I have another script task that would manipulate incoming files [unzipping/moving /deleting] after checking if the file exist.
This script task works totally 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 ?
September 11, 2008 at 9:39 am
Are you missing any references? Is this an x64 or x86 system? Have you tried copying the contents of the script task to the clipboard, deleting the script task and creating a new one?
Tommy
Follow @sqlscribeSeptember 11, 2008 at 9:44 am
This is a X86 system .I will try to delete and recreate the script task.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply