_
_
_
_
_
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