WEB开发网
开发学院数据库MSSQL Server 数据仓库项目中的IP转换成地区 阅读

数据仓库项目中的IP转换成地区

 2008-11-24 10:10:52 来源:WEB开发网   
核心提示: 脚本组件里面的代码Code1' Microsoft SQL Server Integration Services user script component2' This is your new script component in Microsoft Visual B

脚本组件里面的代码

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了。

附件里面我提供了这个包文件和地区维度表数据的下载,希望能够给朋友们提供一点微薄的帮助。

上一页  1 2 

Tags:数据 仓库 项目

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接