_ _ _ _ _ Public Class ScriptMain Inherits UserComponent ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub CreateNewOutputRows() Dim output As IDTSOutput100 = Me.ComponentMetaData.OutputCollection(0) Dim colsCount As Integer = output.OutputColumnCollection.Count Dim colIndex As Integer = 0 ' Setup column indexes. Dim colsIdx As New Hashtable For Each column As IDTSOutputColumn100 In output.OutputColumnCollection Call colsIdx.Add( _ column.Name, _ Me.HostComponent.BufferManager.FindColumnByLineageID( _ output.Buffer, _ column.LineageID)) Next Dim scope As ManagementScope = CType( _ Me.Connections.Connection.AcquireConnection(Nothing), _ ManagementScope) Try Dim rawData As ManagementObjectCollection = GetQueryResult_(scope, False) For Each rawRow As ManagementObject In rawData ' Get packed row values. Dim packed_values() As Object = New Object(colsCount - 1) {} colIndex = 0 For Each column As DictionaryEntry In colsIdx packed_values(colIndex) = rawRow(CStr(column.Key)) colIndex += 1 Next Dim rows As List(Of Object()) = UnwindRow_(packed_values) For Each row As Object() In rows Call Me.OutputBuffer.AddRow() colIndex = 0 For Each column As DictionaryEntry In colsIdx Dim name As String = CStr(column.Key) Dim index As Integer = CInt(column.Value) Dim value As Object = row(colIndex) If value Is Nothing Then Call Me.OutputBuffer.Buffer.SetNull(index) Else Try Me.OutputBuffer.Buffer(index) = GetValue_( _ output.OutputColumnCollection(name), _ value) Catch ex As Exception Throw New Exception(String.Format( _ "Failed to set column '{0}'. {1}", _ name, _ ex.Message)) End Try End If colIndex += 1 Next Next Next Catch ex As Exception Call FireError_(ex.Message) Finally Call Me.Connections.Connection.ReleaseConnection(scope) End Try End Sub ' CreateNewOutputRows ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean Try If String.IsNullOrEmpty(Me.Connection) Then Throw New ApplicationException("Select WMI connection.") End If If String.IsNullOrEmpty(Me.Query) Then Throw New ApplicationException("Specify WQL query.") End If Dim output As IDTSOutput100 = MyBase.ComponentMetaData.OutputCollection(0) If output.OutputColumnCollection.Count = 0 Then ' Setup collection output columns. Call SetupOutputColumns_() End If ' Store connection information in the runtime connection collection, too. ' Cannot directly use RuntimeConnectionCollection in the property get/set ' because of issues with multi-threading. Me.ComponentMetaData.RuntimeConnectionCollection("Connection").ConnectionManagerID = Me.Connection result = True Catch ex As Exception result = False errMessage = ex.Message End Try Validate = result End Function 'Validate ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns list of current properties based on current state. Public Function GetProperties() As String() Dim result As New ArrayList Call result.Add("Connection") Call result.Add("IsQueryVariable") Call result.Add(IIf(Me.IsQueryVariable, "QueryVariable", "Query")) Return CType(result.ToArray(GetType(String)), String()) End Function ' GetProperties #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property Connection() As String Get Connection = m_connection End Get Set(ByVal value As String) m_connection = value End Set End Property ' Connection ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property IsQueryVariable() As Boolean Get IsQueryVariable = m_isQueryVar End Get Set(ByVal value As Boolean) If m_isQueryVar <> value Then m_isQueryVar = value m_query = String.Empty End If End Set End Property ' IsQueryVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property Query() As String Get Query = m_query End Get Set(ByVal value As String) m_query = value End Set End Property ' Query ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property QueryVariable() As String Get QueryVariable = Me.Query End Get Set(ByVal value As String) Me.Query = value End Set End Property ' QueryVariable #End Region ' Properties #Region "Internals" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private ReadOnly Property WmiConnectionType() As String() Get WmiConnectionType = New String() {"WMI"} End Get End Property ' WmiConnectionType ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns variable value. Private Function GetVariable_(ByVal varName As String) As Object Dim result As Object Dim vars As IDTSVariables100 Call Me.VariableDispenser.LockOneForRead(varName, vars) Try result = vars(varName).Value Finally Call vars.Unlock() End Try GetVariable_ = result End Function ' GetVariable_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' This function is based on the Microsoft's WMI Source sample code. ' http://sqlsrvintegrationsrv.codeplex.com/sourcecontrol/network/Show?projectName=SQLSrvIntegrationSrv&changeSetId=40668#348153 ' ' Unwind WMI row ( unwind array in any column ) ' we use the same approach to handle WMI arrays as WMI ODBC driver ' input row is unwinded into several output rows ' output rows should represent every possible combination of WMI arrays elements in different columns ' for details see msdn : ' http://msdn.microsoft.com/en-us/library/aa392328(VS.85).aspx#_hmm_mapping_wmi_arrays_to_odbc ' ' Here is an example : ' ' input row : { a1, {b1,b2}, {c1,c2,c3} } ' ' output rows : ' ' { a1 , b1, c1 } ' { a1 , b1, c2 } ' { a1 , b1, c3 } ' { a1 , b2, c1 } ' { a1 , b2, c2 } ' { a1 , b2, c3 } Private Function UnwindRow_(ByVal arr() As Object) As List(Of Object()) Dim result As New List(Of Object()) Dim buf() As Object = New Object(arr.Length - 1) {} Call UnwindRow_(arr, 0, buf, result) UnwindRow_ = result End Function ' UnwindRow_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub UnwindRow_( _ ByVal arr() As Object, _ ByVal pos As Integer, _ ByVal buf() As Object, _ ByVal result As List(Of Object())) While pos < arr.Length If Not arr(pos) Is Nothing AndAlso arr(pos).GetType().IsArray Then ' This is array. Unwind it. Dim inner As Array = CType(arr(pos), Array) If inner.Length = 0 Then ' empty array will be represented as null value inner = New Object(0) {} End If For Each obj As Object In inner buf(pos) = obj Call UnwindRow_(arr, pos + 1, buf, result) Next Return End If buf(pos) = arr(pos) pos += 1 End While Call result.Add(CType(buf.Clone(), Object())) End Sub ' UnwindRow_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub SetupOutputColumns_() Dim output As IDTSOutput100 = Me.ComponentMetaData.OutputCollection(0) Dim scope As ManagementScope = CType( _ Me.DesignConnections(Me.Connection).AcquireConnection(Nothing), _ ManagementScope) Try ' Get WMI columns. Dim objEnum As ManagementObjectCollection.ManagementObjectEnumerator = GetQueryResult_(scope, True).GetEnumerator() Call objEnum.Reset() Call objEnum.MoveNext() Dim props As PropertyDataCollection = objEnum.Current.Properties For Each prop As PropertyData In props Dim dtsType As DataType Dim length, precision, scale, codePage As Integer Call GetDtsTypeFromProperty_(prop, dtsType, length, precision, scale, codePage) Dim column As IDTSOutputColumn100 = output.OutputColumnCollection.[New]() column.Name = prop.Name column.Description = CStr(GetQualifier_(prop, "Description", String.Empty)) Call column.SetDataTypeProperties(dtsType, length, precision, scale, codePage) Next Finally Call Me.DesignConnections(Me.Connection).ReleaseConnection(scope) End Try End Sub ' SetupOutputColumns_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetQueryResult_( _ ByVal scope As ManagementScope, _ ByVal onlyMeta As Boolean) As ManagementObjectCollection ' Get query. Dim query As String = Me.Query If Me.IsQueryVariable Then query = GetVariable_(query).ToString() End If Dim result As ManagementObjectCollection Dim objQuery As New ObjectQuery(query) Dim searcher As New ManagementObjectSearcher(scope, objQuery) searcher.Options.PrototypeOnly = onlyMeta result = searcher.Get() Try Dim count As Integer = result.Count Catch ex As ManagementException If ex.ErrorCode = ManagementStatus.InvalidClass OrElse _ ex.ErrorCode = ManagementStatus.NotFound Then Throw New Exception("Invalid WQL query.") Else Throw End If End Try GetQueryResult_ = result End Function ' GetQueryResult_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get SSIS type from PropertyData. Private Sub GetDtsTypeFromProperty_( _ ByVal prop As PropertyData, _ ByRef dtsType As DataType, _ ByRef length As Integer, _ ByRef precision As Integer, _ ByRef scale As Integer, _ ByRef codePage As Integer) length = CType(GetQualifier_(prop, "MaxLen", 0), Integer) precision = 0 scale = 0 codePage = 0 Select Case prop.Type Case CimType.Boolean dtsType = DataType.DT_BOOL Case CimType.Char16, CimType.String dtsType = DataType.DT_WSTR If length = 0 Then length = 256 End If Case CimType.DateTime dtsType = DataType.DT_DBTIMESTAMP Case CimType.None dtsType = DataType.DT_NULL Case CimType.Object dtsType = DataType.DT_IMAGE Case CimType.Real32 dtsType = DataType.DT_R4 Case CimType.Real64 dtsType = DataType.DT_R8 Case CimType.SInt16 dtsType = DataType.DT_I2 Case CimType.SInt32 dtsType = DataType.DT_I4 Case CimType.SInt64 dtsType = DataType.DT_I8 Case CimType.SInt8 dtsType = DataType.DT_I1 Case CimType.UInt16 dtsType = DataType.DT_UI2 Case CimType.UInt32 dtsType = DataType.DT_UI4 Case CimType.UInt64 dtsType = DataType.DT_UI8 Case CimType.UInt8 dtsType = DataType.DT_UI1 Case Else Throw New Exception("Unhandled column type.") End Select End Sub ' GetDtsTypeFromProperty_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get qualifier from PropertyData. Private Function GetQualifier_( _ ByVal prop As PropertyData, _ ByVal qualName As String, _ ByVal defValue As Object) As Object Dim result As Object = defValue Try result = prop.Qualifiers(qualName).Value Catch ex As Exception End Try GetQualifier_ = result End Function ' GetQualifier_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetValue_( _ ByVal column As IDTSOutputColumn100, _ ByVal value As Object) As Object Dim result As Object = value Select Case column.DataType Case DataType.DT_WSTR result = CStr(result).Substring( _ 0, _ System.Math.Min(column.Length, CStr(result).Length)) Case DataType.DT_DBTIMESTAMP result = ManagementDateTimeConverter.ToDateTime(CStr(value)) Case DataType.DT_I8 If TypeOf result Is TimeSpan Then result = CType(result, TimeSpan).Ticks End If End Select GetValue_ = result End Function ' GetValue_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub FireError_(ByVal message As String) Dim cancel As Boolean Call Me.ComponentMetaData.FireError(0, "WMI Source", message, String.Empty, 0, cancel) End Sub ' FireError_ #End Region ' Internals #Region "Attributes" Private m_connection As String Private m_isQueryVar As Boolean Private m_query As String #End Region ' Attributes End Class ' ScriptMain ]]> {30D016F9-3734-4E33-A861-5E7D899E18F3};{F184B08F-C81C-45F6-A57F-5ABD9991F28F} Debug AnyCPU 8.0.30703 2.0 {49C11C1E-F77D-4536-AA3E-08CC493AFEA0} Library My Project ScriptComponent_0c0feb5a363d4682a1b1745379d7ce1f ScriptComponent_0c0feb5a363d4682a1b1745379d7ce1f v4.0 512 Binary On On true full false .\bin\Debug\ false true true prompt 4 false true .\bin\Release\ false false true prompt 4 False C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\CozyRoc.SSISPlus.2014.dll False False False False SSIS_SC120 ]]> _ Public Class OutputBuffer Inherits ScriptBufferPlus Public Sub New(ByVal Component As ScriptComponent, ByVal ObjectID As Integer, ByVal IsInput As Boolean, ByVal Buffer As PipelineBuffer, ByVal OutputMap As OutputNameMap) MyBase.New(Component, ObjectID, IsInput, Buffer, OutputMap) End Sub Public Overrides ReadOnly Property StaticInputColumns() As String() Get Return New String() {} End Get End Property Public Overrides ReadOnly Property StaticOutputColumns() As String() Get Return New String() {} End Get End Property Public Overloads Sub AddRow() MyBase.AddRow() End Sub Public Overloads Sub SetEndOfRowset() MyBase.SetEndOfRowset() End Sub Public Overloads Function EndOfRowset() As Boolean EndOfRowset = MyBase.EndOfRowset End Function End Class ]]> _ Public Class UserComponent Inherits ScriptComponentPlus Public Connections As New Connections(Me) Public Variables As New Variables(Me) Public OutputBuffer As OutputBuffer Public Overridable Sub FinishOutputs() End Sub Private Sub MarkOutputsFinished() If OutputBuffer IsNot Nothing Then OutputBuffer.SetEndOfRowset OutputBuffer = Nothing End If End Sub Public Overrides Sub PrimeOutput(ByVal Outputs As Integer, ByVal OutputIDs() As Integer, ByVal Buffers() As PipelineBuffer, ByVal OutputMap As OutputNameMap) For I As Integer = 0 To Outputs - 1 If OutputIDs(I) = GetOutputID(OutputMap, "Output") Then OutputBuffer = New OutputBuffer(Me, OutputIDs(I), False, Buffers(I), OutputMap) End If Next CreateNewOutputRows() FinishOutputs() MarkOutputsFinished() End Sub Public Overridable Sub CreateNewOutputRows() End Sub End Class Public Class Connections Dim ParentComponent As ScriptComponent _ Public Sub New(ByVal Component As ScriptComponent) ParentComponent = Component End Sub Public ReadOnly Property Connection() As IDTSConnectionManager100 Get Return ParentComponent.ComponentMetaData.RuntimeConnectionCollection("Connection").ConnectionManager End Get End Property End Class Public Class Variables Dim ParentComponent As ScriptComponent _ Public Sub New(ByVal Component As ScriptComponent) ParentComponent = Component End Sub End Class ]]> msBuild ScriptComponent_0c0feb5a363d4682a1b1745379d7ce1f ScriptComponent_0c0feb5a363d4682a1b1745379d7ce1f {92716031-67F9-4474-9A47-CEBAEF9A1863} ]]>CozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus.2014, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34eaScriptComponent_0c0feb5a363d4682a1b1745379d7ce1fVisualBasic