Reusability in SSIS?

Something in another thread spurred me to post about this, wondering if others have solved this, or what workarounds they might be using.
Suppose we want to do the same operation on more than one column, or the same data transformation in more than one data flow, or in more than one package.
I don't know how to reuse anything in SSIS, except by copying & pasting and creating a second (or third, ...) copy.
I'm reluctant to copy & paste expressions or boxes or entire data flows, because (a) any change has to be made multiple times, and (b) if someone later changes one copy without realizing that there are other copies, they might go out of sync (I've seen this happen and lead to time-consuming debugging sessions).
The only answer that I've figured out is to push operations out of SSIS into the T-SQL world, where reusability is easy (and familiar) -- make UDFs, views, sprocs to do operations, and call them from multiple locations in the SSIS packages.
However, I don't know how to reuse expression operations in this fashion, because apparently I cannot call UDFs from Derived Column Expressions.
I suppose another solution, at least for little items such as expressions, would be to make changes across all versions by doing automated search & replace operations across all the dtsx files -- I've not done this, and am a little hesitant to do automated changes to dtsx files. Is anyone doing this happily?
Perry,
I certainly wouldn't want to do automated changes to the XML in a .dtsx file - I reckon that's fraught with danger.

At the moment the only unit of reusability is a package. In other words, seperate operations that you want to do more than once into a seperate package and call it using the Execute Package Task. You can't even call a data-flow from multiple points in the same package which really frustrates me - I don't think it would be that hard to implement.

Reusability is an area in which MS need to make giant strides in vNext and I'm quite sure they're aware of this. I dare say its high priority in the features list for vNext. Its certainly something I keep bringing up and I'm sure Kirk Haselden is fed up of me banging on about it by now :)

I have had some ideas about how reusability might be accomplished. My main idea is that, just as packages can be saved as .dtsx files, how about saving pre-configured tasks/components (e.g. A derived column transform that parses a date from a web log file or a Web Service Task that connects to a given web service) as .dtsx files (or perhaps .taskx/.comx files or something) and then being able to use them in a package just as you would a regular task or component. Now the unit of reusability is a task or a component instead of just a package. These pre-packaged tasks/components could be left as files or deployed up to a server just as you do with a package. If you could make them appear in the BIDS toolbox - so much the better!!!

Pre-configured expressions is an interesting subject and I envisage that this could be done in a similar way.

The other really compelling method of reusability would be the ability to build brand new custom data-flow components from existing pre-configured components without writing any code. For example I have a text file source adapter pointing at a web log file and a derived column transform that parses that web log file. Imagine if I could select those 2 components, right-click and select something like "Build new component" and it goes away and builds me a brand new distributable custom source adapter that does exactly the same as what those 2 components do together. This is something I've previously talked about here: http://blogs.conchango.com/jamiethomson/archive/2005/05/26/1470.aspx and is something that really really excites me - if its something that MS can achieve!!!

I've wittered on a bit here...I'll stop now :)

-Jamie|||We have implemented an extension of the standard Microsoft Script Task. It allows the implementation of your own script's user interface and has better script reusability. This can can be used as alternative to implementing a full blown custom SSIS control flow task. For more information please visit: http://www.cozyroc.com/products.html

Regards,
Ivan

Reusability in SSIS?

Something in another thread spurred me to post about this, wondering if others have solved this, or what workarounds they might be using.
Suppose we want to do the same operation on more than one column, or the same data transformation in more than one data flow, or in more than one package.
I don't know how to reuse anything in SSIS, except by copying & pasting and creating a second (or third, ...) copy.
I'm reluctant to copy & paste expressions or boxes or entire data flows, because (a) any change has to be made multiple times, and (b) if someone later changes one copy without realizing that there are other copies, they might go out of sync (I've seen this happen and lead to time-consuming debugging sessions).
The only answer that I've figured out is to push operations out of SSIS into the T-SQL world, where reusability is easy (and familiar) -- make UDFs, views, sprocs to do operations, and call them from multiple locations in the SSIS packages.
However, I don't know how to reuse expression operations in this fashion, because apparently I cannot call UDFs from Derived Column Expressions.
I suppose another solution, at least for little items such as expressions, would be to make changes across all versions by doing automated search & replace operations across all the dtsx files -- I've not done this, and am a little hesitant to do automated changes to dtsx files. Is anyone doing this happily?
Perry,
I certainly wouldn't want to do automated changes to the XML in a .dtsx file - I reckon that's fraught with danger.

At the moment the only unit of reusability is a package. In other words, seperate operations that you want to do more than once into a seperate package and call it using the Execute Package Task. You can't even call a data-flow from multiple points in the same package which really frustrates me - I don't think it would be that hard to implement.

Reusability is an area in which MS need to make giant strides in vNext and I'm quite sure they're aware of this. I dare say its high priority in the features list for vNext. Its certainly something I keep bringing up and I'm sure Kirk Haselden is fed up of me banging on about it by now :)

I have had some ideas about how reusability might be accomplished. My main idea is that, just as packages can be saved as .dtsx files, how about saving pre-configured tasks/components (e.g. A derived column transform that parses a date from a web log file or a Web Service Task that connects to a given web service) as .dtsx files (or perhaps .taskx/.comx files or something) and then being able to use them in a package just as you would a regular task or component. Now the unit of reusability is a task or a component instead of just a package. These pre-packaged tasks/components could be left as files or deployed up to a server just as you do with a package. If you could make them appear in the BIDS toolbox - so much the better!!!

Pre-configured expressions is an interesting subject and I envisage that this could be done in a similar way.

The other really compelling method of reusability would be the ability to build brand new custom data-flow components from existing pre-configured components without writing any code. For example I have a text file source adapter pointing at a web log file and a derived column transform that parses that web log file. Imagine if I could select those 2 components, right-click and select something like "Build new component" and it goes away and builds me a brand new distributable custom source adapter that does exactly the same as what those 2 components do together. This is something I've previously talked about here: http://blogs.conchango.com/jamiethomson/archive/2005/05/26/1470.aspx and is something that really really excites me - if its something that MS can achieve!!!

I've wittered on a bit here...I'll stop now :)

-Jamie|||We have implemented an extension of the standard Microsoft Script Task. It allows the implementation of your own script's user interface and has better script reusability. This can can be used as alternative to implementing a full blown custom SSIS control flow task. For more information please visit: http://www.cozyroc.com/products.html

Regards,
Ivan

Reusability & SSIS - issues they do not write in books about

I have a simple requirement:

Each package needs to have Error Handling - which needs to Execute a SQL statement. It's the same Stored Proc - where each package passes in its ID.

Ok - I get that part about creating a custom task and so on for reusabilty.
But - lets say that after deploying this task in 20 packages - I need to change the name of the Stored Proc.

What is going to happen in that case?

Correct me if I'm wrong - but after deploying the new version of the custom task - do I need to go to each package and update the reference to new version?Isn't this the purpose of package configurations?|||Not quite - configuration cannot handle when the processing logic changes. Lets say for instance in addition to ID input of the Stored Proc - the Custom task / Error handler --> you need to change it to accept System variable "PackageName".
--
Update:
Actually - come to think about it - in example mentioned above - Execute SQL task has been set with following expression:
"EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @.FEED_ID= " + (DT_WSTR,10) @.[User::FEED_ID] + ", @.FEED_EVENT_LOG_TYPE_ID = 3, @.STARTED_ON = '"+(DT_WSTR,30)@.[System::StartTime] +"', @.ENDED_ON = NULL, @.message = 'Package failed. ErrorCode: "+(DT_WSTR,12)@.[System::ErrorCode]+" ErrorMsg: "+REPLACE(REPLACE(@.[System::ErrorDescription],"\"",""),"'","")+"', @.FILES_PROCESSED = NULL, @.PKG_EXECUTION_ID = '" + @.[System::ExecutionInstanceGUID] + "'"

So I suppose - when I need to change it - I can put it in Config.

Thanks|||

TheViewMaster wrote:

Not quite - configuration cannot handle when the processing logic changes. Lets say for instance in addition to ID input of the Stored Proc - the Custom task / Error handler --> you need to change it to accept System variable "PackageName".

So what is your question exactly? If you are changing from ID (perhaps a user variable) to a system variable, then yes, you'll have to update all of the packages. Just as you would have to do if you needed to add another column to the data flow. I don't get it, I guess.|||Allrite - another scenario:

We have a common Script to extract data from XML which works with X number of packages (currently x=5).

So how do you make this script reusable in a way that when you need to make a change in script - all the packages which use the old script get automatically updated.|||

TheViewMaster wrote:

Allrite - another scenario:

We have a common Script to extract data from XML which works with X number of packages (currently x=5).

So how do you make this script reusable in a way that when you need to make a change in script - all the packages which use the old script get automatically updated.

As in a script component? You have a vb.net script that you want to make portable?|||

Hi ViewMaster,

You may want to look into extending SSIS.

You could create an object with all items subject to change (stored procedure names, etc.) stored in package variables and manage the variables in package configurations.

There are a couple books out already that describe this and at least one on the way that is solely dedicated to this subject. Another good resource for information about extending SSIS is SQLIS.com.

Hope this helps,
Andy

|||I think for what you want to do you have to save your script in a legitimate VB 2005 class library and register that class library with the CLR so you can reference it from your packages.

But that really just means that there isn't really any simple way to reuse code throughout SSIS packages, other than the dreadedly inefficient cut and paste.

For an OO developer like me that's a really really big frustration.|||

Correct me if I'm wrong - but after deploying the new version of the custom task - do I need to go to each package and update the reference to new version?

It depends if you change the strong name key of the task, the most obvious way of doing that is to change the assembly version. So don't change it and your are fine, and do not need to touch the packages. I use the AssemblyFileVersion attribute to give some visibility of my versions, whilst maintaining complete compatability.

You could also consider logging, and if the standard stuff does not work, write your own log provider, which could call your stored proc. The log provider is referenced in each package, but is obviously external in much the same way as the task code is in an external assembly, and in this case it may make more sense.

(There is a description of how to use assembly versions or not, page 431, para 3, Profession SQL Server 2005 IS, Wrox.)

|||

Jon Limjap wrote:

I think for what you want to do you have to save your script in a legitimate VB 2005 class library and register that class library with the CLR so you can reference it from your packages.

But that really just means that there isn't really any simple way to reuse code throughout SSIS packages, other than the dreadedly inefficient cut and paste.

For an OO developer like me that's a really really big frustration.

Yep exactly what I meant.

After developing with SSIS for awhile - I really try to keep things simple - by basically using only SQL, Script and Data Flow (mostly limited to importing data to sql staging tables) tasks.

Just the last package i wrote had 2 script tasks which were exactly identical - taking in variable and processing the same way. Since I doubt that this exact script will be used in other packages - it doesnt make to go through the hassle of putting it into gac.

I think what I'm asking is same as with DTS - ability to have "Global" functions - which can be created in 1 place of package and reused through out.
And then possibly be able to promote those Global functions to be used in other packages also|||

TheViewMaster wrote:

Jon Limjap wrote:

I think for what you want to do you have to save your script in a legitimate VB 2005 class library and register that class library with the CLR so you can reference it from your packages.

But that really just means that there isn't really any simple way to reuse code throughout SSIS packages, other than the dreadedly inefficient cut and paste.

For an OO developer like me that's a really really big frustration.

Yep exactly what I meant.

After developing with SSIS for awhile - I really try to keep things simple - by basically using only SQL, Script and Data Flow (mostly limited to importing data to sql staging tables) tasks.

Just the last package i wrote had 2 script tasks which were exactly identical - taking in variable and processing the same way. Since I doubt that this exact script will be used in other packages - it doesnt make to go through the hassle of putting it into gac.

If it does not make sense to make it a reusable component then why have you written this thread in the first place? I think you are saying that you want toreuse some custom code that you have written. So OK, make it a custom task. Why is putting it into the GAC such a problem? Where else would you put it? Wherever you put it you still have to go through the rigmarole of putting it SOMEWHERE, so why is putting it in the GAC any more or less of a problem than anywhere else?

TheViewMaster wrote:

I think what I'm asking is same as with DTS - ability to have "Global" functions - which can be created in 1 place of package and reused through out.
And then possibly be able to promote those Global functions to be used in other packages also

What you are describing is the ability to reuse tasks that you have configured yourself without having to write a custom task. So a scenario may be:

"I am using a Web Service Task that calls a web service. I am going to want to call that Web Service from many packages so why should I bother dragging on another web service task and configuring it the same, why not just drag on the old one that I have already built?"

Currently the only way to do this is to build a custom task - you want a way of distributing pre-configured tasks that you can (and this is the key) instantiate in various places. A change to the base task will occur anywhere that that task is instantiated. What we have described is exactly how a rival product, Informatica, works and when I first saw SSIS 3 years ago I was disappointed that it didn't work in the same way. And I said so:

Libraries of tasks and transformations
(http://blogs.conchango.com/jamiethomson/archive/2005/02/05/929.aspx)

Combining multiple components into a single distributable component via the SSIS Designer
(http://blogs.conchango.com/jamiethomson/archive/2005/05/26/SSIS_3A00_-Combining-multiple-components-into-a-single-distributable-component-via-the-SSIS-Designer.aspx)

Don't expect reuse of tasks in Katmai though - it won't happen. Having said that, look forward to something equally as interesting - reuse of components (or groups of components).

-Jamie

|||

Jon Limjap wrote:

I think for what you want to do you have to save your script in a legitimate VB 2005 class library and register that class library with the CLR so you can reference it from your packages.

But that really just means that there isn't really any simple way to reuse code throughout SSIS packages, other than the dreadedly inefficient cut and paste.

Really? It sounds to me like you've just described one.

By the way, in that scenario you are not constrained to writing your assembly in VB. Assemblies are of course object code so it doesn't matter what language you write them in.

-Jamie

|||

Jamie Thomson wrote:

If it does not make sense to make it a reusable component then why have you written this thread in the first place? I think you are saying that you want toreuse some custom code that you have written. So OK, make it a custom task. Why is putting it into the GAC such a problem? Where else would you put it? Wherever you put it you still have to go through the rigmarole of putting it SOMEWHERE, so why is putting it in the GAC any more or less of a problem than anywhere else?

Q: Why is custom task or code in GAC a problem?
A: Manageability - the more custom code & tasks you have - it will be problemsome to upgrade custom components and also - when you need to "move" your solution to another SQL box (which we may have to do when we roll out the new website).

I am already using "Trash Destination" component and Custom Filter Duplicates based on Key Column(s) transform - but I'm a little cautious to add more external components to SSIS. And as stated in the statement - potentially the repeating code will be in the same package - 2 or 3 tasks - in which case copy&paste outweighs of custom task

Jamie Thomson wrote:

What you are describing is the ability to reuse tasks that you have configured yourself without having to write a custom task. So a scenario may be:

"I am using a Web Service Task that calls a web service. I am going to want to call that Web Service from many packages so why should I bother dragging on another web service task and configuring it the same, why not just drag on the old one that I have already built?"

Currently the only way to do this is to build a custom task - you want a way of distributing pre-configured tasks that you can (and this is the key) instantiate in various places. A change to the base task will occur anywhere that that task is instantiated. What we have described is exactly how a rival product, Informatica, works and when I first saw SSIS 3 years ago I was disappointed that it didn't work in the same way. And I said so:

Libraries of tasks and transformations
(http://blogs.conchango.com/jamiethomson/archive/2005/02/05/929.aspx)

Combining multiple components into a single distributable component via the SSIS Designer
(http://blogs.conchango.com/jamiethomson/archive/2005/05/26/SSIS_3A00_-Combining-multiple-components-into-a-single-distributable-component-via-the-SSIS-Designer.aspx)

Don't expect reuse of tasks in Katmai though - it won't happen. Having said that, look forward to something equally as interesting - reuse of components (or groups of components).

-Jamie

Amen brother, good stuff
I do not see where Alaska comes into play with reusing the tasks - but I'll look forward to reuse of components |||

Jamie Thomson wrote:

Jon Limjap wrote:

I think for what you want to do you have to save your script in a legitimate VB 2005 class library and register that class library with the CLR so you can reference it from your packages.

But that really just means that there isn't really any simple way to reuse code throughout SSIS packages, other than the dreadedly inefficient cut and paste.

Really? It sounds to me like you've just described one.

By the way, in that scenario you are not constrained to writing your assembly in VB. Assemblies are of course object code so it doesn't matter what language you write them in.

-Jamie

Well, again its not simple. Once I compile my class library and register it to the GAC, I can't step through its code anymore.

And while cut and paste works sometimes, what if I've peppered my package with a recurring script, and then find a serious flaw with that code much later? I'll have to go back to each and every script task I've pasted the code in -- and repaste, retest, etc.

It's laborious, and inefficient, any way you put it, not to mention that cutting and pasting per se is very, very error prone.|||

Jon Limjap wrote:

Well, again its not simple. Once I compile my class library and register it to the GAC, I can't step through its code anymore.

Have you tried using the Debug features of VS? For simple testing I'd set Debug Start actions to external program, dtexec, and command line arguments to run a package that uses the class in script. Rather like I'd do for custom component or task development. Faster for checing run-time stuff than attaching to a process by hand.

Jon Limjap wrote:

And while cut and paste works sometimes, what if I've peppered my package with a recurring script, and then find a serious flaw with that code much later? I'll have to go back to each and every script task I've pasted the code in -- and repaste, retest, etc.

Sounds like an argument for custom tasks. If you use the same code more than a few times I think it should be a task, for just that sort of reason.

Reusability & SSIS - issues they do not write in books about

I have a simple requirement:

Each package needs to have Error Handling - which needs to Execute a SQL statement. It's the same Stored Proc - where each package passes in its ID.

Ok - I get that part about creating a custom task and so on for reusabilty.
But - lets say that after deploying this task in 20 packages - I need to change the name of the Stored Proc.

What is going to happen in that case?

Correct me if I'm wrong - but after deploying the new version of the custom task - do I need to go to each package and update the reference to new version?Isn't this the purpose of package configurations?|||Not quite - configuration cannot handle when the processing logic changes. Lets say for instance in addition to ID input of the Stored Proc - the Custom task / Error handler --> you need to change it to accept System variable "PackageName".
--
Update:
Actually - come to think about it - in example mentioned above - Execute SQL task has been set with following expression:
"EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @.FEED_ID= " + (DT_WSTR,10) @.[User::FEED_ID] + ", @.FEED_EVENT_LOG_TYPE_ID = 3, @.STARTED_ON = '"+(DT_WSTR,30)@.[System::StartTime] +"', @.ENDED_ON = NULL, @.message = 'Package failed. ErrorCode: "+(DT_WSTR,12)@.[System::ErrorCode]+" ErrorMsg: "+REPLACE(REPLACE(@.[System::ErrorDescription],"\"",""),"'","")+"', @.FILES_PROCESSED = NULL, @.PKG_EXECUTION_ID = '" + @.[System::ExecutionInstanceGUID] + "'"

So I suppose - when I need to change it - I can put it in Config.

Thanks|||

TheViewMaster wrote:

Not quite - configuration cannot handle when the processing logic changes. Lets say for instance in addition to ID input of the Stored Proc - the Custom task / Error handler --> you need to change it to accept System variable "PackageName".

So what is your question exactly? If you are changing from ID (perhaps a user variable) to a system variable, then yes, you'll have to update all of the packages. Just as you would have to do if you needed to add another column to the data flow. I don't get it, I guess.|||Allrite - another scenario:

We have a common Script to extract data from XML which works with X number of packages (currently x=5).

So how do you make this script reusable in a way that when you need to make a change in script - all the packages which use the old script get automatically updated.|||

TheViewMaster wrote:

Allrite - another scenario:

We have a common Script to extract data from XML which works with X number of packages (currently x=5).

So how do you make this script reusable in a way that when you need to make a change in script - all the packages which use the old script get automatically updated.

As in a script component? You have a vb.net script that you want to make portable?|||

Hi ViewMaster,

You may want to look into extending SSIS.

You could create an object with all items subject to change (stored procedure names, etc.) stored in package variables and manage the variables in package configurations.

There are a couple books out already that describe this and at least one on the way that is solely dedicated to this subject. Another good resource for information about extending SSIS is SQLIS.com.

Hope this helps,
Andy

|||I think for what you want to do you have to save your script in a legitimate VB 2005 class library and register that class library with the CLR so you can reference it from your packages.

But that really just means that there isn't really any simple way to reuse code throughout SSIS packages, other than the dreadedly inefficient cut and paste.

For an OO developer like me that's a really really big frustration.|||

Correct me if I'm wrong - but after deploying the new version of the custom task - do I need to go to each package and update the reference to new version?

It depends if you change the strong name key of the task, the most obvious way of doing that is to change the assembly version. So don't change it and your are fine, and do not need to touch the packages. I use the AssemblyFileVersion attribute to give some visibility of my versions, whilst maintaining complete compatability.

You could also consider logging, and if the standard stuff does not work, write your own log provider, which could call your stored proc. The log provider is referenced in each package, but is obviously external in much the same way as the task code is in an external assembly, and in this case it may make more sense.

(There is a description of how to use assembly versions or not, page 431, para 3, Profession SQL Server 2005 IS, Wrox.)

|||

Jon Limjap wrote:

I think for what you want to do you have to save your script in a legitimate VB 2005 class library and register that class library with the CLR so you can reference it from your packages.

But that really just means that there isn't really any simple way to reuse code throughout SSIS packages, other than the dreadedly inefficient cut and paste.

For an OO developer like me that's a really really big frustration.

Yep exactly what I meant.

After developing with SSIS for awhile - I really try to keep things simple - by basically using only SQL, Script and Data Flow (mostly limited to importing data to sql staging tables) tasks.

Just the last package i wrote had 2 script tasks which were exactly identical - taking in variable and processing the same way. Since I doubt that this exact script will be used in other packages - it doesnt make to go through the hassle of putting it into gac.

I think what I'm asking is same as with DTS - ability to have "Global" functions - which can be created in 1 place of package and reused through out.
And then possibly be able to promote those Global functions to be used in other packages also|||

TheViewMaster wrote:

Jon Limjap wrote:

I think for what you want to do you have to save your script in a legitimate VB 2005 class library and register that class library with the CLR so you can reference it from your packages.

But that really just means that there isn't really any simple way to reuse code throughout SSIS packages, other than the dreadedly inefficient cut and paste.

For an OO developer like me that's a really really big frustration.

Yep exactly what I meant.

After developing with SSIS for awhile - I really try to keep things simple - by basically using only SQL, Script and Data Flow (mostly limited to importing data to sql staging tables) tasks.

Just the last package i wrote had 2 script tasks which were exactly identical - taking in variable and processing the same way. Since I doubt that this exact script will be used in other packages - it doesnt make to go through the hassle of putting it into gac.

If it does not make sense to make it a reusable component then why have you written this thread in the first place? I think you are saying that you want toreuse some custom code that you have written. So OK, make it a custom task. Why is putting it into the GAC such a problem? Where else would you put it? Wherever you put it you still have to go through the rigmarole of putting it SOMEWHERE, so why is putting it in the GAC any more or less of a problem than anywhere else?

TheViewMaster wrote:

I think what I'm asking is same as with DTS - ability to have "Global" functions - which can be created in 1 place of package and reused through out.
And then possibly be able to promote those Global functions to be used in other packages also

What you are describing is the ability to reuse tasks that you have configured yourself without having to write a custom task. So a scenario may be:

"I am using a Web Service Task that calls a web service. I am going to want to call that Web Service from many packages so why should I bother dragging on another web service task and configuring it the same, why not just drag on the old one that I have already built?"

Currently the only way to do this is to build a custom task - you want a way of distributing pre-configured tasks that you can (and this is the key) instantiate in various places. A change to the base task will occur anywhere that that task is instantiated. What we have described is exactly how a rival product, Informatica, works and when I first saw SSIS 3 years ago I was disappointed that it didn't work in the same way. And I said so:

Libraries of tasks and transformations
(http://blogs.conchango.com/jamiethomson/archive/2005/02/05/929.aspx)

Combining multiple components into a single distributable component via the SSIS Designer
(http://blogs.conchango.com/jamiethomson/archive/2005/05/26/SSIS_3A00_-Combining-multiple-components-into-a-single-distributable-component-via-the-SSIS-Designer.aspx)

Don't expect reuse of tasks in Katmai though - it won't happen. Having said that, look forward to something equally as interesting - reuse of components (or groups of components).

-Jamie

|||

Jon Limjap wrote:

I think for what you want to do you have to save your script in a legitimate VB 2005 class library and register that class library with the CLR so you can reference it from your packages.

But that really just means that there isn't really any simple way to reuse code throughout SSIS packages, other than the dreadedly inefficient cut and paste.

Really? It sounds to me like you've just described one.

By the way, in that scenario you are not constrained to writing your assembly in VB. Assemblies are of course object code so it doesn't matter what language you write them in.

-Jamie

|||

Jamie Thomson wrote:

If it does not make sense to make it a reusable component then why have you written this thread in the first place? I think you are saying that you want toreuse some custom code that you have written. So OK, make it a custom task. Why is putting it into the GAC such a problem? Where else would you put it? Wherever you put it you still have to go through the rigmarole of putting it SOMEWHERE, so why is putting it in the GAC any more or less of a problem than anywhere else?

Q: Why is custom task or code in GAC a problem?
A: Manageability - the more custom code & tasks you have - it will be problemsome to upgrade custom components and also - when you need to "move" your solution to another SQL box (which we may have to do when we roll out the new website).

I am already using "Trash Destination" component and Custom Filter Duplicates based on Key Column(s) transform - but I'm a little cautious to add more external components to SSIS. And as stated in the statement - potentially the repeating code will be in the same package - 2 or 3 tasks - in which case copy&paste outweighs of custom task

Jamie Thomson wrote:

What you are describing is the ability to reuse tasks that you have configured yourself without having to write a custom task. So a scenario may be:

"I am using a Web Service Task that calls a web service. I am going to want to call that Web Service from many packages so why should I bother dragging on another web service task and configuring it the same, why not just drag on the old one that I have already built?"

Currently the only way to do this is to build a custom task - you want a way of distributing pre-configured tasks that you can (and this is the key) instantiate in various places. A change to the base task will occur anywhere that that task is instantiated. What we have described is exactly how a rival product, Informatica, works and when I first saw SSIS 3 years ago I was disappointed that it didn't work in the same way. And I said so:

Libraries of tasks and transformations
(http://blogs.conchango.com/jamiethomson/archive/2005/02/05/929.aspx)

Combining multiple components into a single distributable component via the SSIS Designer
(http://blogs.conchango.com/jamiethomson/archive/2005/05/26/SSIS_3A00_-Combining-multiple-components-into-a-single-distributable-component-via-the-SSIS-Designer.aspx)

Don't expect reuse of tasks in Katmai though - it won't happen. Having said that, look forward to something equally as interesting - reuse of components (or groups of components).

-Jamie

Amen brother, good stuff
I do not see where Alaska comes into play with reusing the tasks - but I'll look forward to reuse of components |||

Jamie Thomson wrote:

Jon Limjap wrote:

I think for what you want to do you have to save your script in a legitimate VB 2005 class library and register that class library with the CLR so you can reference it from your packages.

But that really just means that there isn't really any simple way to reuse code throughout SSIS packages, other than the dreadedly inefficient cut and paste.

Really? It sounds to me like you've just described one.

By the way, in that scenario you are not constrained to writing your assembly in VB. Assemblies are of course object code so it doesn't matter what language you write them in.

-Jamie

Well, again its not simple. Once I compile my class library and register it to the GAC, I can't step through its code anymore.

And while cut and paste works sometimes, what if I've peppered my package with a recurring script, and then find a serious flaw with that code much later? I'll have to go back to each and every script task I've pasted the code in -- and repaste, retest, etc.

It's laborious, and inefficient, any way you put it, not to mention that cutting and pasting per se is very, very error prone.|||

Jon Limjap wrote:

Well, again its not simple. Once I compile my class library and register it to the GAC, I can't step through its code anymore.

Have you tried using the Debug features of VS? For simple testing I'd set Debug Start actions to external program, dtexec, and command line arguments to run a package that uses the class in script. Rather like I'd do for custom component or task development. Faster for checing run-time stuff than attaching to a process by hand.

Jon Limjap wrote:

And while cut and paste works sometimes, what if I've peppered my package with a recurring script, and then find a serious flaw with that code much later? I'll have to go back to each and every script task I've pasted the code in -- and repaste, retest, etc.

Sounds like an argument for custom tasks. If you use the same code more than a few times I think it should be a task, for just that sort of reason.