Dataset Destination

Library references

• System
• System.Data
• Microsoft.SqlServer.TxScript
• Microsoft.SqlServer.PipelineHost
• Microsoft.SqlServer.DTSPipelineWrap
• Microsoft.SqlServer.DTSRuntimeWrap
• Microsoft.SqlServer.ManagedDTS
• CozyRoc.SSISPlus
• System.XML

ScriptMain

' CozyRoc SQL Server Integration Services user script component ' ' Copyright (c) 2006-2009 CozyRoc LLC ' ' Permission is hereby granted, free of charge, to any person ' obtaining a copy of this software and associated documentation ' files (the "Software"), to deal in the Software without ' restriction, including without limitation the rights to use, ' copy, modify, merge, publish, distribute, sublicense, and/or sell ' copies of the Software, and to permit persons to whom the ' Software is furnished to do so, subject to the following ' conditions: ' ' The above copyright notice and this permission notice shall be ' included in all copies or substantial portions of the Software. ' ' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, ' EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES ' OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND ' NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT ' HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, ' WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING ' FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR ' OTHER DEALINGS IN THE SOFTWARE. Imports System Imports System.ComponentModel Imports System.Data Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports CozyRoc.SqlServer.SSIS.Attributes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <ValidateProperties("Validate")> _ Public Class ScriptMain Inherits UserComponent ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer) Dim dataRow As DataRow = m_table.NewRow() Dim colsCount As Integer = m_idxIn.Length For colIndex As Integer = 0 To colsCount - 1 dataRow(m_idxOut(colIndex)) = Row.Buffer(m_idxIn(colIndex)) Next Call m_table.Rows.Add(dataRow) End Sub ' Input_ProcessInputRow ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub PreExecute() Call MyBase.PreExecute() ' Get input columns index. m_idxIn = GetInputIdx_() ' Setup output. Dim dataset As DataSet = GetDataSet_() m_table = GetTable_(dataset) m_idxOut = SetupColumns_(m_table) End Sub ' PreExecute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean = True Try If String.IsNullOrEmpty(Me.DataSetVariable) Then Throw New Exception("Select dataset variable.") End If Catch ex As Exception result = False errMessage = ex.Message End Try Validate = result End Function 'Validate ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub OnInputPathAttached(ByVal inputID As Integer) Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0) Dim virtualInput As IDTSVirtualInput90 = input.GetVirtualInput() Call input.InputColumnCollection.RemoveAll() For Each column As IDTSVirtualInputColumn90 In virtualInput.VirtualInputColumnCollection Call virtualInput.SetUsageType(column.LineageID, DTSUsageType.UT_READONLY) Next End Sub ' OnInputPathAttached #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Select variable containing destination dataset object.")> _ <Variable()> _ Public Property DataSetVariable() As String Get DataSetVariable = m_datasetVariable End Get Set(ByVal value As String) m_datasetVariable = value End Set End Property ' DataSetVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Specifies the table name in dataset (optional).")> _ Public Property TableName() As String Get TableName = m_tableName End Get Set(ByVal value As String) m_tableName = value End Set End Property ' TableName #End Region ' Properties #Region "Internals" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get selected input columns index. Private Function GetInputIdx_() As Integer() Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0) Dim colsCount As Integer = input.InputColumnCollection.Count Dim result As Integer() = New Integer(colsCount - 1) {} For colIndex As Integer = 0 To colsCount - 1 Dim inputCol As IDTSInputColumn90 = input.InputColumnCollection(colIndex) result(colIndex) = Me.HostComponent.BufferManager.FindColumnByLineageID( _ input.Buffer, _ inputCol.LineageID) Next GetInputIdx_ = result End Function ' GetInputIdx_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get dataset object. Private Function GetDataSet_() As DataSet Dim result As DataSet Dim vars As IDTSVariables90 Call Me.VariableDispenser.LockOneForWrite(Me.DataSetVariable, vars) Try Try result = CType(vars(Me.DataSetVariable).Value, DataSet) Catch ex As Exception ' Dataset not yet initialized. result = New DataSet() vars(Me.DataSetVariable).Value = result End Try Finally Call vars.Unlock() End Try GetDataSet_ = result End Function ' GetDataSet_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get dataset table. Private Function GetTable_(ByVal dataSet As DataSet) As DataTable Dim result As DataTable If String.IsNullOrEmpty(Me.TableName) Then ' Not specified. If dataSet.Tables.Count > 0 Then result = dataSet.Tables(0) Else result = New DataTable() Call dataSet.Tables.Add(result) End If Else ' Specified. If dataSet.Tables.Contains(Me.TableName) Then result = dataSet.Tables(Me.TableName) Else result = New DataTable(Me.TableName) Call dataSet.Tables.Add(result) End If End If GetTable_ = result End Function ' GetTable_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Setup dataset table columns based on selected input columns. Private Function SetupColumns_(ByVal table As DataTable) As Integer() Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0) Dim colsCount As Integer = input.InputColumnCollection.Count Dim result As Integer() = New Integer(colsCount - 1) {} For colIndex As Integer = 0 To colsCount - 1 Dim inputCol As IDTSInputColumn90 = input.InputColumnCollection(colIndex) ' Setup data column. Dim dataCol As DataColumn If table.Columns.Contains(inputCol.Name) Then dataCol = table.Columns(inputCol.Name) Else dataCol = table.Columns.Add(inputCol.Name) End If result(colIndex) = dataCol.Ordinal Next SetupColumns_ = result End Function ' SetupColumns_ #End Region ' Internals #Region "Attributes" Private m_datasetVariable As String Private m_tableName As String Private m_table As DataTable Private m_idxIn As Integer() Private m_idxOut As Integer() #End Region ' Attributes End Class ' ScriptMain