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?

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.)

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?

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. Whatwe havedescribed 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

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

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.

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

Hi ViewMaster,

You may want to look intoextending 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 acouple 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.)

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

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?

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. Whatwe havedescribed 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

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

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

-Jamie

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

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

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.

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.

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.

I just wanted to comment 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