September 12, 2007 at 1:25 am
I have a stored procedure that returns rows based upon certain criteria; this sp is called using a Datareader Source control. The Datareader Source control outputs the data to a Flat File Destination control.
So far so good, this all works as expected.
However, I found that when a certain row is returned from my sp I need to create a duplicate of it and change one of the columns. This new row then needs to be added to the Flat File. The end result will be a flat file that contains all of the rows returned from the sp AND any new rows that had to be created because of the existence of rows that met a certain criteria.
For example, if the sp returns a row that has column A = Car and column B = Ford I need to create an exact copy of this row but change column B to be equal to Audi. The flat file will therefore contain both the original and new rows.
Hope this makes sense!
Regards
David
September 13, 2007 at 5:03 am
If you are wanting to duplicate rows to your flow, add a script component, in the ouput properties, change it to async. In the script, you can process and pass through rows as normal but also introduce new rows.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
September 13, 2007 at 6:37 am
Hi Crispin,
I'm not sure that I understand all of your suggestion. I added a script component of transformation type to the Data Flow task but I cannot see where you set the async property.
Do I then simply add code to the script task to do the necessary transformations and then link the script task to an output (e.g. text file)?
Thanks
David
September 13, 2007 at 6:55 am
Open the component, click on imputs and outputs. Highlight the Output0. In the properties panel, change the SynchronousInputID to None.
This will detach your component's input and outputs. You will have to select all the input columns and in the code, pass their values to the output columns (Which you have to add manually in the Outputs section)
There are two blocks of code in the script when you open it. See example below:
Public
Class ScriptMain
Inherits UserComponent
Dim i As Integer = 0
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Output0Buffer.AddRow()
Output0Buffer.GeneratedInt1 = Row.GeneratedInt1
i += 1
If i = 5 Then CreateNewOutputRows()
End Sub
Public Overrides Sub CreateNewOutputRows()
Output0Buffer.AddRow()
Output0Buffer.GeneratedInt1 = 123456
End Sub
End
Class
HTH
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
September 13, 2007 at 8:28 am
Hi Crispin,
I get it now! Thanks for your help, much appreciated.
David
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply