Catching an exception from a Web service task

Hi!

I am quite new using SSIS and I have a problem with catching an (SOAP) exception from a Web service task. Some times my web service task can fail and when the web service is failing, it is throwing an exception. When the task succeeds the result is being put into a variable, That part is not a problem.

But catching an exception is. I have tried to use a script task and tried to get exception from the dts object model. I have not yet succeeded on that. But it might be a possible way to go. A different approach might be creating an OnError event on my web service task which I can create a task when triggered. But I have not found any solution yet and I hope some people out there have done this before or have a solution on this.

Regards

Geir F

I forget to tell that I want the error description text from the exception that is thrown from the web service task because I want to log the errors. So what I hope is to have a script task that executes when web service fails and to write some code to catch the error description.

Current status on this is that the script task executes when the WS fails (red arrow). Nice. The web service returns an object, say TestOutput variable. When the WS succeeds, I can convert the object into a integer datatype and catch the value. But when it fails, I don't know to catch the error description. When executing the package, I can see the error description on th progress tab, so there must be a way for me to get the error too :-)

Regards

GF

Catching a General Exception

I am trying to write a query that I only want to run on sql server 2005 databases. If a server isn't 2005, it will throw an exception. I would like to catch this general exception. Here is the query...

DECLARE @.Server [nchar] (100)
SET @.Server = (CONVERT(char(100), (SELECT SERVERPROPERTY('Servername'))))

INSERT INTO [tempdb].[dbo].[User_Auditing] (Server, UserName, WinAuth, SQL_Auth_UserName, PassPolicyOn)
SELECT @.Server, s.name, isntuser, q.name, is_policy_checked
FROM sys.syslogins s FULL OUTER JOIN sys.sql_logins q
ON (s.name = q.name)

The errors I would get are as follows...

Msg 208, Level 16, State 1, Line 4
Invalid object name 'sys.syslogins'.
Msg 208, Level 16, State 1, Line 4
Invalid object name 'sys.sql_logins'.

I know in Java, I would just put a try before the declare and a catch("Invalid object name") after the statement, however, I'm not sure if this is even possible in T-SQL. Thanks for any help.
-Kyle

Nope. You cannot. 2005 introduces the concept of try...catch, but it wouldn't catch this error.

You could do something like

if @.@.version like 'Microsoft SQL Server 2005%'

exec ('select ''this is 2005''')

else

exec ('select ''this is NOT 2005''')

I used @.@.version since it will work on any version of SQL Server. Using serverproperty() is another possibilty.

casting float output param throws an exception.

I keep getting an exception when trying to cast an output param as a float type. the SPROC is marked as float OUTPUT and the Cost column in the database is a float type as well. And there are no nulls in the cloumn either. here is how my code looks:

SqlParameter prmCost= new SqlParameter("@.Cost", SqlDbType.Float,8);
prmCost.Direction=ParameterDirection.Output;
cmd.Parameters.Add(prmCost);
//...blah blah blah
//invalid cast gets throw on here (it happens with all my float types)
productDetails.Cost=(float)prmCost.Value;

Any suggestions as to what I am doing wrong?You have to use the <datatype>.Parse() methods to covert to numbers, e.g.

float test = float.Parse(prmCost.Value.ToString());

|||If you do a watch on prmCost before the error do you see a reasonable value in there?

If so, maybe try the cast with a double on the .Net side and see if you can get it in. Try the code below to help debug. If it still crashes maybe the exception message will be more helpful than your current one.

string message = "";
double doubleValue = 0.0;
try{
string seeWhatsThere = prmCost.Value.ToString();
// put a watch on seeWhatsThere to see what it looks like
doubleValue = System.Double.Parse( seeWhatsThere );
}
catch( FormatException ex){
message = ex.Message;
}
catch( Exception ex ){
message = ex.Message;
}

|||Yes, there is a reasonable value for the parameter (I check the param for a null value before attempting to cast it). I did it as follows, but I'm not sure if I will loose any precision this way. From what I understand Single is the same thing as float, correct me if I'm wrong.

product.Cost=Convert.ToSingle(prmCost.Value);

Thanks for the help.