47468

SSIS Package 2005 - dynamic ToLine

Question:

I need to route an email using the Send Mail Task, in SSIS 2005 depending on a variable within a script task.

So, far, I've created a Variable in the variables tab, but can't seem to figure out where to set it, or how to access it using the code.

Apologies for the vague question, but this isn't my bag, and i'm trying to help out another team. I can send through more detail if required.

<img alt="Script task with a Send Mail Task" class="b-lazy" data-src="https://i.stack.imgur.com/wt2Up.png" data-original="https://i.stack.imgur.com/wt2Up.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

<img alt="Variable within the Variable tab" class="b-lazy" data-src="https://i.stack.imgur.com/S21gq.png" data-original="https://i.stack.imgur.com/S21gq.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

Public Function SetEmailAddress(ByVal variable x As String) As String if x = y then set the isConfirmation variable.

The little code snippet above, is what i'm trying to achieve within the Script Task - process file object.

Answer1:

I don't have a copy of the 2005 SSIS release handy, so some of this answer is approximate.

The big picture what you will need to do is modify the .Value of an object in the SSIS Variables collection.

<h2>Setting Variable's value via code</h2>

With 2008+, they modified the editor so you can specify that you're going to access SSIS Variables. I can't recall if they made that available in 2005. Based on <a href="http://consultingblogs.emc.com/jamiethomson/archive/2005/02/09/SSIS_3A00_-Writing-to-a-variable-from-a-script-task.aspx" rel="nofollow">Jamie's</a> post, it looks like it was available. So, you can either use a GUI if it's available to indicate you want to modify the value of isConfirmation or you can go through the lock/unlock process below

Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain Public Sub Main() Dim vars As Variables Dts.VariableDispenser.LockOneForWrite("isConfirmation", vars) ' Assign a boolean value to the thing we just locked for modifying vars(0).Value = (x=y) vars.Unlock() Dts.TaskResult = Dts.Results.Success End Sub End Class

If you use the GUI to indicate you're locking the variable, it'd be approximately

Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain Public Sub Main() ' Update it in place Dts.Variables("isConfirmation").Value = (x=y) Dts.TaskResult = Dts.Results.Success End Sub End Class

Even though you're in VB which is not case sensitive, the base SSIS object model is case sensitive so IsConfirmation is not going to be found in the Variables collection.

<h2>Putting it all together</h2>

Assuming you have three SSIS Variables created, isConfirmation - boolean and two String variables Address1 and Address2. When isConfirmation is true, we wish to use Address1. When false, we want to use Address2.

You can create the expression directly on the object but you can't debug that. Instead, create <em>another</em> variable, FinalAddress of type string. Right click on it and in the properties menu, set the EvaluateAsExpression property to True and in the Expression use the following formula @[User::isConfirmation] ? @[User::Address1] : @[User::Address2]

Then, on the Send Mail Task itself, set the ToLine (approximate) to be @[User::FinalAddress]

<h2>Troubleshooting</h2>

According to the comments, there are problems when the package is run at the server. In cases like this, I'd look to raise some event messages from the package.

Dim fireAgain As Boolean = True Dim isCurrent As Boolean = True ' logic here ' report on status of what we care about Dts.Events.FireInformation(0, "Debug", String.Format("isCurrent = {0}", isCurrent), String.Empty, 0, ByRef fireAgain)

When you call the package, either through agent or the command line, you're going to want to make sure the /REPORTING E parameter becomes /REPORTING EIW (to report errors, warnings and information events. Rarely is it a bad practice to log this information as it is)

Recommend

  • Python 3.3.3 time.sleep() error [duplicate]
  • Starting audio call only in SImpleWebRTC
  • Trouble separating lists in order to use an element from it
  • Build URL upon submit using select values
  • subsonic 3.0 , The SqlServer default value doesn't work?
  • Why EclipseLink maps a NVARCHAR type to an Object?
  • iis7 pulling wrong connection string - cross site and process?
  • Vim configuration, setting up autocomplete, and columns
  • Porter Stemming of fried
  • Let apache handle 404 error when deemed appropriate by PHP?
  • How do I embed a custom user-control in MS Word?
  • cannot load gems in test environment
  • How to convert Neo4j Result to GraphJSON
  • gulp.watch running same task multiple times when saving many files
  • Bind collection of objects to ListBox
  • Limit Selectable DOM Checkboxes
  • Create an Event Handler whenever a new Node is added in treeview
  • Java Garbage collection, setting reference to null
  • Updating and removing unique join relationships in CakePHP
  • php show all images in directory and sort by last modified
  • CKeditor stripping font tags instead of converting to span
  • How can I add div content to a new page in jspdf?
  • Many to Many in Linq using Dapper
  • EF 4.1 DBContext AutoDetectChangesEnabled
  • Accessing Rows In A LINQ Result Without A Foreach Loop?
  • Where these are stored?
  • abstracting over a collection
  • How can I tell a form not to dispose a particular control when it closes?
  • WPF - CanExecute dosn't fire when raising Commands from a UserControl
  • C++ Partial template specialization - design simplification
  • 'TypeError' while using NSGA2 to solve Multi-objective prob. from pyopt-sparse in OpenMDAO
  • How to get next/previous record number?
  • VB.net deserialize, JSON Conversion from type 'Dictionary(Of String,Object)' to type '
  • retrieve vertices with no linked edge in arangodb
  • Python: how to group similar lists together in a list of lists?
  • How get height of the a view with gone visibility and height defined as wrap_content in xml?
  • Memory offsets in inline assembly
  • Turn off referential integrity in Derby? is it possible?
  • Linking SubReports Without LinkChild/LinkMaster
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize