February 6, 2008 at 2:50 pm
HI everybody,I want to tell my ssis package to stop executing when the source file not found in the path.my package is working very well but it gives me an error if does not find a specified file name,I want it stop executing when the source is not found because my package reads excel source files which are updated weekly so sometimes it happens that some of the files are not there for some business reseals.please help if there is any idea on how to do this.
I wish my problem is clear enough to you all.your help will be much appreciated.
February 11, 2008 at 12:08 pm
I've done this in a couple of ways - I'll try and give you a place to start...
1. Create a variable (scoped to the whole SSIS package) which will contain the full path of the filename
2. Add a second variable to the package as an indicator of the file's existence
3. Add a script task to your control flow that you can use to set the variable from step 2.
A script I've used before (you would change this to use your own variable or variables):
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.VisualBasic.FileIO.FileSystem
Imports System.IO.FileSystemInfo
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim cDataFileName As String
Dim cFileType As String
Dim cFileFlgVar As String
WriteVariable("SCFileFlg", False)
WriteVariable("OOFileFlg", False)
WriteVariable("INFileFlg", False)
WriteVariable("IAFileFlg", False)
WriteVariable("RCFileFlg", False)
cDataFileName = ReadVariable("DataFileName").ToString
cFileType = Left(Right(cDataFileName, 4), 2)
cFileFlgVar = cFileType.ToUpper + "FileFlg"
WriteVariable(cFileFlgVar, True)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class
4. You can then use the precedence constraint (after the script task) to verify that the file exists (Use "Expression and Constraint" to make sure the script succeeds and the make sure the variable from step 2 shows the existence of the file). If the file exists, direct it to the data flow task for the import, otherwise direct it to another task to either terminate or notify (whichever one you want).
Hopefully this helps.
Dan
February 12, 2008 at 11:38 am
HI Dan.
I'm very happy with the solution you gave me yesterday.The problem I have now is that the script tust runs and says validation completed it does not give me any arror.I'm not sure which one comes first between the script tust and the dataflow becouse the way I understand it,after the script tust run the data flow must start the process but that does not happen
I also suspect that my varriables are not confirgered correctly,I would be glad if you can also give me an idea on how to configure these varriebles.
Best Regards
February 12, 2008 at 12:06 pm
guybornn,
Once you make sure your variable is set correctly by the script, you use the control flow to decide what to do next. (The little green arrow that comes out of the script component has options inside.) I've attached a screen shot from one of my packages that uses this technique.
Notice that there are several "exits" from the script component. The "fx" shows that each path only happens when the script step succeeds and the function inside the control flow meets a certain criteria.
This is pretty basic stuff. I'm going to be a stern task master 😉 and suggest you get one of the many SSIS books out there. I personally like the one Brian Knight had a hand in (Professional Integrations Services Programmer). There are also a lot of web sites (including MSDN and TechNet webinars) where you can learn stuff without spending a lot.
I hope this doesn't frustrate you, but I'm a firm believer in the old addage "Give a man a fish, he eats for a day, teach a man to fish and he eats for a lifetime." Go fishing my friend!
Dan
February 12, 2008 at 12:16 pm
how about using the dts.events.FireError in your script ?
Keep in mind you 'll also need to enable the "FailPackageOnFailure" property.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 12, 2008 at 12:25 pm
Another option - use a System.IO within a script task to populate a boolean variable - i.e.
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
'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 = CStr(Dts.Variables.Item("User::varFileName").Value)
fileName = Dts.Variables("varFileDirectory").Value.ToString & "\" & CStr(Dts.Variables.Item("User::varFileName").Value)
'System.Windows.Forms.MessageBox.Show(fileName)
If File.Exists(fileName) Then
Dts.Variables.Item("User::varFileExists").Value = True
'System.Windows.Forms.MessageBox.Show("File Exists!")
Else
Dts.Variables.Item("User::varFileExists").Value = False
'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
Then use an expression in the constraint (LOGICAL OR) to determine the next step - i.e. trigger an alert notification or proceed to the next sequence container.
@[User::varFileExists]==True
@[User::varFileExists]==False
Tommy
Follow @sqlscribeFebruary 12, 2008 at 12:32 pm
I used to do that direct interface as well, but migrated to the method I pasted above because I kept running into variable locking issues. (I can't remember exactly what they were, but the "ReadVariable" and "WriteVariable" Private Subs took care of the problem.)
To give credit where credit is due, I got it here:
Reusable Functions for Reading and Writing Variables in SSIS Script Tasks
That script performs the same action with less issues.
February 13, 2008 at 3:00 pm
A BIG thanks to you all guys,my script tust is working very well now.
Dan
I have used the second script that you gave me ,it is perfect.
As I'm still using sql you will be seeing my name more often becouse you are really helpful guys.
Regards
Guyborn
February 13, 2008 at 3:03 pm
Glad to help.
February 9, 2009 at 2:31 am
Hello All,
- I will offer another way of doing what the OP wants without writing any code.
Step 1:
Either: Create a File COnnection Manager pointing to the file
OR: Provide a PAckage Variable with the Path and Filename as value.
Step 2:
Use a File System Task. Set its Operation to "SEt Attributes" (no need to actually change any attributes). Set it's Source to either the aforementioned File Connection Manager or the Package Variable.
Step 3: Repeat as needed for any number of files.
Comments: If the file is not found the corresponding File System Task will fail, otherwise the Control Flow proceeds to the next step. No need for code. Far easier debugging.
hth,
ArthurDent
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply