Connecting to the database via an OLEDB connection manager within a script task -- sql server dts

John Hardin
9/19/2006 4:01:59 PM

Folks:

We have the need to write a lot of SSIS script tasks that talk directly to
the database. We use OLEDB connections, and log into the database using
database users rather than Windows integrated security.

Within a script task I'm able to create a new OLEDB connection object using
the connection string pulled from the connection manager, but as we are
using database passwords rather than Windows integrated security, and as the
package strips the password out of the connection string, I need to provide
a password. Where do we get the database password that the existing
connection is using?

Alternatively, how would you ask the OLEDB database connection manager to
provide a connection object? The tasks that SSIS provides have to get a
connection from the connection manager - how do *they* do it?

We'd rather not use an ADO.NET:OLEDB connection manager because Slowly
Changing Dimension tasks require a native OLEDB connection manager, and
having both an ADO.NET:OLEDB and a native OLEDB connection manager for the
same database forces having to enter the same configuration information in
two places, exposes the risk that they will be configured differently, and
makes us look stupid.

Can anybody suggest an elegant way to do this?

Thanks.

--
John Hardin KA7OHZ
Senior Applications Developer, RetailCRM Development
web: http://www.epicor.com
voice: (425) 672-1304
fax: (425) 672-0192
email: <jhardin@epicor.com>
EPICOR|CRS Retail Solutions Division
3400 188th Street SW, Suite 185
Lynnwood, WA 98037 USA
Worldwide Headquarters 18200 Von Karman, Suite 1000, Irvine CA 92612 USA
------------------------------------------------------------------------
The first time I saw a bagpipe, I thought the player was torturing an
octopus. I was amazed they could scream so loudly.
------------------------------------------------------------------------

kiran
9/20/2006 1:14:09 AM

hi John,
The soln is given below.
------------------------------------------------------------------------------
" Public Sub Main()
'
' Add your code here
'

Dim vs As Variables
Dts.VariableDispenser.LockForRead("g_str_server")
Dts.VariableDispenser.LockForRead("g_str_username")
Dts.VariableDispenser.LockForRead("g_str_password")
Dts.VariableDispenser.LockForRead("g_str_databasename")
Dts.VariableDispenser.LockForRead("g_str_connectiontype")
Dts.VariableDispenser.GetVariables(vs)
Dts.Connections("MD").ConnectionString = "Data Source=" &
vs("g_str_server").Value.ToString() & ";User ID=" &
vs("g_str_username").Value.ToString() & ";Pwd=" &
vs("g_str_password").Value.ToString() & ";Initial Catalog=" &
vs("g_str_databasename").Value.ToString() & ";Provider=SQLNCLI.1;Auto
Translate=False;"
Dts.Connections("MD").AcquireConnection(Nothing)
Dts.TaskResult = Dts.Results.Success
End Sub

--- NOTE :
2> Create the OLE DB Cnnection In conn manager (HEre I have named it ad
MD for that OLEDB Conn) .
1> Variables prifixed with "g_" are global variables and MD in
"Dts.Connections("MD")" is the name of the OLEDB Connection manager
Defined by the developer in Connection managers.

-----------------------------------------------------------------------------------------------------------------

[quoted text, click to view]

John Hardin
9/21/2006 9:33:42 AM

[quoted text, click to view]

I think you interpreted the reverse of what I was asking. It appears from
the above that you are configuring an existing connection manager from
connection settings in package variables.

What I need is this: The package already has an OLEDB connection manager
(not necessarily talking to SQL Server!) that is configured via the standard
SSIS XML configuration file mechanism, and works for all of the non-script
tasks that interact with the database (e.g. an OLEDB query task).

What I need is to be able to look at that connection manager from within a
script task, and create a connection that talks to the same database so that
the script task can perform database operations.

I don't want to take the connection configuration information from package
variables. That's the hack I'm using right now, and I'd like something more
elegant, if possible.

Unfortunately, looking at the connection manager's ConnectionString property
isn't going to work as the database password is stripped off it.

Also unfortunately, according to the documentation AcquireConnection() does
not work at all for OLEDB connection managers, only ADO.NET connection
managers. How are you getting it to work in your example? (Remember to
distinguish between an OLEDB connection manager and an ADO.NET connection
manager that happens to be talking to an OLEDB data source...)

And that code looks like scripting for a control flow script task. The Dts
object doesn't exists in data flow script tasks, and that's where I need to
do this. Sorry for not making that clear in my initial post.

Thanks for your suggestion, though!

--
John Hardin KA7OHZ
Senior Applications Developer, RetailCRM Development
web: http://www.epicor.com
voice: (425) 672-1304
fax: (425) 672-0192
email: <jhardin@epicor.com>
EPICOR|CRS Retail Solutions Division
3400 188th Street SW, Suite 185
Lynnwood, WA 98037 USA
Worldwide Headquarters 18200 Von Karman, Suite 1000, Irvine CA 92612 USA
------------------------------------------------------------------------
The first time I saw a bagpipe, I thought the player was torturing an
octopus. I was amazed they could scream so loudly.
------------------------------------------------------------------------

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 about sql server dts

sql server dts : Connecting to the database via an OLEDB connection manager within a script task