May 27, 2010 at 12:19 am
Hello,
Any links on how to write a VB.NET code to connect to SQL Server DB and write sql code to fetch data from tables created in SQL Server itself?
Rgds,
Rex
Rex Smith
May 27, 2010 at 1:28 am
I'm sure that we can find something.
Can you give us an idea of what you're doing and why it has to be in script?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 27, 2010 at 3:42 am
Hello,
Actually my requirement was to process files that are not processed. I will delete such processed files which are stored in a table after successful processing.
I have written some logic here. You can give me your comment on that.
Dim dia As New IO.DirectoryInfo("C:\seetesh")
Dim aryFi As IO.FileInfo() = dia.GetFiles("*.txt")
For Each fi In aryFi
strFileSize = (Math.Round(fi.Length / 1024)).ToString()
strqry = "select count(filename) from FileUpload where filename like '" + fi.Name + "'"
command = New SqlClient.SqlCommand(strqry, Con)
DR = command.ExecuteReader()
While DR.Read()
filecounter = DR(0).ToString
filecnt = Integer.Parse(filecounter)
MsgBox(String.Format("{0}", filecounter))
If filecnt > 0 Then
fi.Delete()
End If
End While
DR.Close()
Next
Rgds,
Rex
Rex Smith
May 28, 2010 at 9:11 am
I have never had occasion yet to connect to a database from within a Script Task, but I decided to spend a little time to see how it's done. I used this article to guide me http://www.bimonkey.com/2009/05/the-ado-net-source-and-sql-in-the-script-task/
So first you need to set up an ADO.NET connection in your SSIS Connection manager. Apparently ADO.Net is the way to go, it looks like OLEDB might be used but from what I saw it gets messy.
So here is a little bit of code that connects to the PUBS database on a SQL 2000 server using an SSIS connection named Pubs and display a couple of records:
Public Sub Main()
Dim cn As SqlClient.SqlConnection
Dim cmd As SqlClient.SqlCommand
Dim dr As SqlClient.SqlDataReader
Dim sqltext As String = "select top 2 title from pubs.dbo.titles"
cn = DirectCast(Dts.Connections("Pubs").AcquireConnection(Nothing), SqlClient.SqlConnection)
cmd = New SqlCommand(sqltext, cn)
dr = cmd.ExecuteReader()
While DR.Read()
MsgBox(String.Format("{0}", DR(0).ToString))
End While
Dts.TaskResult = Dts.Results.Success
End Sub
It looks like the main trick here is to cast the SSIS Connection as a SQLConnection. From there it's pretty standard stuff.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply