DTS ERror Email

I have created a DTS package.
When a step in the package fails i would like to be emailed with the error
details.
I have managed to make the Package email me with the 'Send Email Task'
feature, but can;t seem to get the error details.
Any ideas?
You'd have to have the eMail Task send query output as part of the message
or as an attachment. The query you'd execute would be against the package
logs, given that you've enabled package logging.
The logs are stored in the msdb system database. There are logs for the
packages, steps, and tasks, seperately, but you can write a query against
them to extract out which ones ran, at what times, and whether or not any of
them had errors. The package log will give you just generic success and/or
failure information.
Sincerely,
Anthony Thomas

"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:e1NsqMq4EHA.208@.TK2MSFTNGP12.phx.gbl...
I have created a DTS package.
When a step in the package fails i would like to be emailed with the error
details.
I have managed to make the Package email me with the 'Send Email Task'
feature, but can;t seem to get the error details.
Any ideas?
sql

DTS ERror Email

I have created a DTS package.
When a step in the package fails i would like to be emailed with the error
details.
I have managed to make the Package email me with the 'Send Email Task'
feature, but can;t seem to get the error details.
Any ideas?You'd have to have the eMail Task send query output as part of the message
or as an attachment. The query you'd execute would be against the package
logs, given that you've enabled package logging.
The logs are stored in the msdb system database. There are logs for the
packages, steps, and tasks, seperately, but you can write a query against
them to extract out which ones ran, at what times, and whether or not any of
them had errors. The package log will give you just generic success and/or
failure information.
Sincerely,
Anthony Thomas
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:e1NsqMq4EHA.208@.TK2MSFTNGP12.phx.gbl...
I have created a DTS package.
When a step in the package fails i would like to be emailed with the error
details.
I have managed to make the Package email me with the 'Send Email Task'
feature, but can;t seem to get the error details.
Any ideas?

DTS Email Task Works - But not in JOB

I have a DTS package created in SQL Server 2000 that creates a table,
pumps it to excel then emails the spreadsheet to users. I do not have
exchange. I have Outlook 2003 and I have an email account set up in
that that users POP and SMTP server(s). I can create/send/receive
email using this account I have set up in outlook. I have added 3
people to the address book. These are the people that are being sent
the email via DTS. If I excecute the DTS package from the server or
from my laptop - works fine. If I schedule the DTS in a JOB it get a
MAPI logon error:
Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
MAPI Logon failed. Error source: Microsoft Data Transformation
Services (DTS) Package Help file: sqldts80.hlp Help context:
9100 Error Detail Records: Error: -2147220352 (80040480);
Provider Error: 0 (0) Error st... Process Exit Code 1. The step
failed..
I'm not sure what I need to do to get this working. I assume it might
be permissions issue but I have no idea how to address it. Any tips
greatly appeciated since this is making me have to manually run a DTS
every morning *barf*.
Thanks
taranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book. These are the people that are being sent
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
>
> I'm not sure what I need to do to get this working. I assume it might
> be permissions issue but I have no idea how to address it. Any tips
> greatly appeciated since this is making me have to manually run a DTS
> every morning *barf*.
> Thanks
>
You need to set the Outlook profile up under the context of the login
that the SQL Server service account is running under. Meaning, if the
SQL service is running as MYDOMAIN\SQLServerLogin, then you need to
login to the machine as MYDOMAIN\SQLServerLogin and create the Outlook
profile as that user.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||taranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book.
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
There is an easier way to send email with SQL 2000. Have a look at
xp_smtp_sendmail (free) from SQLDev.net. With this extended stored
procedure you can send emails without the hassle of setting up a MAPI
profile.
This procedure is a blessing. Also the setup is easy and there is
plenty of documentation. Go to SQLDev.Net, and in less than 5 minutes
you'll be sending all the emails you want and you'll never miss the SQL
Mail nightmare.
Regards,
lucm

DTS Email Task Works - But not in JOB

I have a DTS package created in SQL Server 2000 that creates a table,
pumps it to excel then emails the spreadsheet to users. I do not have
exchange. I have Outlook 2003 and I have an email account set up in
that that users POP and SMTP server(s). I can create/send/receive
email using this account I have set up in outlook. I have added 3
people to the address book. These are the people that are being sent
the email via DTS. If I excecute the DTS package from the server or
from my laptop - works fine. If I schedule the DTS in a JOB it get a
MAPI logon error:
Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
MAPI Logon failed. Error source: Microsoft Data Transformation
Services (DTS) Package Help file: sqldts80.hlp Help context:
9100 Error Detail Records: Error: -2147220352 (80040480);
Provider Error: 0 (0) Error st... Process Exit Code 1. The step
failed..
I'm not sure what I need to do to get this working. I assume it might
be permissions issue but I have no idea how to address it. Any tips
greatly appeciated since this is making me have to manually run a DTS
every morning *barf*.
Thankstaranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book. These are the people that are being sent
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
>
> I'm not sure what I need to do to get this working. I assume it might
> be permissions issue but I have no idea how to address it. Any tips
> greatly appeciated since this is making me have to manually run a DTS
> every morning *barf*.
> Thanks
>
You need to set the Outlook profile up under the context of the login
that the SQL Server service account is running under. Meaning, if the
SQL service is running as MYDOMAIN\SQLServerLogin, then you need to
login to the machine as MYDOMAIN\SQLServerLogin and create the Outlook
profile as that user.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||taranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book.
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
There is an easier way to send email with SQL 2000. Have a look at
xp_smtp_sendmail (free) from SQLDev.net. With this extended stored
procedure you can send emails without the hassle of setting up a MAPI
profile.
This procedure is a blessing. Also the setup is easy and there is
plenty of documentation. Go to SQLDev.Net, and in less than 5 minutes
you'll be sending all the emails you want and you'll never miss the SQL
Mail nightmare.
Regards,
lucm

DTS Email Task Works - But not in JOB

I have a DTS package created in SQL Server 2000 that creates a table,
pumps it to excel then emails the spreadsheet to users. I do not have
exchange. I have Outlook 2003 and I have an email account set up in
that that users POP and SMTP server(s). I can create/send/receive
email using this account I have set up in outlook. I have added 3
people to the address book. These are the people that are being sent
the email via DTS. If I excecute the DTS package from the server or
from my laptop - works fine. If I schedule the DTS in a JOB it get a
MAPI logon error:
Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
MAPI Logon failed. Error source: Microsoft Data Transformation
Services (DTS) Package Help file: sqldts80.hlp Help context:
9100 Error Detail Records: Error: -2147220352 (80040480);
Provider Error: 0 (0) Error st... Process Exit Code 1. The step
failed..
I'm not sure what I need to do to get this working. I assume it might
be permissions issue but I have no idea how to address it. Any tips
greatly appeciated since this is making me have to manually run a DTS
every morning *barf*.
Thankstaranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book. These are the people that are being sent
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
>
> I'm not sure what I need to do to get this working. I assume it might
> be permissions issue but I have no idea how to address it. Any tips
> greatly appeciated since this is making me have to manually run a DTS
> every morning *barf*.
> Thanks
>
You need to set the Outlook profile up under the context of the login
that the SQL Server service account is running under. Meaning, if the
SQL service is running as MYDOMAIN\SQLServerLogin, then you need to
login to the machine as MYDOMAIN\SQLServerLogin and create the Outlook
profile as that user.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||taranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book.
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
There is an easier way to send email with SQL 2000. Have a look at
xp_smtp_sendmail (free) from SQLDev.net. With this extended stored
procedure you can send emails without the hassle of setting up a MAPI
profile.
This procedure is a blessing. Also the setup is easy and there is
plenty of documentation. Go to SQLDev.Net, and in less than 5 minutes
you'll be sending all the emails you want and you'll never miss the SQL
Mail nightmare.
Regards,
lucm

DTS Create Report And email .

Hello,

I am trying to create a dts to schedule some reports and email it. The point is that i try to do that through Data Flow > Export to Excel....!!! Is there an other way to create reports? Cause i don't see any crystal reports or somenthing else!!!

Thanks
Stefanos
SSIS (I assume you're using SSIS, if not, you're in the wrong forum) does not create reports. You can save data to Excel, but it's just going to be rows & columns. No formatting, etc...|||

Stefanos -

I would recommend looking at SQL Reporting Services (SQLRS) for this need. SQLRS has two different report scheduling capabilities. SQL Server Standard Edition has standard subscription capability, while the Enterprise Edition adds to this a Data Driven subscription capability.

When a standard SQLRS subscription is created, this actually creates a job that is managed by the SQL Agent. If you needed SSIS to be able to execute that job/subscription, I believe it would be easy to do in SSIS.

Hope that might help you.

|||

Thanks for your reply,

I thought that through ssis i could create an excel report and edit the excel file through a script. My problem is that a want to create a report automatically and email it to a customer!! I think that Sql RS will help me doing that. As for the email forwarding, i think i will need ssis.

Thanks Stefanos

DTS and process task - doesnt wait my called exe application finish completely

Hi,

I need to execute a DTS that have a couple of steps and one of them is a process task that simply call an exe file i made that will send an email to warn the user.

What happens here is that the process task executes my exe file but it doesn't wait for it to compete and fires the next task after and finally closes.

There is anyway to make a "while statament" to wait until my exe application finishes?

Any idea?

Thanks in advance,
Tiago TeixeiraI Don't think there is a way to reply to SQL that an executable that it started has now stopped running.

Shame your not using the SQL email service but your prob doing other things as well.

Do you have any contriol over the .exe source code ?

Idea 1. it could possibly create a file in Dos then delete it as a last action before ending. TSQL would then loop (possibly with a while loop) to check if the file still exists notifying you that the .exe has done it's job.

GW|||Why not connect your steps with "On success" workflow/execution paths?
Also, why are you calling another application to send an e-mail when the DTS package designer can send e-mails for you? :D|||I Don't think there is a way to reply to SQL that an executable that it started has now stopped running.

Shame your not using the SQL email service but your prob doing other things as well.

Do you have any contriol over the .exe source code ?

Idea 1. it could possibly create a file in Dos then delete it as a last action before ending. TSQL would then loop (possibly with a while loop) to check if the file still exists notifying you that the .exe has done it's job.

GW

Hi,

Thanks for the help.

I don't use SQL email service because i don't have a MAPI server available to do it, and i don't want create it in IIS just for this purpose.
I found a free dll extension around that sends email without a MAPI account but, the problem is that the DTS aim is export data to excel file and after send this excel file by email, the problem is that when i send the email ( using that dll) an error occurs because the excel file still locked by the precedent task, so i created myself an exe file that frees the xls file and send the email using a .NET library instead of the dll i said, but the erro i reported appears.

About your second idea, can you please be more specific or paste a code snippet abou how to do that Loop. I would appreciate very much.

Thanks in adavance,
Tiago Teixeira|||Hi Teix

I was basically just thinking of something like this


DECLARE @.cnt int
WHILE(@.cnt <= 5)
BEGIN
DECLARE @.result int
EXEC @.result =xp_cmdshell'del Myfile.exe'
IF(@.result = 0)
PRINT'Success'
ELSE
PRINT'Failure'
WAITFORDELAY'00:15'
SET @.cnt = @.Cnt +1
END


Not sure how wise this approach is though

Good Luck

GW|||Hi GWilliy,

I found a way to manage it without code, i tried the code snippet you generously wrote but application behaved in the same way as before.

So, i created by hand a schedule job and inspite of make a single DTS package that would make all the stuff i needed i sliced the main job in 3 parts and i added each one sequentially to the Job and after that all the steps were accomplished correctly and i got the stuff working.

Thanks all for the precious help and ideas,
Best regards,
Tiago Teixeira|||Try windows handler dll, I think every exe program has unique windows handler id, use this id in a loop... Just an Idea...|||thanks i'll investigate that later, but from now i think i've the job done.

thanks
Tiago Teixeira|||Poison Ref Try windows handler dll, how would you access this from TSQL ?

may be handy for future ref

Teix - Glad U got sorted

GW