April 26, 2011 at 3:45 am
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
April 26, 2011 at 4:45 am
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.
April 27, 2011 at 11:58 am
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply