March 19, 2014 at 2:56 pm
I'm working with an existing SSIS ETL package to add custom logging that will write specific entries into a SQL database. If a task fails, I want to write a custom message for a report to pick up. I'm using a SQL task to execute on failure and write the entries. On the Data Flow task it works fine, failing when there is a problem with the incoming file and writing the record. The For Each Loop is more challenging because it executes and shows success even if it doesn't find the files. How can I cause it to FAIL when there are no files? Any help would be appreciated.
OTPS
Only The Paranoid Survive
March 19, 2014 at 3:05 pm
You could have a variable that changes its value inside the foreach loop and use it in the constraint after the loop.
March 20, 2014 at 7:40 am
I can see the concept but not the execution. How would I set a variable to something like the value of the enumerator so if it was zero I could use it in the constraint.
March 20, 2014 at 10:04 am
OTPS (3/20/2014)
I can see the concept but not the execution. How would I set a variable to something like the value of the enumerator so if it was zero I could use it in the constraint.
You can keep a running count as has been suggested.
For instance...
Add a Variable to your Package (make sure it's scoped to the package level).
Add a ForEach Loop Container.
As a step within the Container, Add a Script Task giving it ReadWrite access to the variable you created.
Add script along these lines to the Task...
Dts.Variables["User::MyVariable"].Value = ((int)(Dts.Variables["User::MyVariable"].Value)) +1 ;
Add another Script Task as a step outside the ForEachLoop Container.
Add code along these lines to it...
MessageBox.Show(Dts.Variables["MyVariable"].Value.ToString());
Run it and you should see the number of times your ForEach container looped.
Hopefully, with that you get the general idea.
March 20, 2014 at 10:18 am
I'm assuming that you know how to create a variable (If you don't please tell me).
You have to ensure that your variable scope is set to the package.
Then you add a script task into the loop.
You set your variable ("Counter" in my example) to the ReadWriteVariables property and edit the Script like this (I'm using VB but you could change to C# if you prefer):
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.
Imports System
Imports System.Data
Imports System.Math
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()
Dts.Variables("Counter").Value = Dts.Variables("Counter").Value + 1
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
That will allow you to have a variable that counts for you.
If you have any more questions, feel free to ask.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply