Hi there,

I am writing a custom task in c++. In the UI component I allow the
user to select an existing coonnection that the custom task should use
to perfrom its operation.

This is done by populating a drop-down list with the names of existing
connection so that a selection can be made. In the execute method of
the customtask I then want to use this connection to run some tsql
against.

However although I can get the connection from its collection their
does not appear to be much I can do with it. AcquireConnection does
not give one a database connection to issue tsql against.

I have also tried building up my own oledb string using the properties
of the connection object but this has a problem as the GetPassword
method returns a null pointer no matter what.

Is there a way to use a connection object in the customtask? It seems
to be a pretty useless object except in transformations. If I can't
use a connection object how can I get the saved password or at least
get a valid connection string from an exisitng connection?>

AquireConnection returns an IUnknown of a IDBSession, so you have to cast it

STDMETHODIMP CDtsTask::Execute(

/* [in] */ IDispatch *pPackage,

/* [in] */ IDispatch *pPackageEvents,

/* [in] */ IDispatch *pPackageLog,

/* [out][in] */ LONG *pTaskResult)

{

USES_CONVERSION; // Needed for functions like A2W, OLE2T, etc.

HRESULT hr = NOERROR;

ConnectionPtr pConn;

IUnknown * pUnkSession = NULL;

try {

_Package2Ptr pPack(pPackage);

ConnectionsPtr pConns = pPack->Connections;

// For purpose of test, assume a connection named Conn1

_variant_t connName("Conn1");

pConn = pConns->Item(connName);

// Test QI

IDispatchPtr pidsp = pConn;

pConn = pitj;

IUnknownPtr piunk = pConn;

pitj = piunk;

pConn = piunk;

// Get the connection

// AcquireConnection does not addref, so don't release

pUnkSession = pConn->AcquireConnection(m_bstrName);

IDBCreateCommandPtr pIDBCreateCommand = pUnkSession;

ICommandTextPtr pICommandText;

if (FAILED(hr = pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText,
(LPUNKNOWN *)&pICommandText)))

{

throw _com_error(hr);

}


if (FAILED(hr = pICommandText->SetCommandText(DBGUID_DBSQL,

OLESTR("INSERT INTO [HasRowAdded]([RowAdded]) VALUES(getdate())")

))) {

throw _com_error(hr);

}

long cRowsAffected = 0;

if (FAILED(hr = pICommandText->Execute(NULL, IID_NULL, NULL, &cRowsAffected,
NULL))) {

throw _com_error(hr);

}

*pTaskResult = DTS::DTSTaskExecResult_Success;



}

catch (_com_error e)

{

*pTaskResult = DTS::DTSTaskExecResult_Failure;

}

if (pUnkSession) {

pConn->ReleaseConnection();

// AcquireConnection does not addref, so don't release

//pUnkSession->Release();

}

return hr;

}

GertD@SQLDev.Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.

"Darren Green" <darren.green@reply-to-newsgroup-only.uk.com> wrote in
message news:Pq03jTWyblV$Ewe$@sqldts.com...
[quoted text, click to view]

In article <4865fa37.0308300412.28189a44@posting.google.com>, MXC
<mxc@magicmail.co.za> writes
[quoted text, click to view]

I believe you are on the right track, but I'm afraid I cannot offer any
specific advice since I do not know C++. See this link for my
confirmation reason-

http://tinyurl.com/m53t

[quoted text, click to view]

That is by design. Since one of the early SQL 7.0 SPs this property is
write-only for security reasons.


[quoted text, click to view]

I do VB, so I have no way of ever getting the connection. For any tasks
that need it I capture my own connection details, but as per Euan's post
I linked to above, in C++ this can be done.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com