June 9, 2008 at 8:47 am
Hi All,
I want to know if I can handle this in the pipeline rather than in a stored proc as I have all the data I need already in the pipeline.
I will have is 1 record per transaction, a startdate, enddate Volume, Value and duration.
I need to take this record and split it out by the duration, (1 new record for each month for example). In the 1st of these new records I need to write '1' in the volume field and in the remaining rows need to be set to zero. I then need to take the value and divide it by the duration and place this value across each of the new rows.
Any best practices/learning experiences with something similar would be appreciated here
June 9, 2008 at 10:28 am
You could do this one of two ways that I can think of.
The first would be to use a script task and simply generate multiple output rows per input row.
The second would be to have another data flow that has the appropriate rows based on the duration and use a MERGE JOIN to cause your record duplication. You would then have to mark the "first" row with an aggregate component.
I think in your case, a script task will end up be easier.
June 9, 2008 at 1:54 pm
Hi, thanks for your reply. I am not following exactly what you mean in relation to the script task, but it certainly sounds like the best way to go.
I have not used the script task before, what steps should I be aiming to get the script task to do. What language is used?
June 9, 2008 at 2:00 pm
The script task only supports VB.Net for SQL 2005. 2008 gets you C# as well.
Basically, you will drag a script task into your data flow. You will be prompted to select if it is a source, destination, or transformation. You will make it a transformation.
Then, you will connect it to your data flow just like any other component. When you open the editor for it, you will be able to add an output and configure the output columns. You will then have to write some code to do the work you wanted to do and then create output rows in the output buffer.
There will be some sample if you search google that should get you moving. What you are trying to do will be fairly easy once you know what you are doing with the component.
June 9, 2008 at 2:04 pm
Thanks, but how would go about spliting the one transaction into many?
June 10, 2008 at 4:57 am
You need to do some reading about how to use script tasks. It is pretty easy to add a row to a pipeline.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply