If you have records with a start- and enddate and you want to split them into separate records per date, the Script Component might be a solution for you.
Starting point
Id | Startdate | Enddate |
1 | 01-21-2010 | 01-28-2010 |
2 | 09-26-2010 | 10-02-2010 |
Desired situation
Id | Factdate |
1 | 01-21-2010 |
1 | 01-22-2010 |
1 | 01-23-2010 |
1 | 01-24-2010 |
1 | 01-25-2010 |
1 | 01-26-2010 |
1 | 01-27-2010 |
1 | 01-28-2010 |
2 | 09-26-2010 |
2 | 09-27-2010 |
2 | 09-28-2010 |
2 | 09-29-2010 |
2 | 09-30-2010 |
2 | 10-01-2010 |
2 | 10-02-2010 |
Solution
1) Add a script component to your dataflow.
Script component (transformation) |
2) Select all columns you need and select ReadOnly (default).
ReadOnly Input columns |
3) Now make sure that your task is Asynchronous (number of records in isn't equal to the number of records out) by selecting "None" at SynchronousInputId.
Asynchronous |
4) Copy all input columns to the output columns, but the StartDate and EndDate will be replaced by one date column named FactDate. Make sure that the datatypes match.
Copy columns |
5) Now the script (I used VB.net in this example). SSIS will generate two methods named: Input0_ProcessInputRow and CreateNewOutputRows. You can remove the last one because we only need Input0_ProcessInputRow. First get the start- and enddate, then loop through the dates and add a row for each date:
' Split a record with a start- and enddate into separate records per date 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 ' Method that will be started for each record in you dataflow Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) ' Declare variables to use them in the loop Dim StartDate, EndDate as Date ' Get StartDate from dataflow StartDate = Row.StartDate ' Get EndDate from dataflow. You could add some default value if the EndDate is unknown. If Row.EndDate_IsNull Then EndDate = DateTime.Now.Date Else EndDate = Row.EndDate End If ' Loop through dates While StartDate <= EndDate Me.Output0Buffer.AddRow ' Add a new record Me.Output0Buffer.FactDate = StartDate ' Fill the new created FactDate column Me.Output0Buffer.Id = Row.Id ' Fill all other columns copied from the input columns StartDate = StartDate.AddDays(1) ' Add one day to go to the next date End While End Sub End Class
6) Now add a target and run the package to see the result:
2 rows => 15 rows |
Note: There are other ways to fix this case, but this is one is quite easy and quick.
No comments:
Post a Comment
Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.
All comments are moderated manually to prevent spam.