Getting OLE DB connection properties in script

I have SSIS packages that send success/failure email upon completion, and I'd like to add a note that identifies the server and database used. I can certainly add variables to the packages and use them when constructing the email, but I'd prefer to get the information directly from the OLE DB connection itself. Is there a way to access the connection string from within a control-flow VB script task? Furthermore, can I get the data source and initial catalog from that connection string, or do I need to parse it myself?

Thanks!

Phil

Yeah you can do that. See here: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

If you don't want to parse it yourself you could try using the Properties collection using something like the following:

Dts.Connections("Errors").Properties("Format").GetValue(o)

Unfortunately that doesn't actually work cos I don't know what you're supposed to supply to the GetValue() method and the docs are a bit thin. But you can havea go with it if you like.

|||

Thanks, Jamie. You're right - I couldn't make it work with GetValue. However I was able to skin this particular cat thusly:

Dim _dbCxcn As String
Dim _inx As Integer
Dim _dbServer As String
Dim _dbCatalog As String

_dbCxcn = Dts.Connections("My_OLEDB_Connection").ConnectionString
_inx = InStr(_dbCxcn, "Data Source=")
_dbServer = Mid(_dbCxcn, _inx + 12, InStr(_inx, _dbCxcn, ";") - (_inx + 12))
_inx = InStr(_dbCxcn, "Initial Catalog=")
_dbCatalog = Mid(_dbCxcn, _inx + 16, InStr(_inx, _dbCxcn, ";") - (_inx + 16))

Then I constructed the email message using the _dbServer and _dbCatalog variables.

Thanks again!

Phil

Getting information dependant upon primary & foreign key

Hi All,

It seems I have been requested to carry out a complex query and the best way I think I can do this is with the use of a stored procedure. The problem is that I am not quite sure whether my SP is stated correctly and also how I would go about stating the SP in my VB.net code!

I would be ever so grateful if somebody could look over my SP code and possibly recommend a way of stating my code. My ability is limited so I would appreciate it if examples could be used with possible relations to my problem.

The Problem?

Tables:

1.tblRisk: Ref(pk), subject, status(fk), staff(fk),Dept(fk)

2.tblDept:Ref(pk), Department

The SP should state that Department should appear as the end result of the query when the page is loaded. So when a row is selected in tblRisk, dependant upon what the Dept is in that table, it then populates the department in which it is associated with from tblDept. I have left the SP below.

Many Thanks,

Kunal

CREATE PROCEDURE dbo.ShowMe @.yourInputValue INT
AS
SELECT tblDept.Department
FROM tblDept JOIN tblRisk
ON tblDept.Ref = tblRisk.Dept
WHEREtblDept.Ref = @.yourInputValue
RETURN 0
GO

You stored procedure is ok

You can use SqlCommand to call stored procedures in your ASP .NET application, just set the CommandType property of the SqlCommand to CommandType.StoredProcedure and pass proper parameter(s) to the stored procedure.

Getting events upon DB changes (NOT SCHEDULED)

Hi.

i'm trying to set an Event Provider that will provide me with events when a specific data changes in a specific table in the DB.

I've noticed that the built-in SQL Provider polls the DB using a schedule and that doesn't help me.

I'm thinking of implementing a customed event provider but I'm looking for a way to get events upon DB changes without using an interval (a continues event provider).

is there any way to do it OTHER THAN using a QueryNotification ?

if the best way is indeed using QueryNotification - is there a way of using it with the notification services ? and if anyone had done it - any documentation / code examples / links would be highly appreciated !

Thanks !

I posted an response over in the newsgroups.

http://groups.google.co.il/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/95afce139ab9ffa1/26e0ccda6b6c4921?hl=en#26e0ccda6b6c4921

Joe|||

Joe

I have read the info about the NSEventBeginBatchEvent<ClassName>, NSEventWriteEvent<ClassName> and the NSEventFlushBatchEvent<ClassName>

what I was thinking of doing is create a trigger on the table which i want to monitor. This trigger should call to a SP that takes parameters (the parameters are the event fields). The triggered SP should then call the NSEvent<XXX>Batch SP's

However, when i want to define the Event Provider as a Hosted Provider in the Providers section of the ADF file, it requiers an Assembly file.

I don't see the need of such an Assembly file since all events are generated directly from the DB (by the SP called by the trigger).

should i just create an "empty" Class that implements the iEventProvider interface but actually does nothing ?

help would be highly appreciated !

Best regards, Elad

|||HostedProviders work within and are subject to the NS service. NonHostedProviders work outside that service. So in this case, you'll want to declare a NonHostedProvider.

HTH...

Joe