DTS Package Question: 2 Success Scenarios

Jan 25, 2006

Hi

I am writing a package that i want to have email me on two possible success scenarios.

essentially, this is the conditions:

IF @result = 0
BEGIN
EXEC master..xp_cmdshell @copyfile, NO_OUTPUT
PRINT 'Operation Successful'
END
ELSE
BEGIN
RAISERROR ('This operation failed. Error Code:01. The source and destination files are identical.', 0, 1)
END


If the first condition is met, I want to fire off an email stating success.
If the second condition is met (the RAISERROR) then i want to fire off a different email.

Now, the problem is, I am not sure how to flow it. Both conditions are successful, thus it always fires off the success email if i use an "On Success" flow.

How do i capture the RAISERROR before the email to tell it which email to send?

Does this make sense?

View 1 Replies


ADVERTISEMENT

CrossPosted: DTS Question, 2 Success Scenarios

Jan 25, 2006

Hi

I am writing a package that i want to have email me on two possible success scenarios.

essentially, this is the conditions:

IF @result = 0
BEGIN
EXEC master..xp_cmdshell @copyfile, NO_OUTPUT
PRINT 'Operation Successful'
END
ELSE
BEGIN
RAISERROR ('This operation failed. Error Code:01. The source and destination files are identical.', 0, 1)
END


If the first condition is met, I want to fire off an email stating success.
If the second condition is met (the RAISERROR) then i want to fire off a different email.

Now, the problem is, I am not sure how to flow it. Both conditions are successful, thus it always fires off the success email if i use an "On Success" flow.

How do i capture the RAISERROR before the email to tell it which email to send?

Does this make sense?

View 1 Replies View Related

Passing Execute DTS Package Result (success/failure) To Calling SSIS Package

Mar 6, 2008

I have a SSIS job, one of the last steps it performs is to execute a SQL 2000 DTS package. This has to be done as a SQL 2000 DTS package as it is performing rebuilds of SQL 2000 Analysis Services dimensions and cubes. We've found that when the DTS fails the SSIS job is happily completing showing as a success, we would prefer to know it went wrong.

As far as I'm aware SSIS merely starts the DTS off and doesn't care about it's result. I've taken a look in to turning on the logging for the execute DTS package and thought that the ExecuteDTS80PackageTaskTaskResult would give me the answer I need...but is merely written to the log not available as an event-handler. It also looks like it is not safe to put a SQL task in as the next item to go look at the SQL 2000 system tables to look at the log for the DTS package as the SSIS documentation warns that the DTS package can continue to run after the execute DTS package task has ended.

Ideally I want any error raised within the DTS package to cascade up to be an error in the SSIS job, I can then handle it appropriately. I cannot find a way to do this. Is there a way?

If not, can anyone suggest how in the remainder of the SSIS tasks I can be sure that the DTS has completed before I start any other tasks that will check for the SQL 2000 log of its execution?

View 5 Replies View Related

Execution Of A Package Until It Success

Apr 29, 2008

Hello again to everyone,


Because of aleatory problems some of our Analysis Services Tasks fails and sometimes success with not know reasons. You run the task and it fails, the next time it fails, the next time is success, and so on aleatorily.

The solution of this is to put those tasks in a loop (ore someting similar) that only ends if all of the Analysis Services Tasks succeeds. If not, those tasks starts again. Do you know what I mean? How can I build this? I have investigated with checkpoints, with For Loops ... but still I dont find the way to do it.

Does anybody can help me? I suppose it's not so difficult, but I'm stuck in finding the solution...

Thankx once again.


JohnSSIS

View 3 Replies View Related

Force The Execution Of A Package To Success

May 22, 2008


I have an ssis package with three tasks. I want to force the execution result of the package to success all the time. I execute the package from a .net application. I tried to place my tasks in the package inside a sequence container and created a new script task which would execute when the any of the tasks in the container failed. I am setting the result of the package to success in this script task. This is not correctly working for me. Even if one task in the package failed and the last script task went to success, the .net app is receiving a failure status. Is there any way to handle this in the ssis package than in the .net app. There might be an easy way, but I am unable to find it.

Thanks in advance!!

View 4 Replies View Related

SSIS Job Success But Package Didn't Run

Mar 16, 2007

I created a SSIS package that imports a file then moves the file to a folder called processed.
I created a job that runs the package. The pack works fine when I run it from the SQL Storage Area. When I run the job I get the following message:


Started: 9:58:15 AM DTExec: The package execution returned DTSER_SUCCESS (0). Started: 9:58:15 AM Finished: 9:58:21 AM Elapsed: 6.047 seconds. The package executed successfully. The step succeeded.

showing that the job supposedly completed successfully.


I know that the package the not run because the data did not get imported and the file did not move.

Can anyone explain the package may not run but we still get a job completion status of success?

View 6 Replies View Related

Package Fails But Single Task Ends With Success

Nov 8, 2006

Hi

I've created a simple package that contains only one task that is an execute sql task. When I run only this single task from Business Intelligence development studio it runs successfully. But when I run the whole package (also from Business intlligence studio), the package fails.

The data source I access is ODBC. I'm sure the real reason for the error is the bad ODBC driver of the data source but this can't be changed. So I need to know what is different from running only a task in a package to running the whole package. If I knew that I might be able to adjust some setting and make it work.

Any help welcome.

View 4 Replies View Related

Task Failed, SSIS Package Reporting Success

Mar 26, 2008

I'm debugging a SSIS package in Visual Studio and I have a task that failes but the overall package is reporting success. The deployed package in SQL2005 is doing the same thing.


Task failed: FactVisitApplicationInventory

SSIS package "PACE to PACE DW PROD.dtsx" finished: Success.

The program '[4652] PACE to PACE DW PROD.dtsx: DTS' has exited with code 0 (0x0).


I have set FailPackageOnFailure=True, FailParentOnFailure=True, and MaximumErrorCount=0 on this task and am executing just this single task in Studio and I can't get the Package to report a failure.


Any ideas?

View 3 Replies View Related

DTExec: The Package Execution Returned SUCCESS But Had Warnings

Jun 23, 2006

hi!

i am trying to execute through command line the following:

dtexecui /SQL "ArchiveMain" /SERVER "SE413695AASQL2005" /WARNASERROR /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

I am actaully creating an archiving package.

1. All it does is get some data from the Table.

2. creating directory and set usedirectoryifexist = true

shows some warning when i run that directory already exists cos i don't want to create new one i want to use the existing one.

3. actual archiving to get call to another package for archiving - warning while running the package that parent variable is missing

The pacakge seems to run while i execute it thorugh command prompt but i get this message

DTExec: The package execution returned DTSER_SUCCESS (0) but had warnings, with
warnings being treated as errors!

Thanks,

Jas

View 1 Replies View Related

[scheduling] SSIS Package Fails, But Scheduler Reports Success

Apr 10, 2007

I have an SSIS Package that executes a stored procedure. It turns out my stored procedure was missing.

However, the SQL Job Scheduler reported that step as success.

Can anyone tell me why?

`Le

View 8 Replies View Related

SSIS Package Execution Completes Without Giving Any Success Or Failure Message

Sep 4, 2007

Hi,

We are using SSIS to load some 100k records from flat file to Oracle Destination. We are using Oracle 10g client.
But during the execution after some 5hrs or 6hr with 900k records upload we are getting the message Package execution completed. In the Execution results there is no message related to success or failure and the tasks in the Data Flow where yellow in color. What might be the problem? Any information regarding this case will be helpful for us.

Regards,
Roopa.

View 3 Replies View Related

What Are The Scenarios

May 12, 2006

when one should use SSIS..different data sources,different destinations..etc...i hav never been in such a situation..i have been a developer..beside SSIS are there any MS tools?

thks

View 1 Replies View Related

Troubleshooting Scenarios

Aug 29, 2007

Does anyone have any good places where I can get some practice scenarios for DBA activity? Also any transact sql puzzles to solve for practice purposes. I want to get as much "real world" activity under my belt as possible in a quick time-frame.

Thanks.

View 1 Replies View Related

SSB BizTalk Scenarios

Aug 18, 2006

What are the specific types of scenarios where we could use SSB and BizTalk in tandem?

I have come across a gotdotnet sample of an SSB adapter for BizTalk. As i understand a Biztalk orchestration could be an end point for the SSB conversation.

But what advantages can be obtained using this as compared to a typical SQL adapter for BizTalk which does CRUD operations on DB.

any pointers in this directions would be helpful

TIA

Paritosh

View 3 Replies View Related

Performance In Simple Scenarios

Aug 22, 2007

I have done some performance testing to see if asynchronous triggers performs any better than synchronous triggers in a simple audit scenario -- capturing record snapshots at insert, update and delete events to a separate database within the same instance of SQL Server.

Synchronous triggers performed 50% better than asynchronous triggers; this was with conversation reuse and the receive queue activation turned off, so the poor performance was just in the act of forming and sending the message, not receiving and processing. This was not necessarily surprising to me, and yet I have to wonder under what conditions would we see real performance benefits for audit scenarios.

I am interested if anyone has done similar testing, and if they received similar or different results. If anyone had conditions where asynchronous triggers pulled ahead for audit scenarios, I would really like to hear back from them. I invite any comments or suggestions for better performance.

The asynchronous trigger:



Code Snippet
ALTER TRIGGER TR_CUSTOMER_INSERT ON DBO.CUSTOMER
FOR INSERT AS
BEGIN
DECLARE
@CONVERSATION UNIQUEIDENTIFIER ,
@MESSAGE XML ,
@LOG_OPERATION CHAR(1) ,
@LOG_USER VARCHAR(35) ,
@LOG_DATE DATETIME;

SELECT TOP(1)
@CONVERSATION = CONVERSATION_HANDLE ,
@LOG_OPERATION = 'I' ,
@LOG_USER = USER() ,
@LOG_DATE = GETDATE()
FROM SYS.CONVERSATION_ENDPOINTS;

SET @MESSAGE =
( SELECT
CUST_ID = NEW.CUST_ID ,
CUST_DESCR = NEW.CUST_DESCR ,
CUST_ADDRESS = NEW.CUST_ADDRESS ,
LOG_OPERATION = @LOG_OPERATION ,
LOG_USER = @LOG_USER ,
LOG_DATE = @LOG_DATE
FROM INSERTED NEW
FOR XML AUTO );

SEND ON CONVERSATION @CONVERSATION
MESSAGE TYPE CUSTOMER_LOG_MESSAGE ( @MESSAGE );
END;






The synchronous trigger:



Code Snippet
ALTER TRIGGER TR_CUSTOMER_INSERT ON DBO.CUSTOMER
FOR INSERT AS
BEGIN
DECLARE
@LOG_OPERATION CHAR(1) ,
@LOG_USER VARCHAR(15) ,
@LOG_DATE DATETIME;

SELECT
@LOG_OPERATION = 'I' ,
@LOG_USER = USER() ,
@LOG_DATE = GETDATE()

INSERT INTO SALES_LOG.DBO.CUSTOMER
SELECT
CUST_ID = NEW.CUST_ID ,
CUST_DESCR = NEW.CUST_DESCR ,
CUST_ADDRESS = NEW.CUST_ADDRESS ,
LOG_OPERATION = @LOG_OPERATION ,
LOG_USER = @LOG_USER ,
LOG_DATE = @LOG_DATE
FROM INSERTED NEW
END;





View 4 Replies View Related

Broken Or Hung SQL Server 7 Database Scenarios

Mar 4, 1999

I'm attempting to develop a course whose objective is to present the users with several scenarios of broken or hung databases caused by different things that they then have to fix.

Do you have any ideas or examples of how to break a database and the reasons behind it, also how to repair it afterwards !!

Richard

View 1 Replies View Related

Xp_sendmail && Xp_logininfo No Longer Working Under Certain Scenarios

Feb 11, 2005

Hello, I had a notification set up using xp_sendmail working fine for a while. Recently I updated the SQL Server (http://www.windowsitpro.com/Forums/messageview.cfm?catid=1664&threadid=129743#) to sp3a and we moved the mailbox (http://www.windowsitpro.com/Forums/messageview.cfm?catid=1664&threadid=129743#) that I was using to a Exchange 2003 server. I can still send my notification if I use the domain ID that runs the SQL service and the sa ID, but not the NT ID's that were running it before. I have users use NT authentication (http://www.windowsitpro.com/Forums/messageview.cfm?catid=1664&threadid=129743#) from a domain that's different than the one that the SQL server (http://www.windowsitpro.com/Forums/messageview.cfm?catid=1664&threadid=129743#) resides. There is a trust and nothing has changed with that. Below are the results when I try to run the notification using an NT ID. This ID has full permissions over the SQL server. SQL Mail session started.ODBC error 8198 (42000) Could not obtain information about Windows NT (http://www.windowsitpro.com/Forums/messageview.cfm?catid=1664&threadid=129743#) group/user 'INTERNALxxx.xxx'. Stopped SQL Mail session. As you can see SQL Mail starts and stops ok, but I get the error on the xp_sendmail itself. I can run xp_logininfo to return all of the ID's using this NT login. But if I run xp_logininfo just on the problem ID, I get the following results. EXEC master..xp_logininfo@acctname = 'INTERNALxxx.xxx' Server: Msg 8198, Level 16, State 24, Procedure xp_logininfo, Line 58Could not obtain information about Windows NT group/user 'INTERNALxxx.xxx'. Here's when it works. EXEC master..xp_logininfo BUILTINAdministrators group admin BUILTINAdministrators NULLINTERNALxxx.xxx user admin INTERNALxxx.xxx NULLINTERNALSxx Axx group admin INTERNALSxx Axx NULLSISDOMsxx.dxx.axx user admin SISDOMsxx.dxx.axx NULLSISDOMSxx.Dxx.Axx group admin SISDOMSxx.Dxx.Axx NULLINTERNALlxxx.rxxx user user INTERNALlxxx.rxxx NULL The ones in bold work for everything. Please advise? Julie

View 2 Replies View Related

Disaster Recovery Scenarios For Analysis Services

Jun 7, 2006

Hi

I am trying to find disaster recovery scenarios for Analysis Services which seems to be lacking. Is there anything documented.

Thanks

View 4 Replies View Related

Wich Is Faster? In Relation To These Two Business Object Scenarios

Jun 27, 2006

I kow for a solid comparison between using datareaders and datasets I will have to perform that myself.  But for now I will be utilizing datasets...
What I am doing is currently utilizing assemblies to create my datasets ahead of time.  I will eventually compile them as dlls.  I'm just utilizing assemblies during my building/testing fase.
My questions is:
Is it faster to completely build the datasets and all needed connections inside the assemblies/dlls and fill them?  Or to build the datasets and connections as a sub procedure that can be accessed and then fill them as each required set of data is needed?
I ask because I will be having many different data connections and so I'm not sure if it's faster to explicitly build/fill almost each and every one and have them compiled at runtime ready to be accessed, or to file them when called from a sub etc...?
As I take it, the server should track and monitor which are used the most, and cache them, so as to operate faster.  I wonder if it will still do this if the datasets aren't pre-filled?

View 2 Replies View Related

Success

Jul 15, 2004

oK if anyone remembers the problems with my Update/termination Trigger I found it works best if I do it as a Stored procedure instead of a trigger. Thanks again for your help guys. I really appreciate it

View 3 Replies View Related

Dts 'success' With No Result...

Apr 9, 2002

Hi:
I create a DTS package in 'Copy SQL Objects Task' to transfer 3 stored procedures from my local SQL msdb to remote testing msdb database.
Execution is 'successful' but the destination server msdb does not have the 3 stored procedures.

Any hint on this 'vanished' copy objects?
thanks
-D

View 2 Replies View Related

Confirmation Of Success

Feb 13, 2004

Just a thought.

If an issue is resolved please note that the problem has been resolved. Because there are "many ways to skin a cat" it would be helpful to anyone else with a similar problem (or someone trying to learn) what the solution was.

View 7 Replies View Related

How Check A SQL Job Success Or Not?

Oct 11, 2006

Hi expert,

I use a SQL Job that makes use of the BACKUP command. How can I check this job success or not?

Thanks

View 5 Replies View Related

Success Message

Jun 19, 2007

Hey all

Which system variable do I use if I would like to transfer success message into an SQL table? I'm running a task to import data into an SQL table. And I'm using the System::ErrorDescription variable (Execute SQL task) to insert error messages into a table but I would really appreciate it if someone could tell me what system variable would insert 'success' message into an SQL table. Tried a lot of searching on the net but couldn't find anything.

Many thanks,

Rupa

View 1 Replies View Related

Success Stories

Oct 19, 2007

Has anyone here been involved in a successful commercial Data Warehouse project?
If so, what was your role? How big was the project? Did you do it all or was their one guy data modelling, one for ETL, one for Client-side, one project managing, etc...? What tools did you use? Was it in SQL2000? 2005? Did you use DTS/SSIS for your ETL or Sprocs? Did you use SSAS? Were you writing lots of MDX to meet client-side needs? Or did you use a completely different toolset for all or some of this?
Did you use the Kimball approach? Or were you in the other camp?
Was your latency nightly or did you have some type of "real-time" capability?

I would prefer to hear from people who've actually been involved in successful projects (or failed projects if you would like to share with us why you think they failed). I'm not too interested in hearing from people that have never done it but have a theory as to how it should work (like me).
Many Thanks,
Coolerbob

(Or is there some other website forum I should be asking this question on? (like mdxteam, olapteam, datawarehouseteam or something)

View 3 Replies View Related

Success Of Stored Procedure

Jul 17, 2001

Is there a parameter allowing to verify successful execution of a stored procedure?

Something that can be run as the last line in a stored procedure to verify its success...?

Thanks!

View 1 Replies View Related

Email Job Success To More Than One Operator

Feb 28, 2001

Hi Guys, basically I have a scheduled job in MSSQL7, which on completion e-mails one of the operators.....


How can I get this job to email more than one operator upon completion???


Cheers....

View 1 Replies View Related

Not Much Success With MULTI DELETE

May 11, 2006

Trying to delete records associated with the same contact id from all the tables used for that contact. But I'm getting an error. Strange error because the table does exist and I've been able to do single deletes from that table. So, something tells me I'm not getting something right in my SQL statement. How do I delete records from all 3 tables using the same id (as opposed to doing 3 different SQL calls, one for each)?

-----------------
DELETE names, addresses, phones
FROM names AS n, addresses AS a, phones AS p
WHERE n.id = '12' AND a.id = '12' AND p.id = '12'

Error: Unknown table 'names' in MULTI DELETE
-----------------

(BTW, how do tag my code so it shows up in a code div?)

View 1 Replies View Related

Success Arrows -- Moving Them?

May 10, 2007

I apologize in advance for what is probably a really dumb question.



I have an SSIS package that is executing a number of data flow tasks simultaneously, and then all are followed by a single script step. I'm trying to get all the constraint arrows to look uniform by having them exit the DFTs on the right side and enter the script step on the left. Unfortunately, BI Dev Studio is putting the exiting arrows on the bottom, which looks awful and is difficult to follow. I can only move them between specific points on the bottom of the step. How do I move those arrows to the right side?



Thanks in advance.

View 8 Replies View Related

SQL XML :: How To Query XML Field To Get Success Value

Dec 2, 2015

How to use Xquery to get the success value and the result value from the following XML.

This XML is stored in a SQL Table XML field, i want to be able to get all the success and reslut values for all the records:

Here is my XML

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
 xmlns:ns1="https://data.abc.biz/mysoappathtest/?soap:customcalls:v1"
  xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
  <SOAP-ENV:Body>
   
[Code] ....

I am trying to get the result = 15810 and the Success value = true

View 3 Replies View Related

Errors But Finished With Success

Oct 23, 2006

My ssis package errors out because one of the database connection failed. I successfully logged error but also indicated that package finished successfully. My confusion is if a sheduling software schedules this package, what would be return code sent by dtexe... . would it be success or failure? In this scnerio i want it to return failure so that appropriate team can be contacted.

thanks,

kushpaw

View 1 Replies View Related

How To Choose One Of Two Success-paths?

Jun 28, 2006

I have an ActiveX Script Task in SQL Server 2000. It chooses one of two
possible success-paths depending on if a file exist or not. (Part of the old ActiveX Script for choosing next step is below)


I need to rewrite this for a Script Task in SQL Server 2005 but it seems like it
doesn't have this functionality and objects.


Does anyone know how to write the code for choosing the next step in a
Script Task or knows another way to solve my problem?

The package is not suppose to fail if the file is missing, it's must succeed and that's why I need two success-paths.

Regards,
Sara


'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main ()


Dim pkg
Dim stpContinuePkg
Dim stpExit


SET pkg = DTSGlobalVariables.Parent


SET stpContinuePkg = pkg.Steps("DTSStep_DTSActiveScriptTask_4")
SET stpExit = pkg.Steps("DTSStep_DTSActiveScriptTask_21")


If ...... Then
Main = DTSStepScriptResult_ExecuteTask
stpContinuePkg.DisableStep = False
stpExit.DisableStep = True
Else
.......
End If


Main = DTSTaskExecResult_Success


End Function

View 3 Replies View Related

Tables Export 'Success' But No Relationships...

May 29, 2003

I export all tables from serverA databaseAA to serverB (both SQL2k sp3)databaseAA with 'Success', but without any relationships. In SQL6.5 Tools of Transfer will bring all objects include relationship.

I also tried export "All Objects" which fails with unclear message. I just don't want to do a backup and restore....

thanks
David

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved