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 tables using SSIS package

I need to create a fairly simple package. And almost because of the simplicity, I'm stumped.

I need to copy all non-system tables from server1.database1 to server2.database2. Additionally, four of the 30+ tables need to be renamed on the fly -- i.e. their name will reflect the year and month that the copy takes place.

I've tried using the Transfer SQL Server Object Task to simply copy the tables, but I get flaky results at best with it. Sometimes it tells me the source table doesn't exist, when I can clearly see it (and I've selected it from the list). And even though I have turned on the Include Indexes option, they don't always come through.

I'm wondering if I need to do a For Each loop looking at an ADO object?

Any suggestions?

Stephanie

SBowe wrote:

I need to create a fairly simple package. And almost because of the simplicity, I'm stumped.

I need to copy all non-system tables from server1.database1 to server2.database2. Additionally, four of the 30+ tables need to be renamed on the fly -- i.e. their name will reflect the year and month that the copy takes place.

I've tried using the Transfer SQL Server Object Task to simply copy the tables, but I get flaky results at best with it. Sometimes it tells me the source table doesn't exist, when I can clearly see it (and I've selected it from the list). And even though I have turned on the Include Indexes option, they don't always come through.

I'm wondering if I need to do a For Each loop looking at an ADO object?

Any suggestions?

Stephanie

I suggest you use the Import Wizard to do this for you. I think you can configure it to build the tables for you if they are not already there.

-Jamie

|||

Jamie,

Thanks for the suggestion. However, that won't work for my environment. Specifically, my company requires that I create a scheduled job. So I then must utilize a package.

Here's where I get the flaky results using the SQL transfer object: I only want the non-system tables. So I set the All Tables property to False and then select the tables I want from the Tables Collection property. However, the package then fails when I run it and tells me it cannot find the tables from the source. This is mind-boggling since it allowed me to pick the tables from a list of tables.

I'll keep digging.

Thanks again,

Stephanie

|||

Stephanie,

If you use the import/export wizard in SSMS todo this; you can choose to save it as an SSIS package; then you can open that package and make the specific changes you need (eg renaming the 4 tables).

Rafael Salas

|||Stephanie,

I would advocate NOT using the transfer objects task.

This has some "features" (apparently to preserve sql 2000 compatability) that means the tables will not be transferred over accurately.

Specifically, you may find your transferred tables lose default values or identities

see my thread on this here|||

Rafael Salas wrote:

Stephanie,

If you use the import/export wizard in SSMS todo this; you can choose to save it as an SSIS package; then you can open that package and make the specific changes you need (eg renaming the 4 tables).

Rafael Salas

The import / export wizard will also not setup the tables correctly - see the post I linked to in my post above

copying tables DTS vs SSIS - speed!!!

Hi,
I'm trying to create a package with SSIS to replace the DTS process that we have in place already.
DTS package copy four table content from one server to another. I have created a simple SSIS to do the same processes but the process it alot slower than DTS!!

I did ran the SSIS package using ctrl+F5 and also from command prompt but still it's quite slow.
SSIS uses SMO to access to server and both are running on 2005

ThanksUse the "Table or view - fast load" option in your OLE DB Destinations.|||sorry for my ignorance, but is this an SSIS property? and if yes where can I find it
Thanks|||In the OLE DB Destination, it is a drop down option titled "data access mode". Double click on the OLE DB destination and it's on the main page.|||the problem is that I am using the transfer SQL server object task which it uses SMO by default not OLEDB. unless there is another way to copy the content of a table.
p.s. the tables do not exist on the remote server the transfer SQk server objects task, creates the table as well as copying the content.
cheers|||

Kolf wrote:

the problem is that I am using the transfer SQL server object task which it uses SMO by default not OLEDB. unless there is another way to copy the content of a table.
p.s. the tables do not exist on the remote server the transfer SQk server objects task, creates the table as well as copying the content.
cheers

Remember that SSIS is a data manipulation tool - not a schema manipulation tool. Hence, there isn't THAT much support for moving schema objects about.

If I were you I would create the tables using conventional methods (i.e. CREATE TABLE scripts run from the Execute SQL Task) and then use data-flows to pump data between them. This will not be slower than DTS. It will be alot more maintainable as well.

-Jamie

|||Thanks for your advise,
the problem is the table schema changes each time as the selected tables will be different. Is there a task to be able to extract the schema of the source table, so I can apply it to the destination server.

and then maybe use the data flow to push the data across.

Thanks again|||Data flows don't handle changing metadata, unless you are building them dynamically.|||Thanks , but even if I build the metadata for the tables in the destination in the control flow (which is not a problem) then I should be able to feed the data with the dataflow.

The problem is I have different tables and they change each time so I should be able to write one generic dataflow and change the parameter with a script each time I'm running.

cheers|||

Kolf wrote:

Thanks , but even if I build the metadata for the tables in the destination in the control flow (which is not a problem) then I should be able to feed the data with the dataflow.

The problem is I have different tables and they change each time so I should be able to write one generic dataflow and change the parameter with a script each time I'm running.

cheers

As I think we have discussed on other threads - you can do this.

Apologies if I've missed any of your replies. The alerting functionality of these forums is broken (for me anyway).

-Jamie

Copying table data from SQL Server 2005 to SQL Server 2000 - Very Slow when using OLEDB Source a

An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.

On SQL Server 200 instances the job ran in minutes in the old 2000 package.

Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?

Any inputs will be much appreciated.

Thanks,

MShah

What defect are you referring to in "Transfer Objects" task?|||

Check this out for the defect:

http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackid=336f6832-f68c-4a7f-be74-3e62e1310609

MShah

|||This bug was specific to using "SQL Server Authentication" at the destination of the transfer. It has been fixed in SP1.

So, to copy data from one table to the other, you should be able to use Transfer task with "Windows Authentication" at the destionation if you are using SQL Server 2005 RTM. If you have SP1 installed, you should be able to use "SQL Server Authentication" or "Windows Authentication".|||

Thanks. I will upgrade to SP1. We only use SQL Server Authentication here, no windows / mixed mode.

sql

Copying SSIS Packages

Is there any way to copy SSIS packages from one mirrored server to another? I'm considering using robocopy, but is there another solution? I'd then like to use this solution with the Transfer jobs task.Robocopy works for me in this scenario. Are you using a file-based or SQL Server deployment for the packages?|||I'm storing all packages in the msdb (package store) and we have our jobs pointing to the msdb. With SQL2000, we used the "transfer package" method described in www.sqldts.com, where we simply queried the table from our primary and copied those records to our warm-standby. With that solution on sqldts.com, is it possible to use the "sysdtspackages90" table to achieve the same results? I've also read that "dtutil" can be used to "copy" packages. In my case, I'm not sure on which method would be best though. Or, would it be better to store the packages in the file system?|||

DTUTIL is a good option if you are going with database storage. If you are using file storage, it isn't as valuable (unless you are also resetting GUIDs or passwords when you move them).

My personal preference is file system deployment. Others like database deployment. It really comes down to your preferences and specific requirements (though most requirements can be met be either).

Copying files from a sharepoint location to local machine using SSIS

I have to copy files from a sharepoint or extranet location (basically https://.....) location to my local server using SSIS.

Any kind of early help would be really great.

Search for Sharepoint Web Services. This is the recommended way (at least, what's been recommended to me in the past) to extract data from Sharepoint. You can use a Web Service task to access the Sharepoint web services.

http://www.developer.com/tech/article.php/3104621

Copying Database without data

Hi,
Does anyone know, with either the Apr or Jun CTP, how to copy an entire database without the data? I know to use the SSIS wizard to copy tables with data, but there seem to be no option to not copy the data.

Thanks.In the June CTP you can use the Transfer Objects Task. It has an option for transferring data that you may set to false.|||BTW, There are some issues with this task in the June CTP that have recently been fixed and will not be available until the next CTP. This solution will probably not work for you until you can get the version with the fixes.|||Hi Kirk,
Thanks for the update. I was actually hoping for a checkbox option inside the SSIS Import and Export wizard. :) I'll wait till it is fixed then.|||

you can

1. left click source database ->Script database ->create to

1.1. change database and filenames on the script created

1.2 execute script to create new database

2. left click source database -> tasks -> generate scripts

2.1 add at the to the following lines

use newdatabasename

go

2.2. run the script

you can save the scripts and use them at SSIS for automation

Copying Database without data

Hi,
Does anyone know, with either the Apr or Jun CTP, how to copy an entire database without the data? I know to use the SSIS wizard to copy tables with data, but there seem to be no option to not copy the data.

Thanks.In the June CTP you can use the Transfer Objects Task. It has an option for transferring data that you may set to false.|||BTW, There are some issues with this task in the June CTP that have recently been fixed and will not be available until the next CTP. This solution will probably not work for you until you can get the version with the fixes.|||Hi Kirk,
Thanks for the update. I was actually hoping for a checkbox option inside the SSIS Import and Export wizard. :) I'll wait till it is fixed then.|||

you can

1. left click source database ->Script database ->create to

1.1. change database and filenames on the script created

1.2 execute script to create new database

2. left click source database -> tasks -> generate scripts

2.1 add at the to the following lines

use newdatabasename

go

2.2. run the script

you can save the scripts and use them at SSIS for automation

Copying a column to a SSIS package variable

I need to use a value retrieved in one data flow in the second data flow. What's the best way to do this?

How do I copy the column retrieved to a variable so I can use that variable in the second data flow?

Data Flow implies multiple rows, which doesn't naturally fit with a single variable.

Normally I would be using an Exec SQL Task to populate a variable from a table. You could use a Script Component in the data flow, or perhaps the Recordset Destination if there are several rows.

If reading just one value/line from a file for example, then I'd just use the Script Task.

Copy table from ODBC to SQL database

Hi all,

I'm trying to copy a table from an odbc db to an sqlserver2005 db. SSIS will not do this for me and says I must write a script. I'm not experienced enough in sqlserver to do this and was unable to find a example of this by searching the web. Does anyone know of any websites with an explanation of how to do this?

thanks for any help.

You can use Import/Export Wizard to copy your data to SQL Server db.

http://www.databasejournal.com/features/mssql/article.php/3580216
http://msdn2.microsoft.com/en-us/library/ms141209.aspx|||

That does not really help as the question was not abou how to start the process but how to get part this point, I am having the same problem and am not very good with SQL queries.

Thanks

|||

Have you seen the following thread?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=379235&SiteID=1

There is a lot more messages on the topic in this forum; try to search for them.

Thanks.

Copy table from ODBC to SQL database

Hi all,

I'm trying to copy a table from an odbc db to an sqlserver2005 db. SSIS will not do this for me and says I must write a script. I'm not experienced enough in sqlserver to do this and was unable to find a example of this by searching the web. Does anyone know of any websites with an explanation of how to do this?

thanks for any help.

You can use Import/Export Wizard to copy your data to SQL Server db.

http://www.databasejournal.com/features/mssql/article.php/3580216
http://msdn2.microsoft.com/en-us/library/ms141209.aspx|||

That does not really help as the question was not abou how to start the process but how to get part this point, I am having the same problem and am not very good with SQL queries.

Thanks

|||

Have you seen the following thread?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=379235&SiteID=1

There is a lot more messages on the topic in this forum; try to search for them.

Thanks.