January 5, 2010 at 9:57 am
Here's the deal. I'm trying to use the connection manager information (so the package is portable and editable in the job step) in expressions. I may be going about this the hard way but here's what I'm hoping to get as an end result:
I want the flat file destination in a dataflow to go to whatever the connection manager is set to in the final job. I don't want to hard code the location anywhere in the process otherwise the thing really isn't portable.
All the search results I've found deal with dynamic file names but not dynamic file locations.
If anyone can point me in the right direction I would greatly appreciate it.
January 5, 2010 at 12:41 pm
For example, if you use "c:\temp\test.txt" as filename, it includes location.
or, using two variables, one represents location, one for filename, in your expression editor, concatenate these two.
January 5, 2010 at 12:48 pm
wwei is right on. The ConnectionString property of the flat file connection manager object represents the path and file name. Setting this property, whether you use one or two variables, will tell the connection manager not just what to name the file, but where to put it as well.
January 6, 2010 at 9:35 am
I must not have been clear. I don't want to set the location with a variable. I want to use the location in the connection string as a variable.
I need this as a variable to use in expressions. So if the connection manager information changes then the expression will update accordingly.
January 6, 2010 at 10:24 am
January 11, 2010 at 8:07 am
The location would change based on the connection manager information at the job step.
The DBAs would then have control over which servers send mail from a process without having to go into the package. We're limiting the servers that can send mail to a few enterprise servers so the local server jobs would need to identify where to place the destination file. Since the mail send job will process everything that's in a share the local server job could be changed.
Maybe the larger question is how people are handling sending mail from server jobs in an enterprise environment. Obviously we don't want every SQL server to be able to connect to the hub and we'd like to balance the processes so no server gets swamped with mail causing delivery delays. The process in development is just as I described - destination shares and jobs on the enterprise servers to shred the files that are in place.
January 11, 2010 at 9:56 am
MrsPost (1/11/2010)
The location would change based on the connection manager information at the job step.
Well, that's a given. How is the connection manager getting its information? Are you hard-coding the locations and mail server addresses into packages or are you using config files?
In order for you to use the location in an expression, you'll need to get it into a variable. I don't know of a way to read it from the connection manager. It may be possible with a script task, but my move would be to have the locations in config files. The package would read the location into a variable and use the variable to set the connection manager properties. You could then use the variable in any expression you wish.
As far as managing enterprise mail requests, that's a bit out of my scope. Our network/server guys gave me the address of a mail server our on network that they would like me to use. I use one single server exclusilvly for all of me SSIS work. I have no idea what else is using that same server. I assume that the network/server team would notify me if changes needed to be made for performance or throughput reasons.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply