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