Hello, I need help in understanding how to using scripts (in Script Component). When I click “Design Script” button I’m redirected to public method “Input0_ProcessInputRow” and I can add some transformation for each row (record). But for this transormation I need some total sum and I don’t know how can I calcute this. Example : I have “Data Flow Task” (DFT). In thos DFT I have “OLE DB Source”, “Lookup”, another “OLE DB Source”, “Lookup” AND “Merge Join” Component. Using data from last component I want to add new column with calculate. I have column “Order Amount”, and in new column I want to have percentage of total summary. So I need to have value of Total Order Amount. How can I do this? I was thinking about Script Component, but I don’t know how to calculate total in method described above. If I good understand in this method I can operate only on one row. (I use SQL Server 2005 Developer Edition) Best Regrards, Mike

You may not need a Script component for this. There are two work-around that I can think of: Let's suppose that you are loading Order Dertails, and, as you mentioned, you want the Order Total and the Percentage of Order Total for each Order Detail row. First, you could leave those fields alone in the DFT, and calculate their value using an Execute SQL Task after the DFT. Such SQL would UPDATE the OrderDetails table based on a join to its (aggregated) self on OrderID. Second, you could put in a MultiCast in the DFT that splits the pipieline, then down one side put in a Aggregate that does a SUM of Order Detail Amont and Grouped by OrderID, and re-name it OrderTotal. Then have a Merge Join the brings the two pipelines together, joining on OrderID. After that, have a Derived Column transform that does the calculation of Percent of Total. And Lastly, if this is a Fact table used for an Analsis Server Cube, you could (and probably should) instead create a Calculated Member using the Order Dimension's Parent member, etc. But if you're going this route, re-ost your question in the Analysis Server forum because the solution would be an MDX calculation. Hope this helps.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Thank you for your answer. I'll try to do with your instruction. BTW I want to do this using Script Component. I was looking in documentation about asynchronous transformations and I think this is solution for me. Now I have problem with methods, because I can’t overload one of them, but there is another problem. I’ll search on forum. Thank you. Mike

I have a cinder block and a 10-foot beam and I plan to use it to jack up my car so I can change a tire. I'll use the cinder block as a fulcrum and the beam as a lever. What's wrong with this picture? This is what's wrong: I have a tool in the trunk designed and dedicated for this very function, it's called a jack. If you try to go with a Script Component, I think you are going to have trouble because you will need to somehow halt all the rows for a particular OrderID (asynchronous output) until ALL are collected, then calculate the total, then calculate the percentage for each row, then move on to the next batch (OrderID). My SERIOUS suggestion: Ditch the Script Component and go with transforms "dedicated for this very function".Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

I completely concur with Todd C on this one. There are plenty of opportunities for you to get familiar with the Script component in situations where you have no other choice. Using a Script in this case only brings more problems down the line with maintainability. Talk to me now on