6,129
社区成员
发帖
与我相关
我的任务
分享
Imports System.Data.SqlClient
...
Public Class ScriptMain
Inherits UserComponent
Dim connMgr As IDTSConnectionManager100
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim sqlParam As SqlParameter
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
connMgr = Me.Connections.MyADONETConnectionManager
sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
End Sub
Public Overrides Sub PreExecute()
sqlCmd = New SqlCommand("INSERT INTO Person.Address2(AddressID, City) " & _
"VALUES(@addressid, @city)", sqlConn)
sqlParam = New SqlParameter("@addressid", SqlDbType.Int)
sqlCmd.Parameters.Add(sqlParam)
sqlParam = New SqlParameter("@city", SqlDbType.NVarChar, 30)
sqlCmd.Parameters.Add(sqlParam)
End Sub
Public Overrides Sub MyAddressInput_ProcessInputRow(ByVal Row As MyAddressInputBuffer)
With sqlCmd
.Parameters("@addressid").Value = Row.AddressID
.Parameters("@city").Value = Row.City
.ExecuteNonQuery()
End With
End Sub
Public Overrides Sub ReleaseConnections()
connMgr.ReleaseConnection(sqlConn)
End Sub
End Class
参考
Override the AcquireConnections method to connect to the external data source。例子里是ado.net,不过在
[code=VB.NET]
Dim cm As ConnectionManager
Dim cmParam As Wrapper.IDTSConnectionManagerDatabaseParameters90
Dim conn As OleDb.OleDbConnection
cm = Dts.Connections("oledb")
cmParam = CType(cm.InnerObject, Wrapper.IDTSConnectionManagerDatabaseParameters90)
conn = CType(cmParam.GetConnectionForSchema(), OleDb.OleDbConnection)
Dim sqlTerm As String
Dim sr As IO.StreamReader
Dim sw As IO.StreamWriter
Dim content As String
Dim qryPath As String
Dim batPath As String
'Set the standard template location, which will be overwritten with the final location
qryPath = "your query file path"
batPath = "your batch file path"
' Build a sql query file per request
sqlTerm = " AND EXTRACT(Year from OTINSTALL.INSTALL_DATE) = " + CStr(y) + " AND EXTRACT(MONTH from OTINSTALL.INSTALL_DATE) = " + CStr(m)
sr = New IO.StreamReader(String.Format("{0}.sql", qryPath))
content = sr.ReadToEnd()
content = content + sqlTerm
sr.Close()
qryPath = Regex.Replace(qryPath, "Templates", "Queries")
sw = New IO.StreamWriter(String.Format("{0}{1}.sql", qryPath, timeSlice))
sw.Write(content)
sw.Close()