easier email alerts from sql 7/2000?

hi,

i get just as frustrated each time i try to configure email alerts on failed jobs on ms sql, it is beyond me why microsoft couldn't just let you point out an SMTP server to send through and be done with it.

is there a way to avoid having to setup an email client on our sql 7 and 2000 servers through some 3rd party app or other simple solution?

thanks in advance,
danielhi,

is there a way to avoid having to setup an email client on our sql 7 and 2000 servers through some 3rd party app or other simple solution?

thanks in advance,
daniel

Amen, brother!

You do have some options:

1. There are some 3rd party tools to (sort of) accomplish what you are trying to do. They are not supported, however. Look at www.sqldev.net.

2. You can add additional steps to the jobs you wish to monitor (and set the task order to on failure).

3. You can upgrade to SQL 2005. This is not quite as awful as it sounds and getting to Database Mail (which is SMTP) is a major benefit!

Regards,

hmscott|||Amen, brother!

You do have some options:

1. There are some 3rd party tools to (sort of) accomplish what you are trying to do. They are not supported, however. Look at www.sqldev.net.

2. You can add additional steps to the jobs you wish to monitor (and set the task order to on failure).

3. You can upgrade to SQL 2005. This is not quite as awful as it sounds and getting to Database Mail (which is SMTP) is a major benefit!

Regards,

hmscott
1. thanks, i'll check them out. but maybe it's just easier to parse the errorlogs of both servers for failed backups from some php script.

2. do you mean add a step with the "operating system command" type and execute some external program to send alerts?

3. the sql7 server hosts 67 databases, the sql2000 server hosts 38, most of them production databases so it's still a scary thought time-wise :)

Dynamically configure CacheType?

I'm looking for a way to dynamically set the CacheType property of a Lookup transformation via a configuration variable. Is this possible? The CacheType does not appear to be a selectable property when choosing which properties to export to a configuration file. Can this property be manipulated through a script task inside the package?

Interesting question. Looking into it I found out that the data flow components don't support expressions, which would have been nice and the solution to your problem. And you can't modify a package within itself. So, to answer your question, its not possible.

So, in view of that, if your data flow is not very complex, you can try two copies of it, on with the cached lookup another without. You would use expressions on the precedence constraint to decide which one to execute during runtime.

|||

Ravi G wrote:

Interesting question. Looking into it I found out that the data flow components don't support expressions, which would have been nice and the solution to your problem. And you can't modify a package within itself. So, to answer your question, its not possible.

So, in view of that, if your data flow is not very complex, you can try two copies of it, on with the cached lookup another without. You would use expressions on the precedence constraint to decide which one to execute during runtime.

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.|||

Phil Brammer wrote:

Ravi G wrote:

Interesting question. Looking into it I found out that the data flow components don't support expressions, which would have been nice and the solution to your problem. And you can't modify a package within itself. So, to answer your question, its not possible.

So, in view of that, if your data flow is not very complex, you can try two copies of it, on with the cached lookup another without. You would use expressions on the precedence constraint to decide which one to execute during runtime.

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.

Took the words right out of my mouth Phil In case anyone is interested, expressions on data-flow components was virtually the very last feature that was added to the product prior to RTM. All components CAN support expressions on their custom properties, the component developer decides whether or not it WILL by setting IDTSCustomProperty90.ExpressionType which is set to one of the values of the DTSCustomPropertyExpressionType enumeration.

In answer to the OP, CacheType of the Lookup component does not allow its value to be set by an expression, so you can't do what you want I'm afraid. If you want this behaviour changing then go to http://connect.microsoft.com/sqlserver/feedback

-Jamie

|||

Phil Brammer wrote:

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.

Phil,

I tried it and I can only see package level properties. I'll have to look closely.

But control flow doesn't seem like the right place to me. What if you have multiple instances of a data flow component that supports expressions, how would you know which property belongs to which instance?

|||

Ravi G wrote:

Phil Brammer wrote:

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.

Phil,

I tried it and I can only see package level properties. I'll have to look closely.

That's because not all components allow you to set their properties using expressions. if they do, then those properties will show up (off the top of my head I know that the SQLCommand property of the Datareader Source component can be set this way so take a look at that).

Ravi G wrote:

But control flow doesn't seem like the right place to me. What if you have multiple instances of a data flow component that supports expressions, how would you know which property belongs to which instance?

The path syntax allows for it - as you shall see.

Sounds like good blog material

-Jamie

|||

Ravi G wrote:

Phil Brammer wrote:

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.

Phil,

I tried it and I can only see package level properties. I'll have to look closely.

But control flow doesn't seem like the right place to me. What if you have multiple instances of a data flow component that supports expressions, how would you know which property belongs to which instance?

Sorry. Right click on the data flow on the control flow background.

They are "fully qualified." [DFComponentName].[xxxx].[Property]|||

Phil Brammer wrote:

Sorry. Right click on the data flow on the control flow background.

They are "fully qualified." [DFComponentName].[xxxx].[Property]

Cool. I see it now.

In my package, the derived column was the only component that supported this.

This is how it shows up:

[DF component name].[Derived Column Output].[Column Name].[FriendlyExpression]

What does FriendlyExpression mean?

|||

Ravi G wrote:

Phil Brammer wrote:

Sorry. Right click on the data flow on the control flow background.

They are "fully qualified." [DFComponentName].[xxxx].[Property]

Cool. I see it now.

In my package, the derived column was the only component that supported this.

This is how it shows up:

[DF component name].[Derived Column Output].[Column Name].[FriendlyExpression]

What does FriendlyExpression mean?

That's the name of the property. Don't worry too much about why its called that, just know that it is a custom property that can be affected with an expression. More here: http://msdn2.microsoft.com/en-us/library/ms141069.aspx

Admittedly this takes a bit of getting your head around. Basically you can set an expression using an expression. Or to put it another way, the result of your expression is, in itself, another expression.

-Jamie

|||

I've found this useful reference for all the custom properties of the stock components.

http://msdn2.microsoft.com/en-us/ms136014.aspx

-Jamie

Dynamic Tool Configuration

Newbie here wanting to get information, links, opinions on how to configure a simple data flow from code. Here's my example. I have a CSV input tool, a SORT tool, and a CSV output tool. I want to call this package from an application running C#. Can I access this package like DataPack("filename","sort","output filename") from the object model? Or does XML have to get involved? Can I dynamically swap out the input or output tools? Any links to the UML mockup of the object model? Just getting started here.

Thanks in advance,
Mitch

Start here: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/60fe148b-a7c4-4289-ae3e-2e949fc1886c.htm