Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Provide variables for the from and to addresses in Email Task Expand / Collapse
Author
Message
Posted Thursday, November 6, 2008 11:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 31, 2008 1:51 PM
Points: 10, Visits: 8
Hi,
I am new to SSIS and want to use a variable for the from and to address for the Email Task. I have declared a package scope variable GV_FromAddress and tried to use it in the from as @GV_FromAddress. Any help?

Post #598397
Posted Thursday, November 6, 2008 12:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 10, 2010 4:28 AM
Points: 18, Visits: 71
ok,, here's how I did that..

1, setup your variables in the ssis package like you suggest.. then on the properties of the variables add an expression like this.. @[User::Email] ( if you click the ... build button on the expression line in properties, it will help you do this )

you can populate the variables a couple of different ways,,

1, you can use a configuration.. thats under the ssis menu and then configurations.. or you can right click the screen on the control flow tape and you'll see it.. this will use either an environmental variable, or an xml file that you save locally, or a sql database table for storage of you config database.. I would roughly suggest the xml file version if you don't have a preference.

there are some other options where you can pass a variable to a sql job that runs the package.. here's one.. if you're wanting to execute the package from vb.net for example, here's some code I had used previously..

Sub Main()

Dim pkgLocation As String
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult
Dim emailtxt As String = ""

Dim eventListener As New EventListener()
Dim FileLocation As String = ""
If Form1.txtbxFileLocation.Text <> "" Then FileLocation = Form1.txtbxFileLocation.Text
If Form1.txtbxEmailID.Text <> "" Then emailtxt = Form1.txtbxEmailID.Text
pkgLocation = "C:\Temp\Package.dtsx"
pkg = app.LoadPackage(pkgLocation, eventListener)
If FileLocation <> "" Then pkg.Variables("Path").Value = FileLocation
If emailtxt <> "" Then pkg.Variables("Email").Value = emailtxt
pkgResults = pkg.Execute(Nothing, Nothing, eventListener, Nothing, Nothing)

Form1.Label1.Text = pkgResults.ToString()

Console.WriteLine(pkgResults.ToString())
Console.Read()

End Sub

hopefully some of that will help.. try to provide more details in your question if you need more info... like how your trying to go about it.. etc..
Post #598482
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse