Script Component and OLEDB Connection

Question

 

Hi

I am using OLEDB Connection in script component. I get a login failure when I run the package. I use SQL Server authentication. Do you have to use windows authentication in script component? Thanks

 

Answer 1

No, you can use SQL authentication. What code are you using to get the connection?

 

Answer 2

I used

.Connection = New OleDbConnection(Connections.MyConnection.ConnectionString)

because Connections.MyConnection.ConnectionString  dose not include the password, it did not work. Now I am using

Connection = New OleDbConnection(Connections.MyConnection.ConnectionString + "Password=" + Variables.TorqueDWPwd + ";")

 

it works. But I am not sure if this is the best way to do

 

The ConnectionString is stored in the database table like Microsoft Project REAL. In Microsoft Project REAL, it uses windows authentication. I don't want to use windows authentication here.

 

Answer 3

You should use the MyConnection.AquireConnection method to get the connection object - you'll probably need to use an ADO.NET connection manager, otherwise you'll get a COM object back.

 

If you use AquireConnection, it will handle the password for you. Of course, I'm assuming that you do have the password being set through a configuration or that it is stored in the package.

 

You might check this article out for an overview of how to do this:

http://technet.microsoft.com/en-us/library/aa337080.aspx

 

Answer 4

You can use this code.

        Dim cmParams As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 = CType(DtsConvert.GetWrapper(Me.Connections.EtlLog).InnerObject, Wrapper.IDTSConnectionManagerDatabaseParameters100)
        Dim objConnection As OleDbConnection = CType(cmParams.GetConnectionForSchema(), OleDbConnection)

then you can use oldb Connection