]]> _ _ _ Public Class ScriptMain Inherits UserComponent ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub CreateNewOutputRows() Dim output As IDTSOutput90 = 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 IDTSOutputColumn90 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 IDTSOutput90 = 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 IDTSVariables90 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 IDTSOutput90 = 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 IDTSOutputColumn90 = 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 IDTSOutputColumn90, _ 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 ]]> ScriptComponent_0c0feb5a363d4682a1b1745379d7ce1f true CozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34ea