August 7, 2009 at 4:59 am
Hi,
I have for each look going for the file to process in ssis but I want to send emmail if there is no file to process starting with. If there is file then it send mail with file name. I have variable defined for loop to get file name.
How can i set up that.
Thansk
August 7, 2009 at 2:15 pm
So if your package kicks off and there are no files in the folder to process, you want an e-mail sent out? If that's what you're looking for, create a package level variable to count the # of files processed inside your loop. You'll need to add a task inside the loop to increment the variable.
From the for each container, connect a send mail task. Set the Precedence Constraint between the two to use an expression. Set the expression to test for your file count variable. If file count = 0, send mail.
If you want to send an e-mail for each file name, put a send mail task inside your loop. Use the expression builder to include the file name variable inside the Message Source property of the send mail task.
August 8, 2009 at 7:19 pm
Hi,
I have fname variable define. I can't understand the expression how can i right in express.
is it right.
if count(@[User::Fname]) = 0 then exec send_dbmail?
else exec send_dbmail @body = @[User::Fname]
?
please help
August 10, 2009 at 3:53 pm
August 12, 2009 at 10:40 am
Hi
I have a similar issue to resolve i.e if no file, then send an email.
I have created my package variable (as int32) but am unsure as to what task i should create within the loop - should this be a sql task?
i am new to ssis so excuse the questions!
thanks in advance
haythem
August 12, 2009 at 11:37 am
Haythem.Al-Kishtaini (8/12/2009)
HiI have a similar issue to resolve i.e if no file, then send an email.
I have created my package variable (as int32) but am unsure as to what task i should create within the loop - should this be a sql task?
i am new to ssis so excuse the questions!
thanks in advance
haythem
The task to increment the variable I take it?
If so, I'd use a script task. Let's say your variable name is FileCount, here's the script:
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim vars As Variables
Dim Count As Integer
Dts.VariableDispenser.GetVariables(vars)
Dts.VariableDispenser.LockOneForRead("FileCount", vars)
Count = vars(0).Value
vars.Unlock()
Dts.VariableDispenser.LockOneForWrite("FileCount", vars)
vars(0).Value = Count + 1
vars.Unlock()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
August 13, 2009 at 3:01 am
thanks for the script. However, i really am at a beginner level on these things...
i have my foreach container but haven't figured how to actually assign the variable to the container?
I have assigned other variables such as file names but on this my beginners knowledge is showing!
August 13, 2009 at 12:09 pm
You don't have to 'assign' the variable to any of the container properties. All you need to do is create the variable and add a script task with the code example that I gave you.
Then, you'll want to do some debugging to ensure that the code is incrementing the variable as expected. Here's what I'd do:
1. Add an Execute SQL task after your For Each file container. Set the SQL Statement in this task to 'SELECT 1'. This is just so the task passes validation.
2. Next, place a debugging breakpoint on this task. This will halt the package execution after the for each container.
3. Using the Watch Window, place the FileCount variable to the 'Name' column. This will allow you to view the run time value of your FileCount variable. You can use this method to capture and verify the FileCount value to ensure that the script is properly incrementing the value. Once you are satisfied that it is working, delete the Execute SQL task from step 1.
August 14, 2009 at 1:53 am
this now works! thanks for the explanation. I have added two precedence containers (plus the script task ofcourse) and now it either sends an email saying no file is present or it continues with the process.
Thanks again.
Haythem
August 14, 2009 at 8:22 am
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply