reusable lookup component

Question

reusable lookup component

hramineni

SSC Enthusiast

Points: 175
More actions
October 7, 2009 at 2:37 pm

#136266

I am new to SQL Server but have plenty of expereince in ETL .
I just started using SSIS of sql server 2008.
My requirement demands a lookup on table to return the Id by passing two input fields. These two inputs values will be passed through a paramerter in package.
My question is Can I create this lookup as single independent object and use it in multiple packages. so tomorrow anychanges to the lookup ( for ex: query overide) ,I will only change at one place and rest will taken care automatically.
Plesae suggest how to do or appreciate any link to the documentation.
Also can you have mutiple control flows in one packages and set up dependencies on these control flows while running.
Also can we create a package and use that as reusable in other pakcages

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ivan Donev SSC Eights! Points: 957 More actions · Answer 1

Hi there,

I guess if I understood the problem correctly,instead of a lookup you can create a function in the database which to be called with an Execute SQL task and map the result from the SQL to a variable in the package. This way you can achieve the desired re-usability.

/ivan

COZYROC One Orange Chip Points: 28499 More actions · Answer 2

hramineni (10/7/2009)
I am new to SQL Server but have plenty of expereince in ETL .
I just started using SSIS of sql server 2008.
My requirement demands a lookup on table to return the Id by passing two input fields. These two inputs values will be passed through a paramerter in package.
My question is Can I create this lookup as single independent object and use it in multiple packages. so tomorrow anychanges to the lookup ( for ex: query overide) ,I will only change at one place and rest will taken care automatically.
Plesae suggest how to do or appreciate any link to the documentation.
Also can you have mutiple control flows in one packages and set up dependencies on these control flows while running.
Also can we create a package and use that as reusable in other pakcages

Hi,

If you can use third-party components, check the commercial CozyRoc SSIS+ library. There are components for reusable Script Task, reusable Data Flow and in version 1.4 a reusable script component. You can have your logic in one place and use it from many packages.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

hramineni SSC Enthusiast Points: 175 More actions · Answer 3

Hi Ivan,

thankyou for your response. I thought that will be one option but was wondering if there is anything in SSIS that I can create a lookup component and reuse in different pacakges.

Also is there a way I can write a vb script and use the same in differnce packages instead of creating the same script in each package control flow.?

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

hramineni (10/9/2009)
Hi Ivan,
thankyou for your response. I thought that will be one option but was wondering if there is anything in SSIS that I can create a lookup component and reuse in different pacakges.
Also is there a way I can write a vb script and use the same in differnce packages instead of creating the same script in each package control flow.?

There is nothing else other than implementing your own custom component. CozyRoc's solution is the only one available on the market.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 5

Also is there a way I can write a vb script and use the same in differnce packages instead of creating the same script in each package control flow.?

Technically, you could write the script, save it as a file and then (after adding the script to the canvas) use the file as the script source, but while you're definitely only writing the script once, in my book this isn't exactly a 'reusable component'

If you're looking for the maplet or reusable transform paradigm of Informatica, then it's either Cozy's way, or the highway....

Steve.

hramineni SSC Enthusiast Points: 175 More actions · Answer 6

steve,

thanks for your reply. you are right I am looking something in SSIS similar to informatica resuable transformation or mapplet procedure..!

can you be little more specific when u say create the script as file and call that file script source? I created a Script task in the control flow and I do not see where I can call this script? and also where will the script reside if I have to reuse it in multipl packages?