HI,
I created a script component that is doing some transformations and it works very well. Now, I want to use the same script component on other dataflows in order to use it with other tables. Since my script component uses three outputs and some of my dimensions may have sometimes 50-60 columns, and the fact that I need to customize the column names used in my script, I would like to be able to have some kind of wizard like the SCD wizard to create columns into the outputs based on a list of columns that the user would and and input some columns (historical attributes, etc) into my script component logic .
Has anybody did that before, or is there some articles/samples where I could look in order to get me started in doing this?
Thank you,
Ccote
You would probably want to write a custom component, rather than trying to implement this in a ScriptTx. BOL has a ton of info, as well as samples and tutorials on how to do this. Also see the tutorial and custom components on this site:
http://msdn.microsoft.com/sql/bi/integration/downloads/default.aspx
As an aside SCD is a special case that uses advanced services to create several child components on the design surface. In RTM code you could not emulate this behavior, however in SP1 (soon to be released on the web) we have exposed these interfaces. The IDtsPipelineEnvironmentService service lets custom data flow components have programmatic access to the parent Data Flow task.
|||I'd be a bit stronger, as soon as you start asking the question how can I resue a script task or component, the answer should actually be to write a custom component.
The SCD interface is different to most in two ways.
First it uses a Wizard style of form layout and behaviour, which you could do today in a task or component.
The second is that it spans several data flow components, not just the core SCD component you will see in the middle after completing the wizard. The ability to see outside of the current component, accessing the parent data flow and then other sibling components is what you cannot do until we get IDtsPipelineEnvironmentService in SP1.
|||You are right by saying that what I want to do is different someways to the SCD wizard.
We have both standard type 1 dimension and hybrid dimensions (type1 columns and type2 columns) that need to be processed. Since the SSIS lookup cache is not refreshed (unless cache is not used which has perfomance issues), I had to create an asynchrounous script component that suit our needs. SSIS is great for this by the way. My script component can handle type 1, type 2 as well as hybrid dimensions.
What I would like to do is to build a user interface that would modify the package
definition is a sense that it would take columns in output (like the script component does right now), do some script modifications and produce 3 outputs:
1- Type 1 columns update
2- Type 2 inserts based on some other columns
3- Type 2 current flag indicator update to 0 of the row that was already current before the load.
To produce all of these outputs and modify the script accordingly by hand will be feasible but teadious and error prone. I do know that there is sections in the package (.dtsx) where this info appears. I would like to create a wizard type form that would allow me to add outputs and script changes whitout doing this manually in the script component. The wizard would generate the script component for me, which would be faster to do and much less error prone.
Thank you all for your help,
Ccote
You could do this in one of two ways I think. What you cannot do however is add your own UI to the script component.
1 - Create a VS Addin that configures the package. I am not sure that this is feasible, would you be able to get the package context and would changes be reflected in the UI are two points to check first of all.
2 - Create a custom component. When you drop this new component onto a package it would invoke your component's custom UI (implemented in a wizard style etc). From there you could get access to the package, and should be able to add the script component and confgure it to your requirements. Unless you can then self delete you would be left with the redundant component in your package, but I cannot see any other way. You cannot write a custom UI for the script component. This really makes me think you would be better of writing a custom component to do the work itself rather than messing around with the Script Component.
|||HI Darren, thank you for your answer. I think that answer two is the way to go. Is there any documentation that could get me started. Basically, I need to create a component that would behave like an asynchronous script component, perform some logic based on some columns (identified by user) and add some outputs. I have Wrox Professionnal SSIS book, I see that there is a few chapters in there. Do you have other suggestions?
Thank you very much for your help,
Ccote
|||The is an asynch component sample that ships wth the product. There are also quite a few samples available from MS Downloads. Try these as well.
No comments:
Post a Comment