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 instead of backing up SQL 2005 db

I just found out that our backup software (MS DPM) is conflicting with the
SQL Maintenance jobs. We can't use back up database task in maintenace jobs.
Is there a way to copy a database? I'd like to add this to maintenance jobs
as a t-sql script, or sql server agent job.
Thanks in advance...
The BACKUP command has an option named COPY_ONLY that allows to perform
database backups without affecting the normal sequence of full, differential
and transaction log backups.
See more details on COPY_ONLY on BACKUP on BOL.
Hope this helps,
Ben Nevarez
"Artunc" wrote:

> I just found out that our backup software (MS DPM) is conflicting with the
> SQL Maintenance jobs. We can't use back up database task in maintenace jobs.
> Is there a way to copy a database? I'd like to add this to maintenance jobs
> as a t-sql script, or sql server agent job.
> Thanks in advance...
|||My question would be why are you trying to back it up twice then? How
exactly is it conflicting?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Artunc" <Artunc@.discussions.microsoft.com> wrote in message
news:96113D5A-2B6E-4EDC-9D3E-91979FC7E3B5@.microsoft.com...
>I just found out that our backup software (MS DPM) is conflicting with the
> SQL Maintenance jobs. We can't use back up database task in maintenace
> jobs.
> Is there a way to copy a database? I'd like to add this to maintenance
> jobs
> as a t-sql script, or sql server agent job.
> Thanks in advance...
|||No other reson than having a second set of backup for importand dbs.
This is the error I am getting in DPM when backup maintenance job and DPM
backup job run for the same databases:
"DPM tried to do a SQL log backup, either as part of a backup job or a
recovery to latest point in time job. The SQL log backup job has detected a
discontinuity in the SQL log chain for SQL Server 2005 database database
SQLSVR\DB1 since the last backup. All incremental backup jobs will fail until
an express full backup runs. (ID 30140 Details: Internal error code:
0x80990D11)"
"Andrew J. Kelly" wrote:

> My question would be why are you trying to back it up twice then? How
> exactly is it conflicting?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Artunc" <Artunc@.discussions.microsoft.com> wrote in message
> news:96113D5A-2B6E-4EDC-9D3E-91979FC7E3B5@.microsoft.com...
>

Copying instead of backing up SQL 2005 db

I just found out that our backup software (MS DPM) is conflicting with the
SQL Maintenance jobs. We can't use back up database task in maintenace jobs.
Is there a way to copy a database? I'd like to add this to maintenance jobs
as a t-sql script, or sql server agent job.
Thanks in advance...The BACKUP command has an option named COPY_ONLY that allows to perform
database backups without affecting the normal sequence of full, differential
and transaction log backups.
See more details on COPY_ONLY on BACKUP on BOL.
Hope this helps,
Ben Nevarez
"Artunc" wrote:
> I just found out that our backup software (MS DPM) is conflicting with the
> SQL Maintenance jobs. We can't use back up database task in maintenace jobs.
> Is there a way to copy a database? I'd like to add this to maintenance jobs
> as a t-sql script, or sql server agent job.
> Thanks in advance...|||My question would be why are you trying to back it up twice then? How
exactly is it conflicting?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Artunc" <Artunc@.discussions.microsoft.com> wrote in message
news:96113D5A-2B6E-4EDC-9D3E-91979FC7E3B5@.microsoft.com...
>I just found out that our backup software (MS DPM) is conflicting with the
> SQL Maintenance jobs. We can't use back up database task in maintenace
> jobs.
> Is there a way to copy a database? I'd like to add this to maintenance
> jobs
> as a t-sql script, or sql server agent job.
> Thanks in advance...|||No other reson than having a second set of backup for importand dbs.
This is the error I am getting in DPM when backup maintenance job and DPM
backup job run for the same databases:
"DPM tried to do a SQL log backup, either as part of a backup job or a
recovery to latest point in time job. The SQL log backup job has detected a
discontinuity in the SQL log chain for SQL Server 2005 database database
SQLSVR\DB1 since the last backup. All incremental backup jobs will fail until
an express full backup runs. (ID 30140 Details: Internal error code:
0x80990D11)"
"Andrew J. Kelly" wrote:
> My question would be why are you trying to back it up twice then? How
> exactly is it conflicting?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Artunc" <Artunc@.discussions.microsoft.com> wrote in message
> news:96113D5A-2B6E-4EDC-9D3E-91979FC7E3B5@.microsoft.com...
> >I just found out that our backup software (MS DPM) is conflicting with the
> > SQL Maintenance jobs. We can't use back up database task in maintenace
> > jobs.
> >
> > Is there a way to copy a database? I'd like to add this to maintenance
> > jobs
> > as a t-sql script, or sql server agent job.
> >
> > Thanks in advance...
>|||You should decide which backup method is more important to you. Let this do both database and log
backups. Let the other do only database backup, which will not disrupt the log backup chain (for the
other backup method).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Artunc" <Artunc@.discussions.microsoft.com> wrote in message
news:B5F12DC7-47DE-41E4-B8CD-2F9158EEC288@.microsoft.com...
> No other reson than having a second set of backup for importand dbs.
> This is the error I am getting in DPM when backup maintenance job and DPM
> backup job run for the same databases:
> "DPM tried to do a SQL log backup, either as part of a backup job or a
> recovery to latest point in time job. The SQL log backup job has detected a
> discontinuity in the SQL log chain for SQL Server 2005 database database
> SQLSVR\DB1 since the last backup. All incremental backup jobs will fail until
> an express full backup runs. (ID 30140 Details: Internal error code:
> 0x80990D11)"
>
> "Andrew J. Kelly" wrote:
>> My question would be why are you trying to back it up twice then? How
>> exactly is it conflicting?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Artunc" <Artunc@.discussions.microsoft.com> wrote in message
>> news:96113D5A-2B6E-4EDC-9D3E-91979FC7E3B5@.microsoft.com...
>> >I just found out that our backup software (MS DPM) is conflicting with the
>> > SQL Maintenance jobs. We can't use back up database task in maintenace
>> > jobs.
>> >
>> > Is there a way to copy a database? I'd like to add this to maintenance
>> > jobs
>> > as a t-sql script, or sql server agent job.
>> >
>> > Thanks in advance...
>>

copying data folder to another instance

How do I copy the data from one instance of an MSDE to another? What I need
to do is write the structure, data, etc on a CD so I can copy this back on
my server instance at home and work on it.
Thanks for the information.
Brad
Attach/Detach or Backup/Restore
"Brad" <ballison@.ukcdogs.com> wrote in message
news:OijL5KEEFHA.1348@.TK2MSFTNGP14.phx.gbl...
> How do I copy the data from one instance of an MSDE to another? What I
need
> to do is write the structure, data, etc on a CD so I can copy this back on
> my server instance at home and work on it.
> Thanks for the information.
> Brad
>
|||Norman,
I primarily progam and I really am not a DB Admin. I understand the
concepts of Attach/Detach but I have never practically used it. How would I
get into the database to do that? Is the Backup/Restore the Backup that is
used through Windows?
I know I can get into it with OSQL, right? When I do try this opening a
command window, typing OSQL -U sa, then the sa password, I get the following
error:
[Shared Memory]SQL Server does not exist or access denied
[Shared Memory]ConnectionOpen (Connect())
Thanks for the information.
Brad
"Norman Yuan" <NotReal@.NotReal.not> wrote in message
news:eXwJ7OEEFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Attach/Detach or Backup/Restore
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:OijL5KEEFHA.1348@.TK2MSFTNGP14.phx.gbl...
> need
>
|||Norman,
In doing some research I changed OSQL -U to OSQL -E -S <server\instance> and
it worked.
I am now using BACKUP and RESTORE.
Thanks for the information.
Brad
"Norman Yuan" <NotReal@.NotReal.not> wrote in message
news:eXwJ7OEEFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Attach/Detach or Backup/Restore
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:OijL5KEEFHA.1348@.TK2MSFTNGP14.phx.gbl...
> need
>
|||Now I am getting an error message when I try to RESTORE. It looks as if
RESTORE is using the InstanceName from the Backup and I am trying to retore
to a different MSDE instance name on a different server. It gives me error
messages.
Did I do something wrong with the restore statement?
"Brad" <ballison@.ukcdogs.com> wrote in message
news:OTXJuiEEFHA.464@.TK2MSFTNGP15.phx.gbl...
> Norman,
> In doing some research I changed OSQL -U to OSQL -E -S <server\instance>
> and it worked.
> I am now using BACKUP and RESTORE.
> Thanks for the information.
> Brad
>
> "Norman Yuan" <NotReal@.NotReal.not> wrote in message
> news:eXwJ7OEEFHA.2540@.TK2MSFTNGP09.phx.gbl...
>
|||Posting the error message would help, but you're probably trying to restore
a database whose physical location does not exists on the destination
computer. Look at the WITH MOVE qualifier of the RESTORE command. This will
allow you to the logical database files to a different physical location
than where they originated from. Another solution is to make the location
for the database files the same on all systems.
Jim
"Brad" <ballison@.ukcdogs.com> wrote in message
news:Ochhl5EEFHA.1600@.TK2MSFTNGP10.phx.gbl...
> Now I am getting an error message when I try to RESTORE. It looks as if
> RESTORE is using the InstanceName from the Backup and I am trying to
> retore to a different MSDE instance name on a different server. It gives
> me error messages.
> Did I do something wrong with the restore statement?
>
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:OTXJuiEEFHA.464@.TK2MSFTNGP15.phx.gbl...
>
|||Jim,
Thanks for the information. This is the error message that I get:
File 'CLASSICSQL' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL$UKCSQL\Data\CLASSICSQL.mdf'. Use WITH MOVE to identify a valid
location for the file.
The syntax I am using is "RESTORE DATABASE ced FROM DISK =
'D:\MSDEBack\CES.bak' "
This is when I get the error. What is the syntax using the WITH MOVE? or
where would I find this information?
Thanks,
Brad
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:uOfJD%23FEFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Posting the error message would help, but you're probably trying to
restore
> a database whose physical location does not exists on the destination
> computer. Look at the WITH MOVE qualifier of the RESTORE command. This
will[vbcol=seagreen]
> allow you to the logical database files to a different physical location
> than where they originated from. Another solution is to make the location
> for the database files the same on all systems.
> Jim
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:Ochhl5EEFHA.1600@.TK2MSFTNGP10.phx.gbl...
gives[vbcol=seagreen]
<server\instance>[vbcol=seagreen]
I[vbcol=seagreen]
back
>
|||Jim,
Got it. Using osql for the first time and making sure there are not any
syntax errors is a pain.
Thanks again,
Brad
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:uOfJD%23FEFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Posting the error message would help, but you're probably trying to
restore
> a database whose physical location does not exists on the destination
> computer. Look at the WITH MOVE qualifier of the RESTORE command. This
will[vbcol=seagreen]
> allow you to the logical database files to a different physical location
> than where they originated from. Another solution is to make the location
> for the database files the same on all systems.
> Jim
> "Brad" <ballison@.ukcdogs.com> wrote in message
> news:Ochhl5EEFHA.1600@.TK2MSFTNGP10.phx.gbl...
gives[vbcol=seagreen]
<server\instance>[vbcol=seagreen]
I[vbcol=seagreen]
back
>
|||hi,
AllcompPC wrote:
> Jim,
> Thanks for the information. This is the error message that I get:
> File 'CLASSICSQL' cannot be restored to 'C:\Program Files\Microsoft
> SQL Server\MSSQL$UKCSQL\Data\CLASSICSQL.mdf'. Use WITH MOVE to
> identify a valid location for the file.
> The syntax I am using is "RESTORE DATABASE ced FROM DISK =
> 'D:\MSDEBack\CES.bak' "
> This is when I get the error. What is the syntax using the WITH
> MOVE? or where would I find this information?
>
RESTORE DATABASE ced
FROM DISK = 'D:\MSDEBack\CES.bak'
WITH
MOVE Logical_File_Name_for_Data to 'C:\Program Files\Microsoft SQL
Server\MSSQL$UKCSQL\Data\CLASSICSQL.Mdf' ,
MOVE Logical_File_Name_for_Log to 'C:\Program Files\Microsoft SQL
Server\MSSQL$UKCSQL\Data\CLASSICSQL_Log.Ldf'
or the alike, specifying the logical file names and the destination physical
position you want the files to be restored to..
http://msdn.microsoft.com/library/de...ra-rz_25rm.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

copy table data back and forth

This is probably a dumb question.. but here goes.

I'd like to make a copy of several tables before changes are made to them so that I can 'roll back' if necessary.

What I 'thought' i could to was this:

select * into mytable_temp from mytable

and then to roll back..

truncate table mytable

Select * into mytable from mytable_temp

When I try and select back into my original table, it says I can't because the object already exists.. What is a better way to accomplish this??This is probably a dumb question.. but here goes.

I'd like to make a copy of several tables before changes are made to them so that I can 'roll back' if necessary.

What I 'thought' i could to was this:

select * into mytable_temp from mytable

and then to roll back..

truncate table mytable

Select * into mytable from mytable_temp

When I try and select back into my original table, it says I can't because the object already exists.. What is a better way to accomplish this??

You can do it by -

select * into mytable_temp from mytable

and then to roll back..

truncate table mytable

insert into mytable
select * from mytable_temp
go
drop table mytable_temp|||Thanks so much! that will work for me.. just couldn't get the syntax right!!!!

one more question though.. how do I roill back when one column is an identity column? I get this error:

An explicit value for the identity column in table 'pcb00100' can only be specified when a column list is used and IDENTITY_INSERT is ON.|||Thanks so much! that will work for me.. just couldn't get the syntax right!!!!

one more question though.. how do I roill back when one column is an identity column? I get this error:

An explicit value for the identity column in table 'pcb00100' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Just do this -

select * into mytable_temp from mytable

and then to roll back..

truncate table mytable
go
set identity_insert mytable on
go
insert into mytable(id,name)
select id,name from mytable_temp
go
set identity_insert mytable off
go
drop table mytable_temp

That will work for you.|||AWESOME thank you so much!|||When I do this.. I'm still getting the below error...
(not sure what you mean by id,name in your code.. here's mine.. )

truncate table mytable
go
set identity_insert mytable on

go
insert into mytable
select * from mytable_temp
go
set identity_insert mytable off
go

Server: Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'mytable' can only be specified when a column list is used and IDENTITY_INSERT is ON.|||Server: Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'PCB00100' can only be specified when a column list is used and IDENTITY_INSERT is ON.insert into PCB00100 (column_list)
select * from PCB00100_temp
go-PatP|||Thanks Pat!

Is there anyway to generate the column list so I can paste it into my code? There are ALOT of columns : )|||Hi

This might help:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63300|||Hi

This might help:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63300You are a deviant soul... I knew that there was something I liked about you from the git-go! ;)

-PatP|||You are a deviant soul... I knew that there was something I liked about you from the git-go! ;)
Lol - thanks (I think) :D

BTW - ignore the bit where jhermiz says he would rather type out every column name for a table (twice) than use my script. He's just fooling around - we go back a long way and actually I am very highly respected at SQL Team...ahem ;)|||I am very highly respected at SQL Team...ahem ;)

that and 35 cents will get you a washington post.|||that and 35 cents will get you a washington post.I have not the faintest idea what that means - perhaps the meaning fades as it crosses the Atlantic or perhaps I am just being dim :)|||I have not the faintest idea what that means - perhaps the meaning fades as it crosses the Atlantic or perhaps I am just being dim :)

My guess is that Thrasy is expressing how he doesn't like SQL Team :o) .. I remember back (a while ago) he posted something on SQL Team that started a war of words. I was shocked at how edgy some of the folks at SQL Team were. To his defense (not that he needs it ;) ) Thrasy didn't do anything that seemed offensive but the reaction from some SQL Team members was very immature.|||In all fairness I did make a little joke to start it off and the ringleader Tara did back off. On a rare occassion I still post over there but the place does leave a bad taste in my mouth.

If 35 cents gets you a washington post newspaper. And respect at SQLTeam + 35 cents gets you a washington post. Then what does respect at SQLTeam equal.

X + Y = Z
X = Z
Y = 0|||Thanks Sean - faintly embarrassed that you thought it necessary to explain and then also prove mathematically lol

I tracked down your thread - quite right - they were a bit like humourless piranhas...but I was sorry to see you later lost your cool and resorted to SQL based comedy... oh Sean - how could you?

BTW - like your new SQL Team sig :D|||BTW- to add a more simple answer to the (final) question - you can also right click the table in the object browser of query analyser -> Script Object to New Window as and then choose your weapon of choice. Select gives you the easiest-to-copy-and-paste column list.