_ _ _ _ _ Public Class ScriptMain Inherits UserComponent ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub CreateNewOutputRows() Dim culture As CultureInfo = CultureInfo.GetCultureInfo(MyBase.ComponentMetaData.LocaleID) Dim currentDate As Date = GetDate_(m_startDate, Me.IsStartDateVariable) Dim endDate As Date = GetDate_(m_endDate, Me.IsEndDateVariable) While currentDate <= endDate With MyBase.OutputBuffer Call .AddRow() .Day = currentDate.Day .DayShortName = culture.DateTimeFormat.GetAbbreviatedDayName(currentDate.DayOfWeek) .DayName = culture.DateTimeFormat.GetDayName(currentDate.DayOfWeek) .DayOfYear = currentDate.DayOfYear .Week = culture.Calendar.GetWeekOfYear( _ currentDate, _ culture.DateTimeFormat.CalendarWeekRule, _ culture.DateTimeFormat.FirstDayOfWeek) .Month = currentDate.Month .MonthShortName = culture.DateTimeFormat.GetAbbreviatedMonthName(currentDate.Month) .MonthName = culture.DateTimeFormat.GetMonthName(currentDate.Month) .Quarter = GetQuarter_(currentDate) .Year = currentDate.Year currentDate = currentDate.AddDays(Me.IterationStep) End With End While Call MyBase.OutputBuffer.SetEndOfRowset() End Sub ' CreateNewOutputRows ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean Try If Me.IterationStep <= 0 Then Throw New ApplicationException("Specified iteration step is invalid.") End If If String.IsNullOrEmpty(m_startDate) Then Throw New ApplicationException("Specify start date.") End If If String.IsNullOrEmpty(m_endDate) Then Throw New ApplicationException("Specify end date.") End If 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 ' Setup static. Call result.Add("IterationStep") Call result.Add("Culture") ' Setup dynamic. Call result.Add("IsStartDateVariable") Call result.Add(IIf(Me.IsStartDateVariable, "StartDateVariable", "StartDate")) Call result.Add("IsEndDateVariable") Call result.Add(IIf(Me.IsEndDateVariable, "EndDateVariable", "EndDate")) GetProperties = CType(result.ToArray(GetType(String)), String()) End Function ' GetProperties ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns list of available cultures. Public Function GetCultures() As Object() Dim result As New ArrayList() Dim cultures() As CultureInfo = CultureInfo.GetCultures(CultureTypes.AllCultures) For Each culture As CultureInfo In cultures Call result.Add(culture.DisplayName) Next GetCultures = result.ToArray() End Function ' GetCultures #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property IterationStep() As Integer Get IterationStep = m_iterStep End Get Set(ByVal value As Integer) m_iterStep = value End Set End Property ' IterationStep ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property Culture() As String Get Culture = CultureInfo.GetCultureInfo(MyBase.ComponentMetaData.LocaleID).DisplayName End Get Set(ByVal value As String) MyBase.ComponentMetaData.LocaleID = GetIdFromDisplayName_(value) End Set End Property ' Culture ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property IsStartDateVariable() As Boolean Get IsStartDateVariable = m_isStartDateVar End Get Set(ByVal value As Boolean) If m_isStartDateVar <> value Then m_isStartDateVar = value m_startDate = String.Empty End If End Set End Property ' IsStartDateVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ Public Property StartDate() As DateTime Get Dim result As DateTime If Not String.IsNullOrEmpty(m_startDate) Then result = Convert.ToDateTime(m_startDate) End If StartDate = result End Get Set(ByVal value As DateTime) m_startDate = value.ToString() End Set End Property ' StartDate ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property StartDateVariable() As String Get StartDateVariable = m_startDate End Get Set(ByVal value As String) m_startDate = value End Set End Property ' StartDateVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property IsEndDateVariable() As Boolean Get IsEndDateVariable = m_isEndDateVar End Get Set(ByVal value As Boolean) If m_isEndDateVar <> value Then m_isEndDateVar = value m_endDate = String.Empty End If End Set End Property ' IsEndDateVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ Public Property EndDate() As DateTime Get Dim result As DateTime If Not String.IsNullOrEmpty(m_endDate) Then result = Convert.ToDateTime(m_endDate) End If EndDate = result End Get Set(ByVal value As DateTime) m_endDate = value.ToString() End Set End Property ' EndDate ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property EndDateVariable() As String Get EndDateVariable = m_endDate End Get Set(ByVal value As String) m_endDate = value End Set End Property ' EndDateVariable #End Region ' Properties #Region "Internals" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetVariable_(ByVal varName As String) As Object Dim result As Object Dim vars As IDTSVariables100 = Nothing Call Me.VariableDispenser.LockOneForRead(varName, vars) Try result = vars(varName).Value Finally Call vars.Unlock() End Try GetVariable_ = result End Function ' GetVariable_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetDate_(ByVal dateStr As String, ByVal isVar As Boolean) As Date Dim result As Date If Not String.IsNullOrEmpty(dateStr) Then If isVar Then dateStr = GetVariable_(dateStr).ToString() End If result = Convert.ToDateTime(dateStr) End If GetDate_ = result End Function ' GetDate_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetIdFromDisplayName_(ByVal displayName As String) As Integer Dim result As Integer = CultureInfo.CurrentCulture.LCID If Not String.IsNullOrEmpty(displayName) Then Dim cultures() As CultureInfo = CultureInfo.GetCultures(CultureTypes.AllCultures) For Each culture As CultureInfo In cultures If culture.DisplayName.Equals(displayName) Then ' Found culture. result = culture.LCID Exit For End If Next End If GetIdFromDisplayName_ = result End Function ' GetIdFromDisplayName_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetQuarter_(ByVal input As Date) As Integer Dim result As Integer If input.Month >= 4 AndAlso input.Month <= 6 Then result = 1 ElseIf input.Month >= 7 AndAlso input.Month <= 9 Then result = 2 ElseIf input.Month >= 10 AndAlso input.Month <= 12 Then result = 3 Else result = 4 End If GetQuarter_ = result End Function ' GetQuarter_ #End Region ' Internals #Region "Attributes" Private m_iterStep As Integer Private m_isStartDateVar As Boolean Private m_startDate As String Private m_isEndDateVar As Boolean Private m_endDate As String Private m_culture As CultureInfo #End Region ' Attributes End Class ' ScriptMain ]]> _ 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 WriteOnly Property [Day]() As Int32 Set Me(0) = Value End Set End Property Public WriteOnly Property [Day_IsNull] As Boolean Set If (value) SetNull(0) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [DayShortName]() As String Set Me(1) = Value End Set End Property Public WriteOnly Property [DayShortName_IsNull] As Boolean Set If (value) SetNull(1) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [DayName]() As String Set Me(2) = Value End Set End Property Public WriteOnly Property [DayName_IsNull] As Boolean Set If (value) SetNull(2) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [DayOfYear]() As Int32 Set Me(3) = Value End Set End Property Public WriteOnly Property [DayOfYear_IsNull] As Boolean Set If (value) SetNull(3) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [Week]() As Int32 Set Me(4) = Value End Set End Property Public WriteOnly Property [Week_IsNull] As Boolean Set If (value) SetNull(4) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [Month]() As Int32 Set Me(5) = Value End Set End Property Public WriteOnly Property [Month_IsNull] As Boolean Set If (value) SetNull(5) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [MonthShortName]() As String Set Me(6) = Value End Set End Property Public WriteOnly Property [MonthShortName_IsNull] As Boolean Set If (value) SetNull(6) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [MonthName]() As String Set Me(7) = Value End Set End Property Public WriteOnly Property [MonthName_IsNull] As Boolean Set If (value) SetNull(7) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [Quarter]() As Int32 Set Me(8) = Value End Set End Property Public WriteOnly Property [Quarter_IsNull] As Boolean Set If (value) SetNull(8) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public WriteOnly Property [Year]() As Int32 Set Me(9) = Value End Set End Property Public WriteOnly Property [Year_IsNull] As Boolean Set If (value) SetNull(9) Else Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.") End If End Set End Property Public Overrides ReadOnly Property StaticOutputColumns() As String() Get Return New String() {"Day", "DayShortName", "DayName", "DayOfYear", "Week", "Month", "MonthShortName", "MonthName", "Quarter", "Year"} 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 ]]> {30D016F9-3734-4E33-A861-5E7D899E18F3};{F184B08F-C81C-45F6-A57F-5ABD9991F28F} Debug AnyCPU 8.0.30703 2.0 {F73097E3-3B19-415A-84FD-382D26B492DE} Library My Project ScriptComponent_72fa985f60e24531bfd7eba54da56fc3 ScriptComponent_72fa985f60e24531bfd7eba54da56fc3 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 SSIS_SC120 ]]> _ 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 End Class Public Class Variables Dim ParentComponent As ScriptComponent _ Public Sub New(ByVal Component As ScriptComponent) ParentComponent = Component End Sub End Class ]]> msBuild ScriptComponent_72fa985f60e24531bfd7eba54da56fc3 ScriptComponent_72fa985f60e24531bfd7eba54da56fc3 {A54AF869-06C1-4F06-9780-2C5ED894BB35} ]]>CozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus.2014, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34eaScriptComponent_72fa985f60e24531bfd7eba54da56fc3VisualBasic