ActiveX Script in DTS

[navshiv]

I have used the following code which somebody kindly posted on this forum a while back. This works perfectly for me but is there a way of not having to hardcode username and password (see cn.open line below)? For security reasons I do not think I will be able to put this code live with a username and password visible. Any ideas? Thanks.

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
dim cn 'connection object
dim rs 'recordset object
dim str 'sql string
dim no_recs 'record count

'create objects and connect to sql
set cn = createobject("ADODB.Connection")
set rs = createobject("ADODB.Recordset")
cn.provider="sqloledb"
cn.open "SERVER", "USERNAME", "PASSWORD"

'get the count for the source table
str = "select count(*) from TABLE"
set rs = cn.execute(str)
no_recs = rs.fields(0).value

'close objects and set to nothing
cn.close
set rs=nothing
set cn=nothing


'default to failure
x = DTSTaskExecResult_Failure

'if records exist, continue with success
if no_recs <> 0 then x = DTSTaskExecResult_Success

Main = x
End Function

 

[SimeonJ]

Try this... it will pop up a input box for the executor to enter a username and then a password.

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
dim cn 'connection object
dim rs 'recordset object
dim str 'sql string
dim no_recs 'record count
dim UsrName 'User Name
dim Pwd 'Password


'Enter UserName
UsrName = inputbox ("Please Enter UserName","Enter Username")

'Enter Password
Pwd = inputbox ("Please Enter Password","Enter Password")

'create objects and connect to sql
set cn = createobject("ADODB.Connection")
set rs = createobject("ADODB.Recordset")
cn.provider="sqloledb"
cn.open "SERVER", UsrName, Pwd

'get the count for the source table
str = "select count(*) from TABLE"
set rs = cn.execute(str)
no_recs = rs.fields(0).value

'close objects and set to nothing
cn.close
set rs=nothing
set cn=nothing


'default to failure
x = DTSTaskExecResult_Failure

'if records exist, continue with success
if no_recs <> 0 then x = DTSTaskExecResult_Success

Main = x
End Function