download sample code
Beberapa bulan yang lalu saya pernah menanyakan di forum SQLServer-Indo mengenai bagaimana menggunakan Table-Valued Parameter di SSIS. Setelah nggak ada yang jawab (huhuhu...) akhirnya saya menemukan caranya sendiri. Kenapa saya ingin menggunakannya adalah karena saya ingin meng-execute stored procedure dengan TVP, dimana TVP nya adalah hasil transformasi di SSIS, lalu menyimpan nilai yang error ke file
Pada contoh yang saya buat, pertama-tama saya membuat 2 connection ke SQL Server. Yang pertama adalah OLEDB connection sedangkan yang kedua adalah ADO.NET connection
Lalu saya tambahkan satu variable dengan nama tvp dengan type data object
Kemudian saya buat DFT yang isinya transformasi data dengan destination Recordset Destination. Variable yang digunakan untuk menyimpan data di Recordset Destination adalah tvp.
Lalu saya buat DFT kedua, menghubungkan antara DFT pertama dan kedua. Saya gunakan Script Component sebagai source, menambahkan tvp pada property ReadOnlyVariables, menambahkan connection ke ADO.NET connection, menambahkan 3 input column yaitu name, quantity, dan error message (column yang ditambahkan sesuai dengan column yang terdapat pada input column recordset destination).
Pada script yang terdapat di ScriptComponent saya tambahkan script berikut:
C# |
private
SqlDataReader _reader;
private
SqlConnection _connection;
public
override
void PreExecute()
{
base.PreExecute();
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
DataTable dtTVP = new
DataTable();
adapter.Fill(dtTVP, this.Variables.tvp);
_connection = new
SqlConnection(this.Connections.ADONETConnection.ConnectionString);
_connection.Open();
SqlCommand command = new
SqlCommand("InsertProduct", _connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@product", dtTVP);
_reader = command.ExecuteReader();
}
public
override
void PostExecute()
{
base.PostExecute();
_connection.Close();
}
public
override
void CreateNewOutputRows()
{
while (_reader.Read())
{
Output0Buffer.AddRow();
Output0Buffer.name = _reader.GetString(0);
Output0Buffer.quantity = _reader.GetInt32(1);
Output0Buffer.errormessage = _reader.GetString(2);
}
Output0Buffer.SetEndOfRowset();
}
} |
VB |
Private _reader As SqlDataReader
Private _connection As SqlConnection
Public
Overrides
Sub PreExecute()
MyBase.PreExecute()
Dim adapter As
New OleDb.OleDbDataAdapter
Dim dtTVP As
New DataTable
adapter.Fill(dtTVP, Me.Variables.tvp)
_connection = New SqlConnection(Me.Connections.ADONETConnection.ConnectionString)
_connection.Open()
Dim command As
New SqlCommand("InsertProduct", _connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.AddWithValue("@product", dtTVP)
_reader = command.ExecuteReader()
End
Sub
Public
Overrides
Sub PostExecute()
MyBase.PostExecute()
_connection.Close()
End
Sub
Public
Overrides
Sub CreateNewOutputRows()
While _reader.Read()
Output0Buffer.AddRow()
Output0Buffer.name = _reader.GetString(0)
Output0Buffer.quantity = _reader.GetInt32(1)
Output0Buffer.errormessage = _reader.GetString(2)
End
While
Output0Buffer.SetEndOfRowset()
End
Sub |
Kemudian saya tambahkan Flat File Destination ke Data Flow dan menghubungkannya dengan Script Component.