SSIS Script Destination component: как сделать потоковый UPDATE, EXEC и т.д.

Предположим, нам необходимо производить что-то сложнее INSERT в Data flow task. DTS 2000 позволял такое, но в SSIS 2005-2008 к сожалению это невозможно. OleDb destination на первый взгляд позволяет использовать SQL Command, но не поддерживает параметры, поэтому передать что-то туда нельзя.
Выход - использовать Script Component в Data Flow Task в качестве Destination. Способы различаются для ADO.NET connection и OleDb connection (первый вариант более рекомендуемый, потому что он позволяет использовать текущее соединение и транзакцию).


Добавляем внутри Data Flow Task трансформацию Script Component, выбираем использование его в качестве destination:

В моем случае я добавил два destination для ADO.NET и OleDb соединения соответственно.

Выбираем язык скрипта (я использовал VB.NET) и добавляем переменные из SSIS package, которые мы хотим сделать доступными в скрипте:

Задаем поля из потока данных, которые будут доступны скрипту:

Задаем connection, который будет доступен скрипту:

Далее, в случае использования ADO.NET connection, текст скрипта будет таким:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.SqlClient

<...>

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim SchoolID As Integer = Me.Variables.SchoolID ' так мы получаем доступ к переменным
Dim StudentID As String = Row.StudentId ' так - к полям из потока данных
Dim Usin As String = Row.USIN
Dim connMgr As ConnectionManagerAdoNet
Dim sqlConn As SqlConnection
'Get the connection manager we have created in the package
connMgr = Me.Connections.USINConnection ' так - к соединениям
sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
'подхватили соединение
Dim sqlCmd As New SqlCommand("EXEC up_usin_log_validation_error @user_id='synchronise process',@school_id=" & CStr(SchoolID) & ",@school_student_id='" & StudentID & "',@usin='" & Usin & "',@error_type='SYN_DIFF_USIN',@error_text='USIN is different in SAS database and USIN database'")
SqlCmd.Connection = sqlConn
sqlCmd.ExecuteNonQuery()
'Отпустили соединение
connMgr.ReleaseConnection(sqlConn)
'
End Sub


В случае OleDb несколько иначе:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient

<...>

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
Dim SchoolID As Integer = Me.Variables.SchoolID ' получаем переменные
Dim StudentID As String = Row.Studentid ' данные из потока
Dim Usin As String = Row.USIN
Using cn As System.Data.OleDb.OleDbConnection = _
New System.Data.OleDb.OleDbConnection(Me.Connections.SASConnection.ConnectionString) ' вместо соединения, как в предыдущем примере, мы берем только его connection string и открываем собственное. Имейте в виду, что если это было SQL Authentication, пароль передан не будет.
cn.Open()
Dim cmd As System.Data.OleDb.OleDbCommand = cn.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "UPDATE Student SET UniversalIdentificationNumber='" & Usin & "' WHERE Code='" & StudentID & "'"
cmd.ExecuteNonQuery()
cn.Close()
End Using
''
End Sub

End Class

Соответственно, в вышеперечисленных примерах можно использовать любые SQL команды и логику любой сложности.

Источники:
http://www.codeproject.com/KB/database/SSIS_destination_script.aspx
http://blogs.msdn.com/b/mattm/archive/2008/08/22/accessing-oledb-connection-managers-in-a-script.aspx (тут интересный пример, показывающий, как подхватить активное OleDb соединение без создания нового, но у меня он не сработал)
http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm
http://msdn.microsoft.com/en-us/library/ms136018.aspx
http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/442515bd-6b52-4236-bd11-398ac70a1b2c
http://msdn.microsoft.com/en-us/library/ms135927.aspx#Y375