all groups > sql server dts > october 2007 >
You're in the

sql server dts

group:

SSIS - How to force a connection manager to evaluate its expressio


SSIS - How to force a connection manager to evaluate its expressio Todd Beaulieu
10/16/2007 7:47:03 AM
sql server dts:
I'm writing a custom log provider that needs a connection manager to record to.

The connection manager's ConnectionString is set via an expression and that
expression is not satisfied until a sccript task can retrieve the connection
string and plug it in.

All this means the logging events are flowing long before the connection can
be established. I'm queueing up the events for "catch-up" once the logger
sees the connection has been established.

Is there an inexpesnive way to determine if the connection has been
established? I was using AquireConnection() within a try...catch block.

a) This is very expensive.
b) I'm now suffering from a recursive log/failure cycle which I have yet to
resolve.

I can't query the ConnectionString, because it never gets set for some
reason. Experiments with my other connection managers suggests the run time
waits until the connection is "needed" until it evaluates the expression. Is
there a way to force it to evaluate? I've tried "peeking" at properties, but
that doesn't force it.

RE: SSIS - How to force a connection manager to evaluate its expressio Todd Beaulieu
10/17/2007 4:58:01 AM
I'm not thrilled about this solution, but it does seem to work. I added a
Script Task that forces connections on the connection managers. This causes
the expressions to be evaulated and therefore the connection strings to be
filled in.

Try
Dim cm As ConnectionManager

For Each cm In Dts.Connections
cm.AcquireConnection(Nothing)
Next

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
Throw ex
End Try
Re: SSIS - How to force a connection manager to evaluate its expressio jhofmeyr NO[at]SPAM googlemail.com
10/26/2007 5:23:15 AM
On Oct 17, 12:58 pm, Todd Beaulieu
[quoted text, click to view]

Hi Todd,

I guess another way to do this would be to set the actual connection
string in your script task instead of in an expression. This might
be more efficient that the method you're using above.

Regards,
J
Re: SSIS - How to force a connection manager to evaluate its expre Todd Beaulieu
10/26/2007 6:08:01 AM
I know of no way to manipulate the connection string directly. In SSIS, from
everything I've read, properties are read-only, and the mechanism through
which we are to set them is expressions.

[quoted text, click to view]
Re: SSIS - How to force a connection manager to evaluate its expre jhofmeyr NO[at]SPAM googlemail.com
10/27/2007 4:43:17 AM
On Oct 26, 2:08 pm, Todd Beaulieu
[quoted text, click to view]

Hi Todd,

It is definitely possible to change the connection string from inside
a script task.
Try:
Dts.Connections("test").ConnectionString = "<insert connection string
here>"

As with an expression, you only have ensure that the metadata of
tables that use the connection is valid for any of the databases that
you point the connection manager at :)

Regards,
J
Re: SSIS - How to force a connection manager to evaluate its expre jhofmeyr NO[at]SPAM googlemail.com
10/27/2007 5:02:58 AM
On Oct 26, 2:08 pm, Todd Beaulieu
[quoted text, click to view]

Hi Todd,

You can indeed change the ConnectionString of a connection manager at
runtime using a Script task.
Try:
Dts.Connections("test").ConnectionString = "<insert connection string
here>"

This was tested only with script tasks in the Control Flow, not in the
Data Flow - but I'd think on principle it should be the same.

You do obviously have to ensure (as you do with expressions) that any
tables referenced using the connection manager has the same metadata
as was stipulated at design time.

Good luck!
J
Got something to say? Click here to post a reply to this thread.
Don't see what you're looking for? Try a search.
View other messages in the sql server dts group.
AddThis Social Bookmark Button
View Other Months
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
home · blog · groups Questions? Comments? Contact the dn