Home | Print | Q/A | Guest | NewsLetter
Display context of search results Case-sensitive searching
FrontPageMicrosoftSQLServerMetaData › SSIS-날짜쪼개기
Database System
Data Warehouse
Data Analysis
Operating System
Open Source
Enterprise Architecture
Software Engineering
Process
Working Smart

SQL Server
PostgreSQL
Oracle
DB2
Teradata
MySQL
Performance Tuning
Programming

Link
Philosophy
Tools
Misc
주인놈
_
_
SideBar Edit

아.. 닝기리.. Try ~ Catch 썼더니만 5배는 느리다..ㅋ
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub 입력0_ProcessInputRow(ByVal Row As 입력0Buffer)
        '
        ' Add your code here
        '
        'Try
        Dim binIP(3) As Byte

        If Row.LoginDT = Row.LogoutDT Then

            If Row.IP_IsNull = True Then
                With Out01Buffer
                    .AddRow()
                    .AccountKey = Row.AccountKey 'CType(Asc(Left(Row.CN, 1)).ToString() & Asc(Right(Left(Row.CN, 2), 1)).ToString() & Right(Row.CN, 10), Int64)
                    .LoginTime = Row.LoginTime
                    .LogoutTime = Row.LogoutTime
                    .ServiceKey = Row.ServiceKey
                    .DateKey = Row.LoginDT
                End With
            Else
                With Out01Buffer
                    .AddRow()
                    .AccountKey = Row.AccountKey 'CType(Asc(Left(Row.CN, 1)).ToString() & Asc(Right(Left(Row.CN, 2), 1)).ToString() & Right(Row.CN, 10), Int64)
                    .LoginTime = Row.LoginTime
                    .LogoutTime = Row.LogoutTime
                    .ServiceKey = Row.ServiceKey
                    .DateKey = Row.LoginDT
                    .IP = Row.IP
                End With
            End If
            

        Else
            ' 날짜를 24시 기준으로 쪼개에 넣는다.
            ' 24시 이하

            Dim LogoutTime As Date
            Dim LoginTime As Date
            Dim AddDate As Date
            Dim i As Integer

            i = 0
            While (i <= Row.DayDiff)

                If i = 0 Then
                    AddDate = Row.LoginTime
                    LoginTime = AddDate
                    LogoutTime = New Date(AddDate.Year, AddDate.Month, AddDate.Day, 23, 59, 59, 999)
                ElseIf i = Row.DayDiff Then
                    AddDate = AddDate.AddDays(1)
                    LoginTime = New Date(AddDate.Year, AddDate.Month, AddDate.Day, 0, 0, 0, 0)
                    LogoutTime = Row.LogoutTime
                Else
                    AddDate = AddDate.AddDays(1)
                    LoginTime = New Date(AddDate.Year, AddDate.Month, AddDate.Day, 0, 0, 0, 0)
                    LogoutTime = New Date(AddDate.Year, AddDate.Month, AddDate.Day, 23, 59, 59, 999)
                End If

                If Row.IP_IsNull = True Then
                    With Out01Buffer
                        .AddRow()
                        .AccountKey = Row.AccountKey 'CType(Asc(Left(Row.CN, 1)).ToString() & Asc(Right(Left(Row.CN, 2), 1)).ToString() & Right(Row.CN, 10), Int64)
                        .LoginTime = LoginTime
                        .LogoutTime = LogoutTime
                        .ServiceKey = Row.ServiceKey
                        .DateKey = CInt(LoginTime.Date.ToString().Replace("-", "").Substring(0, 8))
                    End With
                Else
                    With Out01Buffer
                        .AddRow()
                        .AccountKey = Row.AccountKey 'CType(Asc(Left(Row.CN, 1)).ToString() & Asc(Right(Left(Row.CN, 2), 1)).ToString() & Right(Row.CN, 10), Int64)
                        .LoginTime = LoginTime
                        .LogoutTime = LogoutTime
                        .ServiceKey = Row.ServiceKey
                        .DateKey = CInt(LoginTime.Date.ToString().Replace("-", "").Substring(0, 8))
                        .IP = Row.IP
                    End With
                End If
                
                i = i + 1
            End While
        End If
        'Catch ex As Exception
        '    Dim err As Integer

        'End Try
    End Sub
End Class

EditText|Print|FindPage|DeletePage|LikePages|http://www.databaser.net|last modified 2010-03-19 18:37:20