数据仓库项目中的IP转换成地区
2008-11-24 10:10:52 来源:WEB开发网脚本组件里面的代码
Code
1' Microsoft SQL Server Integration Services user script component
2' This is your new script component in Microsoft Visual Basic .NET
3' ScriptMain is the entrypoint class for script components
4
5Imports System
6Imports System.Xml
7Imports System.Net
8Imports System.Data
9Imports System.Data.SqlClient
10Imports System.Math
11Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
12Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
13
14Public Class ScriptMain
15 Inherits UserComponent
16
17 Private dataSet As DataSet = New DataSet()
18
19 Public Overrides Sub 输入0_ProcessInputRow(ByVal Row As 输入0Buffer)
20
21 Dim GeographyKey As Int32 = 0
22 Dim ipcurrent As String = String.Empty
23
24 Try
25 If (Row.IPAddress_IsNull()) Then
26 Row.GeographyKey = -1
27 Return
28 End If
29
30 ipcurrent = Row.IPAddress
31
32 If (ipcurrent.Length = 0) Then
33 Row.GeographyKey = -1
34 Return
35 End If
36
37 Dim i As Integer
38 For i = 0 To dataSet.Tables("DimGeography").Rows.Count - 1
39 Dim dtRow As DataRow = dataSet.Tables("DimGeography").Rows(i)
40 Dim ipsections As String()
41 ipsections = dtRow("IPSections").ToString().Split(CChar(";"))
42
43 Dim ipsection As String
44 For Each ipsection In ipsections
45 If (IpBetween(ipcurrent, ipsection)) Then
46 GeographyKey = Convert.ToInt32(dtRow("GeographyKey").ToString())
47 Exit For
48 End If
49 Next
50 If (GeographyKey <> 0) Then
51 Row.GeographyKey = GeographyKey
52 Return
53 End If
54 Next
55 GeographyKey = -1
56 Row.GeographyKey = GeographyKey
57 Catch
58 GeographyKey = -1
59 Row.GeographyKey = GeographyKey
60 End Try
61
62 End Sub
63
64 Public Overrides Sub PreExecute()
65 MyBase.PreExecute()
66
67 Dim conn As OleDb.OleDbConnection
68 Dim adapter As OleDb.OleDbDataAdapter
69 Dim sql As String = "select GeographyKey,IPSections from DimGeography"
70
71 conn = New OleDb.OleDbConnection(Connections.DataCenterDW.ConnectionString + ";pwd=work8520")
72
73 adapter = New OleDb.OleDbDataAdapter(sql, conn)
74
75 Try
76 conn.Open()
77 adapter.Fill(dataSet, "DimGeography")
78 Catch ex As Exception
79
80 Finally
81 conn.Close()
82 End Try
83
84 End Sub
85
86 Private Function IpBetween(ByVal targetip As String, ByVal ipsection As String) As Boolean
87 Dim ipstart As String = ipsection.Split(CChar("-"))(0)
88 Dim ipend As String = ipsection.Split(CChar("-"))(1)
89
90 If (IPCompare.IsGreaterOrEqual(targetip, ipstart) And IPCompare.IsLessOrEqual(targetip, ipend)) Then
91 Return True
92 End If
93
94 Return False
95 End Function
96
97End Class
98
99
这样就OK了。
附件里面我提供了这个包文件和地区维度表数据的下载,希望能够给朋友们提供一点微薄的帮助。
更多精彩
赞助商链接