ssis如何通过代码更新数据流源的sqlcommand
原因：安装系统后，无法通过sqlcommand来实现参数的使用。如果将代码设置为select * from t_table where column1 > ?无法识别参数。这个问题搞不定
解决方案：希望通过脚本组建或者数据流的事件脚本，通过脚本来进行字符串替换，实现参数的功能。
问题：如何在包的脚本组件中，通过代码引用已经定义的数据流源的sqlcommand属性，这样我就可以通过代码来实现参数查询的功能。或者在包的事件程序中，onPreExecute事件中处理这个任务。
知识缺陷：由于一直没有接触vb.net不知道如何通过代码来引用已有对象，成了我的痛。
希望不吝赐教！
[最优解释]
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)
然后override PreExecute 可以创建或配置sqlcommand
[/code]
[其他解释]
该回复于2012-02-22 13:03:27被版主删除
[其他解释]
参数无法识别是什么意思?
在参数那一栏选择这个参数的变量就可以了
[其他解释]
我现在的项目是从oracle extract数据，不过思想差不多。
用txt文件放置.sql的模板，然后用SSIS的vb或c# script去赋参数和调用.sql然后spool数据。
[其他解释]
现在的问题就是不知道如何通过vb来调用txt文件
请明确！
[其他解释]
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()
