16,554
社区成员
发帖
与我相关
我的任务
分享
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Create a table, add columns and name
' them the same as they are in the
' database table they are updating
Dim table As New DataTable()
table.Columns.Add("ItemCode")
table.Columns.Add("ItemDesc")
'Load the table with 100,000 rows!
For i As Integer = 1 To 100000
table.Rows.Add(i, "Item " & i) : Next
'Create a new StopWatch to measure the
' amount of time it takes to perform the
' insert
Dim swatch As New Stopwatch()
swatch.Start()
'Configure the connection string
Dim cnString As String = _
"Data Source=localhost;" & _
"Initial Catalog=Catalog;" & _
"Integrated Security=True;"
'Create a new SqlBulkCopy class
Dim bcp As New SqlClient.SqlBulkCopy(cnString, _
SqlClient.SqlBulkCopyOptions.KeepNulls)
'You MUST specifiy the table you are going to fill
bcp.DestinationTableName = "Items"
'Increase the timeout to a more appropriate time
' for the amount of data your are inserting.
' The default is 30 seconds
bcp.BulkCopyTimeout = 60
'Write the data to the SQL Server Database table
bcp.WriteToServer(table)
'Close the instance
bcp.Close()
'Stop the Stopwatch!
swatch.Stop()
MsgBox("Done! Elapsed Milliseconds: " & _
swatch.ElapsedMilliseconds)
End Sub
使用存储过程传递参数
通过DTEXEC命令行适用工具来执行SSIS包即可. 参考:
create proc ssisexec_up @param1 nvarchar(100) as begin
DECLARE @sqlcmd nvarchar(2000)
DECLARE @returncode int
SET @sqlcmd ='dtexec /f "C:\UpsertData.dtsx" /SET \package.variables[myvariable].Value;'+ltrim(@param1)+ ' '
EXEC @returncode = xp_cmdshell @sqlcmd end