Correct approach to catching execution time errors in a custom task

Hi,

I'm building a custom task and just wondering what is the correct way of passing errors back to SSIS. Is there a rcommended approach to doing this. Currently I just wrap everything in a TRY...CATCH and use componentEvents to fire it back! Here's my code:

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser,IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
bool failed = false;
try
{
/*
* do stuff in here
*/
}
catch (Exception e)
{
componentEvents.FireError(-1, "", e.Message, "", 0);
failed = true;
}
if (failed)
{
return DTSExecResult.Failure;
}
else
{
return DTSExecResult.Success;
}
}

Any comments?

-Jamie

Anyone?|||the boolean flag isn't necessary. the line: return DTSExecResult.Failure;
could be in the catch block.|||

Good point. cheers Duane!! So it should be:

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser,IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
try
{
/*
* do stuff in here
*/

return DTSExecResult.Success;
}
catch (Exception e)
{
componentEvents.FireError(-1, "", e.Message, "", 0);
return DTSExecResult.Failure;
}
}

-Jamie

[Microsoft follow-up]

|||

Hi Jamie,

this looks like the correct approach to me.

sql

Correct approach to catching execution time errors in a custom task

Hi,

I'm building a custom task and just wondering what is the correct way of passing errors back to SSIS. Is there a rcommended approach to doing this. Currently I just wrap everything in a TRY...CATCH and use componentEvents to fire it back! Here's my code:

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser,IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
bool failed = false;
try
{
/*
* do stuff in here
*/
}
catch (Exception e)
{
componentEvents.FireError(-1, "", e.Message, "", 0);
failed = true;
}
if (failed)
{
return DTSExecResult.Failure;
}
else
{
return DTSExecResult.Success;
}
}

Any comments?

-Jamie

Anyone?|||the boolean flag isn't necessary. the line: return DTSExecResult.Failure;
could be in the catch block.|||

Good point. cheers Duane!! So it should be:

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser,IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
try
{
/*
* do stuff in here
*/

return DTSExecResult.Success;
}
catch (Exception e)
{
componentEvents.FireError(-1, "", e.Message, "", 0);
return DTSExecResult.Failure;
}
}

-Jamie

[Microsoft follow-up]

|||

Hi Jamie,

this looks like the correct approach to me.

Copying DTS Packages across Servers

HI All,

Now my brain isn't quite where it should be right about now - so hopefully you will be able to put me at rest.

I have been building a new server with SQL2000 on it. Some of the stuff on our existing server is going to be migrated across - however I for the life of me cannot make any sense of how to move stuff (like DTS packages) from one server to another.

moving the Database's - no problems, but all the other guff in the tree (in Enterprise Manager) I cant seem to get.

Any assistance here is greatly appreciated.

Cheers
TroyHi Troy,

You can save the packages as a structure file and save them to disk and then transfer them to your new server...

Or,.. you could get cunning and write a quick app to do it. I have some asp pages that I have written that will do this and I can flick them your way if you want... they will probably need tweaking to suit your needs...

What's the weather like in Auckland today??|||Hey thanks for that.
I will go and have a play and see what I can do - I assume it is a pretty straight forward process dumping them to file? And then reimporting them?

As for the ASP page - ummm errr, would love to take you up on that offer, but sadly I am about as literate with that sort of stuff as Cinderalla with a grenade laucher. (unless it is blatantly obvious). What are your thoughts on this?

Hmmm as for the weather - well all in all okay, and it is meant to be good through the weekend. Which will make a nice change because our new pool should have a chance to dry up and return to what was our lawn :(
Hopefully better in Aust.|||It's a relatively simple asp page,... the key thing is the SQL that we use. You could probably write it up as another stored proc or something to carry out the same function... ASP is a piece of cake to understand assuming some general knowledge of scripting languages...

Have a think about it and let me know.

Weather in Sydney today is overcast and cool,... not raining yet though... I think my cousin who is visiting from Chch brought this cloud with him...

Copy VS Create

Hello,

We are building a smart client that ships with it an offline db - Sql Express.

I wanted to check if there is a preferred/recommended approach to deploy the database ? I'm essentially asking between

1. Copying a database file and

2. Creating the database file by connecting to Master and issueing create database etc and then running the scripts as we need it.

Any help will be great,

Thanks,

Avinash

PS: The app is a multi user app and may be used by multiple windows users on the same machine. So we currently use the Auto Attach feature in the connection string with database files in the Special Application/User Data folder. So we isolate files b/w users.

Could you clarify your definition of "offline data" with your comment that this is a multi-user application? Do you expect multiple people to be using the same database simultaneously or should each user have a separate offline database?

Thanks for the additional informaiton.

Mike - SQL Express team

|||Hello Mike,

Its a multi user application in that - several windows users may use the application installled on one machine. Not at the same time. Currently we have one copy of the database per windows user logging into the machine/application in the User Data special folder.

So, yes its one database file per user but many such files may exist on a machine one per user. And only one user may use the machine/application at a time. However this may be slightly different in a WTS - terminal service environment. But even then the idea of one offline database file per user will not change.

Any advise will be appreciated and will help a lot..
Thanks,
Avinash
|||

Hi Avinash,

Thanks for the additional information. Since you are really looking for each user to have a unique database, I would embed the database into your application and then use User Instances to handle the attach/detach of the database. User Instances are new in SQL Express and are used by VS 2005 when you insert a database into your applciation. Check out the User Instance white paper for more information.

One of the main benefits of User Instances is that the person running the application doesn't have to be an admin user or have sa permissions to deal with things like attach and detach. Normally a user must have elevated permissions to do things such as this.

Once you've embedded a database in your application, you can also use ClickOnce deployment to get your application to your users. You should be able to find out more about ClickOnce in the VS documentation.

Regards,

Mike Wachal
SQL Express team

|||

Hello Mike Wachal,

One last question - if we generate the database file on a particular version of ms sql express and in time to come if there are a few more versions of ms sql express out there with the users - if we continue to package with our app a database file from an older version of sql express will that be a problem with users running a newer version of sql express ?

Also what about the converse scenario - where we ship a database file from a newer version of sql while the customer is still running on an older version.

Thanks,

Avinash