How to connect SQLServer2008 DB using script task(vbscript)

Question

How to connect SQLServer2008 DB using script task(vbscript)

jpangging

SSC Veteran

Points: 264
More actions
April 26, 2011 at 3:45 am

#147934

Hi,
I want to connect to SQ Server 2008 DB and do some SELECT queries.
The basic purpose is, i want to fetch a attribute value from one of the Table in database and store it in a variable.
The Provider being used here is Native OLE DB\SQL Server Native Client 10.0

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

jpangging SSC Veteran Points: 264 More actions · Answer 1

Hi,

I did some work on this and could connect to the database. But i am unable to fetch the result and store it in a variable.

Here is the vbscript code:

Public Sub Main()

Dim cs As String

Dim buildStr As String

Dim FileName As String = System.IO.Path.GetFileName(Dts.Variables("Name").Value.ToString())

MsgBox(FileName)

Dim s As String = Dts.Variables("Name").Value.ToString

MsgBox(s)

Dim fStream As New System.IO.FileStream(s, System.IO.FileMode.Open, System.IO.FileAccess.Read)

If System.IO.File.Exists(s) Then

cs = "SERVER=ABCD;DATABASE=TRIAL;User Id=USER;PASSWORD=MYPASS"

Dim conobj As New System.Data.SqlClient.SqlConnection(cs)

conobj.Open()

MsgBox("connection open")

Dim cmd As New System.Data.SqlClient.SqlCommand

buildStr = " Select Empd_name from Emp_det where Emp_id='A01' "

Dim abc As String = cmd.CommandText = buildStr

cmd.Connection = conobj

MsgBox(buildStr)

MsgBox(abc)

End If

fStream.Close()

Dts.TaskResult = ScriptResults.Success

End Sub

Here 'Name' is a SSIS variable having value 'A01'. In the above code i have hard coded this value. But actually i have to pass the value dynamically.Please suggest.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

I think this whole approach is overkill, you can do it with an EXEC SQL task using input and output variable mapping. About the only code needed is the SQL statement.

CEWII