SSIS and Script Component (1 Viewer)

[neoklis]

Hi guys,

In a SSIS package i have 5 or 6 script component. Is it possible to write once a function and call it from each script when i need it or do i have to copy my function into each script component?

Thanks

 

[SQL_Hell]

Script component? do you mean execute sql task?

 

[neoklis]

No my friend,

Script component is different than 'execute sql task'. You can find it into the data flow task and when you click on the toolbox it is in the data flow transformations.

 

[SQL_Hell]

Ah you mean "script task", looking at a script task in VS2005, it shows the language as vb.net. i dont think you can execute your function unless you create a ado connection / ado command object.

Just use an execute sql task, that's what they are there for after all

 

[neoklis]

Whell actually, because of working with data transformations (importing txt files into sql and transfrom data) is the reason that I need to work into a data flow task. And data flow task I thing that is the best way for manipulating data. Is that so..;

 

[SQL_Hell]

Ok I see what you mean now, you dont mean sql server user defined function do you, you mean vb.net function right? if not can you clarify what kind of function you mean?

Any function you create there will be within a class right? so just make a new instance of that class provided it's declared as public to the whole package.
I would have a first step (outside of the data flow) that creates the class and then each script component creates an object of that class.

 

[neoklis]

Yes, I mean a vb.net function.
I am a little confused.. Which control flow item i have to select to write my public function..;

 

[SQL_Hell]

To be honest it doesn't matter if you declare the classes in 1 step or you declare a class in each 'script component'

are you worried about a small amount of time to compile the class?

 

[neoklis]

I am worried about the next time I may want to change my function I will have to remember all the steps I use it and replace it. My worreid about will be of the possibility to forget a step.

After a google search I couldn’t find an article about how I can make a vb.net function public in a package to avoid retype it in every script component. The only thing I found was an article saying that we cannot have a public function for a package and he was wishing the next version to be resolved. I am not sure if I can find it back but I wiil give a try to find it and post it here.

Thank you

 

[SQL_Hell]

Ok, I see your predicament. Maybe you could build up documentation and stored scripts in SourceSafe, that would help.

Here is quite an interesting article

http://www.sqlservercentral.com/articles/Integration+Services/64572/